create table via execute immediate does not work

Question

I'm experiencing a problem with the PLSQL block below. When the code is executed as written below (without the loop) the script is successful. However when the loop is uncommented I get an error that the table in the select statement was not found. When I check, I find that indeed the table was not created at all. It's almost as if the loop tries to runs before the execute immediate.

Yes I am aware that since the table is not populated anyway the print statement would not appear regardless however I would not expect the query to fail.

begin
    execute immediate 'create table tbl_temp (oid_value number(20) ,rowid_field varchar2(20))';

    /*for nullval in (select oid_value from tbl_temp)
    loop
        DBMS_OUTPUT.PUT_LINE('something');
    end loop;*/
end;

Answer 1

When you try to run this code, Oracle first checks if it is correct, thus checking if all the tables it uses exist.

At the beginning, your table does not exist, so this code will give an error and nothing will be done.

If you want to create a table on the fly and then make a query on it ( and I believe this is not a great idea), you could use a cursor based on a dynamic SQL; for example:

SQL> select * from tbl_temp;
select * from tbl_temp
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> declare
  2      cur sys_refcursor;
  3      val number(20);
  4  begin
  5      execute immediate 'create table tbl_temp (oid_value number(20) ,rowid_field varchar2(20))';
  6
  7      open cur for 'select oid_value from tbl_temp';
  8      loop
  9          fetch cur into val;
 10          exit when cur%notfound;
 11          dbms_output.put_line('val = ' || val);
 12      end loop;
 13  end;
 14
 15
 16  /

PL/SQL procedure successfully completed.

SQL> select * from tbl_temp;

no rows selected

SQL>