The
best approach is AVOID execute immediate. And just use regular static SQL in PL/SQL.
If you want to create a table and insert/select it, you can wrap execute immediates inside execute immediates:
declare
l1 integer;
begin
execute immediate q'!
begin
execute immediate 'create table t ( x int )';
execute immediate 'insert into t values ( 1 )';
execute immediate 'select x from t'
into :l1;
end;!'
using out l1;
dbms_output.put_line('l1 = ' || l1);
end;
/
l1 = 1
Yuck!
If you find yourself trying to do this, it's time to walk away and rethink the problem...