table or view does not exist while updating a table from private temporary

Question

Facing with issue with executing multiple statements, I assume issue appearing while trying to update from temp table to existing table, having the exception like ORA-00942: table or view does not exist with the following query

begin
    execute immediate 'CREATE PRIVATE TEMPORARY TABLE ORA$PTT_TMP AS SELECT ID,NAME from USERS WHERE 1 = 0';
    execute immediate 'INSERT INTO ORA$PTT_TMP (ID,NAME) VALUES (:1, :2)' using 1,'NAME';
    UPDATE USERS2 t1
    SET (t1.NAME) = (SELECT t2.NAME FROM ORA$PTT_TMP t2 WHERE t1.ID = t2.ID);
    execute immediate 'DROP TABLE ORA$PTT_TMP';
end;

What I'm doing wrong here

Answer 1

The problem is that Oracle has to compile the entire block before it can execute it. When it tries to compile the block, it sees the reference to ORA$PTT_TMP in your update statement, fails to resolve that reference, and so the entire block fails to compile.

If you want to create an object at runtime, all references to the object in the same block would need to defer compilation until runtime. That means using dynamic SQL (execute immediate or dbms_sql) to execute the update statement as well. Or, depending on the situation, you could have one PL/SQL block that dynamically creates the object and a second PL/SQL block that contains static references to the object.