Home » SQL & PL/SQL » SQL & PL/SQL » Create Table error (11g)
Create Table error [message #656581] Tue, 11 October 2016 15:16 Go to next message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
In an attempt to learn about nesting tables, I was trying to run this code in an anonymous block, (not necessarily as part of a full package) and the "CREATE TYPE" statement errors out because an error is thrown on that line with an ORA-6550O error. I'm sure I'm making a simple mistake somewhere. Ideas?


BEGIN
--create the sub table...
CREATE TYPE tblSubCOURSELIST88 AS TABLE OF VARCHAR2(64);

-- drop main table  
DROP Table RLN_TBLMAINDEPT88;

--create main table
CREATE TABLE RLN_TBLMAINDEPT88 
  (
    DEPT_NAME     VARCHAR2(20),
    DEPT_DIRECTOR VARCHAR2(20),
    DEPT_OFFICE   VARCHAR2(20),
    COURSES       TBLCOURSELIST88
  ) 
    NESTED TABLE COURSES STORE AS RLN_tblSubDEPT88COURSES;

--insert first set into the main table and courses into the sub table
   INSERT INTO RLN_TBLMAINDEPT88
    VALUES('English3', 'Lynn Saunders3', 'Breakstone Hall 23A',
       tblSubCOURSELIST88
           (
             'Expository Writing3' 
            ,'Film and Literature3'
            ,'Modern Science Fiction3'
            ,'Discursive Writing3'
            ,'Modern English Grammar3'
           )
          );

--insert the second set into the main table and courses into the sub table
   INSERT INTO RLN_TBLMAINDEPT88
      VALUES('Music4', 'Clint Ashlock4', 'Yardley Hall 2053',
         tblSubCOURSELIST88
              (
                'Jazz History4'
               ,'Contemporary Jazz4'
               ,'19th Century Jazz4'
               ,'Jazz Fusion4'
               ,'Jazz Improvisational Theory4'
              )
           );

   COMMIT;
    
END;
Re: Create Table error [message #656582 is a reply to message #656581] Tue, 11 October 2016 15:21 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
If you want to run DDLs,you have to use dynamic SQL. Look up EXECUTE IMMEDIATE.

It is logically impossible to do DDL the way you trying to, because PL/SQL is a compiled language, and at compile time the objects don't exist.
Re: Create Table error [message #656583 is a reply to message #656581] Tue, 11 October 2016 15:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2699
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can't issue DDL directly in PL/SQL. And PL/SQL isn't needed here at all. Also, you created type tblSubCOURSELIST88 while declaring column COURSES as TBLCOURSELIST88. Anyway:

SQL> CREATE OR REPLACE
  2    TYPE tblSubCOURSELIST88
  3      AS TABLE OF VARCHAR2(64)
  4  /

Type created.

SQL> DROP Table RLN_TBLMAINDEPT88 PURGE
  2  /
DROP Table RLN_TBLMAINDEPT88 PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE RLN_TBLMAINDEPT88
  2    (
  3      DEPT_NAME     VARCHAR2(20),
  4      DEPT_DIRECTOR VARCHAR2(20),
  5      DEPT_OFFICE   VARCHAR2(20),
  6      COURSES       tblSubCOURSELIST88
  7    )
  8      NESTED TABLE COURSES STORE AS RLN_tblSubDEPT88COURSES
  9  /

Table created.

SQL>    INSERT INTO RLN_TBLMAINDEPT88
  2      VALUES('English3', 'Lynn Saunders3', 'Breakstone Hall 23A',
  3         tblSubCOURSELIST88
  4             (
  5               'Expository Writing3'
  6              ,'Film and Literature3'
  7              ,'Modern Science Fiction3'
  8              ,'Discursive Writing3'
  9              ,'Modern English Grammar3'
 10             )
 11            )
 12  /

1 row created.

SQL>    INSERT INTO RLN_TBLMAINDEPT88
  2        VALUES('Music4', 'Clint Ashlock4', 'Yardley Hall 2053',
  3           tblSubCOURSELIST88
  4                (
  5                  'Jazz History4'
  6                 ,'Contemporary Jazz4'
  7                 ,'19th Century Jazz4'
  8                 ,'Jazz Fusion4'
  9                 ,'Jazz Improvisational Theory4'
 10                )
 11             )
 12  /

1 row created.

SQL> COMMIT
  2  /

Commit complete.

SQL> SELECT  *
  2    FROM  RLN_TBLMAINDEPT88
  3  /

DEPT_NAME            DEPT_DIRECTOR        DEPT_OFFICE
-------------------- -------------------- --------------------
COURSES
------------------------------------------------------------------------------------------------------------------------------------

English3             Lynn Saunders3       Breakstone Hall 23A
TBLSUBCOURSELIST88('Expository Writing3', 'Film and Literature3', 'Modern Science Fiction3', 'Discursive Writing3', 'Modern English
Grammar3')

Music4               Clint Ashlock4       Yardley Hall 2053
TBLSUBCOURSELIST88('Jazz History4', 'Contemporary Jazz4', '19th Century Jazz4', 'Jazz Fusion4', 'Jazz Improvisational Theory4')


SQL>

SY.

Re: Create Table error [message #656598 is a reply to message #656583] Wed, 12 October 2016 07:24 Go to previous message
Irish88
Messages: 36
Registered: August 2016
Location: Plains of Kansas
Member
>>You can't issue DDL directly in PL/SQL.
And PL/SQL isn't needed here at all. <<

two good points here, Solomon; I was not aware of the first one, thank you.
Previous Topic: Unable to Recreate the Procedure
Next Topic: Anonymous block in sql plus
Goto Forum:
  


Current Time: Tue Jan 23 05:02:20 CST 2018

Total time taken to generate the page: 0.06468 seconds