14

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?

15

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).

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.