Why does SQL*PLUS need a slash after CREATE TYPE?

Question

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error.

Example:

CREATE OR REPLACE TYPE MyType AS OBJECT (
    Item1 NUMBER,
    Item2 NUMBER
);

For some reason I have to add a slash here

CREATE OR REPLACE TYPE MyType AS OBJECT (
    Item1 NUMBER,
    Item2 NUMBER
);
/

To me it looks similar to a Create Table statement, which doesn't require a slash. I find it rather confusing. I know how it works, but can anyone explain why this design decision was made?

answered in : http://dba.stackexchange.com/questions/1305/what-is-the-difference-between-terminating-an-oracle-sql-statement-with-semicolon — Joe, Mar 08 '11 at 13:04
No the question **Why** is not answered there. It seems to be an arbitrary design decision of the sql*plus team. — bernd_k, Mar 08 '11 at 13:13
There is a great answer: http://stackoverflow.com/a/10207695/1568658 — user218867, Dec 05 '16 at 05:47

score 15 · Accepted Answer · answered Mar 08 '11 at 13:29

you need a / after a PL/SQL block in SQL*Plus:

SQL> begin
  2     null;
  3  end;
  4  -- here you need a /
  5  /

PL/SQL procedure successfully completed

This is so SQL*Plus knows you are done with your statement (which could include intermediate, non-terminating ;).

SQL types may include PL/SQL code, therefore the SQL*Plus devs decided that you need a / in all cases after a CREATE TYPE:

SQL> CREATE OR REPLACE TYPE t AS OBJECT (
  2     x NUMBER,
  3     MEMBER PROCEDURE setx(p_x NUMBER)
  4  );
  5  /

Type created

SQL> CREATE OR REPLACE TYPE BODY t AS
  2     MEMBER PROCEDURE setx (p_x NUMBER) IS
  3     BEGIN
  4        x := p_x;
  5     END;
  6  END;
  7  /

Type body created

Note: you also need a / after you define a procedure, a package or a package body (for the same reason).

Why does SQL*PLUS need a slash after CREATE TYPE?

1 Answers1