CODEWITHSUNDEEP
sqloracle-databaseplsql
Ady96
Ady96

Reputation: 716

Creating database table in PL/SQL procedure

I am trying to create table with procedure CREATE_TABLE and then insert the information into the created table with procedure PRINT_INFO but I am getting an exception:

Errors: PROCEDURE PRINT_INFO Line/Col: 4/3 PL/SQL: SQL Statement ignored Line/Col: 4/15 PL/SQL: ORA-00942: table or view does not exist

Errors: PROCEDURE CREATE_TABLE Line/Col: 5/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

And here is my code example:

CREATE OR REPLACE PROCEDURE PRINT_INFO
IS
BEGIN
  INSERT INTO TABLE_T (TABLE_ID, MESSAGE) VALUES (1, 'Hello World!');
END PRINT_INFO;
/

CREATE OR REPLACE PROCEDURE CREATE_TABLE
IS
BEGIN

  CREATE TABLE TABLE_T(
    TABLE_ID NUMBER NOT NULL,
    MESSAGE VARCHAR2(25), 
    PRIMARY KEY(TABLE_ID)
  );
  
  PRINT_INFO;
  
END CREATE_TABLE;
/

EXEC CREATE_TABLE;

Where could be the problem? How can I get rid of an exception?

Upvotes: 0

Views: 4780

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

Both procedures have to use dynamic SQL:

  • print_info because it inserts into a table which - at compilation time - doesn't exist yet
  • create_table because it runs DDL and - in order to do that - you need to use dynamic SQL

Therefore:

SQL> CREATE OR REPLACE PROCEDURE PRINT_INFO
  2  IS
  3  BEGIN
  4    execute immediate q'[INSERT INTO TABLE_T (TABLE_ID, MESSAGE) VALUES (1, 'Hello World!')]';
  5  END PRINT_INFO;
  6  /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE CREATE_TABLE
  2  IS
  3  BEGIN
  4    execute immediate 'CREATE TABLE TABLE_T(' ||
  5      'TABLE_ID NUMBER NOT NULL, ' ||
  6      ' MESSAGE VARCHAR2(25), '    ||
  7      ' PRIMARY KEY(TABLE_ID) '    ||
  8      ')';
  9
 10    PRINT_INFO;
 11  END CREATE_TABLE;
 12  /

Procedure created.

SQL> EXEC CREATE_TABLE;

PL/SQL procedure successfully completed.

SQL> SELECT * FROM table_t;

  TABLE_ID MESSAGE
---------- -------------------------
         1 Hello World!

SQL>

Upvotes: 5

Abdulmohsen Almasoud
Abdulmohsen Almasoud

Reputation: 66

You have to declare a string and assign your ddl to that variable then use execute immediate your_variable ;

Upvotes: 0

Related Questions