Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
answered in : dba.stackexchange.com/questions/1305/… –  Joe Mar 8 '11 at 13:04
2  
No the question Why is not answered there. It seems to be an arbitrary design decision of the sql*plus team. –  bernd_k Mar 8 '11 at 13:13
add comment

1 Answer

up vote 8 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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