EXECUTE IMMEDIATE statement for table creation doesn't work

Question

I have to write a stored procedure that creates a table which is used to record the type and X and Y coordinates of a tree. I understand you must use the EXECUTE IMMEDIATE statement in order to create the table Here's the code:

Create or replace Procedure add_populate_trees 
(p_treenum in NUMBER)
IS
v_loop_counter NUMBER := 0;
v_artari_counter NUMBER :=0;
v_mesteceni_counter NUMBER :=0;
v_fagi_counter NUMBER :=0;
v_arini_counter NUMBER :=0;
v_stejari_counter NUMBER :=0;
BEGIN
EXECUTE IMMEDIATE 'drop table copaci';
EXECUTE IMMEDIATE 'create table copaci (Tip_copac VARCHAR2(25), Coord_X NUMBER(5), Coord_Y NUMBER(5))';
WHILE v_loop_counter <= p_treenum LOOP
    INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values('Artar',DBMS_RANDOM.value(0,1000000),DBMS_RANDOM.value(0,1000000));
    v_artari_counter := v_artari_counter + 1;
    v_loop_counter :=v_loop_counter + 1;
INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values('Mesteacan',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000));
    v_mesteceni_counter := v_mesteceni_counter + 1;
    v_loop_counter := v_loop_counter + 1;


    INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values('Fag',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000));
    v_fagi_counter := v_fagi_counter + 1;
    v_loop_counter := v_loop_counter + 1;


    INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values('Arin',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000));
    v_arini_counter := v_arini_counter + 1;
    v_loop_counter := v_loop_counter + 1;


    INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values('Stejar',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000));
    v_stejari_counter := v_stejari_counter + 1;
    v_loop_counter := v_loop_counter + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_artari_counter||'artari');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_mesteceni_counter||'mesteceni');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_fagi_counter||'fagi');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_arini_counter||'arini');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_stejari_counter||'stejari');
end;

Followed by similar inserts and variable increment, however when I execute this I get the error for the insert that the table does not exist, which can only mean the table was not created. Thanks in advance for any help

Allan · Answer 1 · 2022-01-19T17:39:00.287

If the table doesn't exist when you initially try to compile and execute the procedure, you will get an error. This is because PL/SQL validates against the objects it references at compile time. The only way to reference a table that might exist is to use EXECUTE IMMEDIATE. The simplest way to fix this is to run your create table script before you initially create the procedure.

However, with Oracle, it's generally a bad idea to issue DDL from procedural code. Rather than dropping and recreating a table every time you use it, the best way to handle this kind of requirement is to create a global temporary table (GTT) once and write your code to reference it. A GTT's values are automatically removed either when you commit or when the session is ended (depending on the options you choose when creating it), so it is always clean when it is first accessed within a session.

The only way to access a not-yet-existent table in a procedure is to change every reference to it into an EXECUTE IMMEDIATE. Since you're not passing any variable to the insert commands, that should just be a matter of turning them into strings.

I would like to create the table before executing the script however the educational assignment given to me states clearly that I must create the table within the procedure, I assume that means it is possible — user3402719, Mar 19 '15 at 18:42
If you have interpreted the assignment correctly, then it's a terrible assignment. — Allan, Mar 19 '15 at 18:45
Well.. I don't know if someone can misinterpret "the procedure must create the table with the type of tree and X and Y coordinates of an axis" — user3402719, Mar 19 '15 at 18:48

score 0 · Accepted Answer · edited May 23 '17 at 12:06

Does the 'copaci' table exist before you are calling the procedure? If not it will die once you try to issue the 'drop table copaci' command. Wrap your drop statement to see if that's what's happening and to allow your procedure to continue:

BEGIN
  EXECUTE IMMEDIATE 'drop table copaci';
EXCEPTION WHEN OTHERS THEN
  dbms_output.put_line('exception occurred because the copaci table did not exist');
END;

If you're encountering an Insufficient Privileges error, have a look at this answer: Execute Immediate within a stored procedure keeps giving insufficient priviliges error

If that's your issue, you need to add the AUTHID:

Create or replace Procedure add_populate_trees 
(p_treenum in NUMBER)
AUTHID CURRENT_USER
IS
v_loop_counter NUMBER := 0;
v_artari_counter NUMBER :=0;
v_mesteceni_counter NUMBER :=0;
v_fagi_counter NUMBER :=0;
v_arini_counter NUMBER :=0;
v_stejari_counter NUMBER :=0;
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'drop table copaci';
  EXCEPTION WHEN OTHERS THEN
    NULL;
  END;  
EXECUTE IMMEDIATE 'create table copaci (Tip_copac VARCHAR2(25), Coord_X NUMBER(10), Coord_Y NUMBER(10))';
WHILE v_loop_counter <= p_treenum LOOP
    EXECUTE IMMEDIATE 'INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values(''Artar'',DBMS_RANDOM.value(0,1000000),DBMS_RANDOM.value(0,1000000))';
    v_artari_counter := v_artari_counter + 1;
    v_loop_counter :=v_loop_counter + 1;
    EXECUTE IMMEDIATE 'INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values(''Mesteacan'',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000))';
    v_mesteceni_counter := v_mesteceni_counter + 1;
    v_loop_counter := v_loop_counter + 1;


    EXECUTE IMMEDIATE 'INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values(''Fag'',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000))';
    v_fagi_counter := v_fagi_counter + 1;
    v_loop_counter := v_loop_counter + 1;


    EXECUTE IMMEDIATE 'INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values(''Arin'',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000))';
    v_arini_counter := v_arini_counter + 1;
    v_loop_counter := v_loop_counter + 1;


    EXECUTE IMMEDIATE 'INSERT INTO copaci (Tip_copac, Coord_X, Coord_Y) Values(''Stejar'',DBMS_RANDOM.value(0,1000),DBMS_RANDOM.value(0,1000))';
    v_stejari_counter := v_stejari_counter + 1;
    v_loop_counter := v_loop_counter + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_artari_counter||'artari');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_mesteceni_counter||'mesteceni');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_fagi_counter||'fagi');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_arini_counter||'arini');
DBMS_OUTPUT.PUT_LINE('In padure au fost plantati '||v_stejari_counter||'stejari');
end;

Note that the values you're trying to insert don't fit into your columns, so I had to increase the size to number(10).

I did.. however I get the same error... and earlier on I tried without the drop table statement with the same result — user3402719, Mar 19 '15 at 17:15
PL/SQL: SQL Statement ignored PL/SQL: ORA-00942: table or view does not exist these are the errors and I get a pair of these for each insert statement that I have in that code.. plus I ran your code above and it works just fine... and after I added a loop and some insert statements I got this error again.. I will edit and provide the full code — user3402719, Mar 19 '15 at 18:24
The updated procedure won't even compile if the table doesn't exist already. You would need to use EXECUTE IMMEDIATE for the inserts. — Mark Leiber, Mar 19 '15 at 19:05
Thanks mate it works like a charm and since I have to insert at least 1 million rows I will have to wait a bit but who cares. Thanks a bunch again and good luck — user3402719, Mar 20 '15 at 08:10

EXECUTE IMMEDIATE statement for table creation doesn't work

2 Answers2