Why EXECUTE IMMEDIATE is needed here?

Question

I am a SQL Server user and I have a small project to do using Oracle, so I’m trying to understand some of the particularities of Oracle and I reckon that I need some help to better understand the following situation:

I want to test if a temporary table exists before creating it so I had this code here:

DECLARE
  table_count INTEGER;
  var_sql VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (
            hello varchar(1000) NOT NULL)';
BEGIN
  SELECT COUNT(*) INTO table_count FROM all_tables WHERE table_name = 'TEST';

  IF table_count = 0 THEN
    EXECUTE IMMEDIATE var_sql;
  END IF;
END;

It works normally, so after I executed it once, I added an else statement on my IF:

ELSE
  insert into test (hello) values ('hi');

Executed it again and a line was added to my test table.

Ok, my code was ready and working, so I dropped the temp table and tried to run the entire statement again, however when I do that I get the following error:

ORA-06550: line 11, column 19:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 11, column 7:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Then I changed my else statement to this and now it works again:

ELSE
  EXECUTE IMMEDIATE 'insert into test (hello) values (''hi'')';

My question is why running individually I can simply use the insert instead of the EXECUTE IMMEDIATE and also why my SELECT statement right after BEGIN still works when all the rest appears to need EXECUTE IMMEDIATE to run properly?

Answer 1

The whole PL/SQL block is parsed at compile time, but the text within a dynamic statement isn't evaluated until runtime. (They're close to the same thing for an anonymous block, but still distinct steps).

Your if/else isn't evaluated until runtime either. The compiler doesn't know that the table will always exist by the time you do your insert, it can only check whether or not it exists at the point it parses the whole block.

If the table does already exist then it's OK; the compiler can see it, the block executes, your select gets 1, and you go into the else to do the insert. But if it does not exist then the parsing of the insert correctly fails with ORA-00942 at compile time and nothing in the block is executed.

Since the table creation is dynamic, all references to the table have to be dynamic too - your insert as you've seen, but also if you then query it. Basically it makes your code much harder to read and can hide syntax errors - since the dynamic code isn't parsed until run-time, and it's possible you could have a mistake in a dynamic statement in a branch that isn't hit for a long time.

Global temporary tables should not be created on-the-fly anyway. They are permanent objects with temporary data, specific to each session, and should not be created/dropped as part of your application code. (No schema changes should be made by your application generally; they should be confined to upgrade/maintenance changes and be controlled, to avoid errors, data loss and unexpected side effects; GTTs are no different).

Unlike temporary tables in some other relational databases, when you create a temporary table in an Oracle database, you create a static table definition. The temporary table is a persistent object described in the data dictionary, but appears empty until your session inserts data into the table. You create a temporary table for the database itself, not for every PL/SQL stored procedure.

Create the GTT once and make all your PL/SQL code static. If you want something closer to SQL Server's local temporary tables then look into PL/SQL collections.