Creating database table in PL/SQL procedure

Question

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?

@Littlefoot has given you the technical reason for your errors. I'd go further and suggest that there is no reason to be writing PL/SQL procedures to create tables. Table creation should be a one-time activity that can/should be done with straight sql. Is this a class project? I could swear I've seen this same question in the last couple of days. — EdStevens

Littlefoot Littlefoot · Accepted Answer · 2021-02-28T11:12:59

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>

Creating database table in PL/SQL procedure

2 Answers