How do I complete the input of a CREATE TYPE in sqlplus (Oracle)?

I'm trying to input a CREATE TYPE in my terminal, what I can't figure out how to finish it. Here is my specific query:

CREATE TYPE testtype AS OBJECT (
    id int
);
/

It then refuses to perform any operation, but instea开发者_开发百科d sits waiting for more input. I can input just about every other query type without a hitch. What is going on? Is there some finalizing step I need to do to make it finish?

---

When you say that it 'instead sits waiting for more input', do you mean that SQL*Plus is asking for another line of input, or that the database apparently isn't responding?

In other words, are you getting this:

SQL> create type testtype (
  2     id int
  3  );
  4  /
  5

(the 5 indicating that SQL*Plus thinks there's more to come), or are you getting something like this:

SQL> create type testtype (
  2     id int
  3  );
  4  /


^Ccreate type testtype (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

(I interrupted this after it ran for a few seconds without successfully completing.)

For the rest of this answer, I'll assume the latter. I can't honestly believe that SQL*Plus would fail to recognise the end of your input. A single / on a line on its own (even with spaces on either or both sides of it) is interpreted as a definite end of input, even if the input isn't valid SQL.

Note that if you're using a shell such as bash, you can type in text even if the program you're running isn't currently requesting user input. For example:

SQL> create type testtype as object (
  2     id int
  3  );
  4  /

hello
is anybody there?
^Ccreate type testtype as object (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

In this case, the lines hello and is anybody there? were ignored by SQL*Plus since it was busy when I typed them in. (It may choose to later read them in - they will be available to read from its standard input - but in this case it chose not to.)

To get the create type statement to run for a few seconds and not complete, I created the following (appropriately-named) trigger:

CREATE OR REPLACE TRIGGER utterly_stupid
  BEFORE CREATE ON SCHEMA
BEGIN
  IF UPPER(ora_dict_obj_name) = 'TESTTYPE' THEN
    LOOP
      NULL;
    END LOOP;
  END IF;
END;
/

This trigger causes an infinite loop if you attempt to create an object called 'TESTTYPE'.

I'm not saying that you have a trigger that's as stupid as this in your database, but you may want to take a look at any BEFORE CREATE or AFTER CREATE triggers in your database.

Another possibility may be an AFTER SERVERERROR ON DATABASE trigger. Perhaps your create type statement is triggering an error, and causing one of these triggers to fire. Are there any of these in your database?

A session that appears to hang is usually caused by locking. If another user has a lock on a row or a table that you're trying to update, your session will block until they release their lock. But it's difficult to see what could be blocking a create type statement on its own, which is why I suggested triggers. You can also try reading this article on locking and blocking in Oracle.

I can't believe the problem is due to the type already existing or you not having permission to create a type. In both cases you should get an error straight away.

---

There must be something you are not telling us:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> CREATE TYPE testtype AS OBJECT (
  2      id int
  3  );
  4  /

Type created.

SQL>

---

A saw a behaviour like this and that when there were open transactions (and hence locks) on a table.

The sqlplus commandline will happily start executing and then wait forever.

Solution: commit/rollback and close other sessions