0

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?

Improve this question
1
  • @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. Commented Feb 28, 2021 at 13:22

2 Answers 2

Reset to default
5

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>
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

4 Comments

no you do not have to declared a string and assign your ddl to it. You can just as easily "execute immediate 'select my_col from my_table' ; " That said, I prefer to assign it to a varaible, so that I can also reference it in dbms_output.put_line for debugging.
I would go a step further than @EdStevens. Putting the actual statement in a variable allows me to log the actual statement is case of an exception. But this should NOT be in a procedure anyway.
@Belayer - "But this should NOT be in a procedure anyway". Correct - for production code. But I did say "for debugging". We put lots of stuff in code for debugging that we take out when debugging is complete and were are going to production.
@EdStevens - Yes. I too. But the point putting the statement into a variable is useful for more than debugging.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.