plsql - execute immediate create table and update table

Question

I am creating a temp table in pl/sql using execute immediate & also inserting in the table why create table.

After that I m updating the table. But i m getting error table doesn't exists as it is not creating the table thr execute immediate

sample code---------

begin
  execute immediate 'create table t23 as  select ''1'' aa from dual'; 
  update t23 set aa ='2' where aa='1';
  COMMIT ;
end;

Answer 1

You are using static SQL to perform the update, and this is validated before the PL/SQL is run, and so finds that it references a table that doesn't currently exist. You could use dynamic SQL to perform the update:

begin
  execute immediate 'create table t23 as  select ''1'' aa from dual'; 
  execute immediate 'update t23 set aa =''2'' where aa=''1''';
  COMMIT ;
end;

However, really it is bad practice in Oracle to dynamically create temporary tables like this in the first place. Why are you doing it? Once we know that perhaps we can suggest a better alternative.