How about other types of stements
October 23, 2019 - 1:06 pm UTC
Reviewer: tractor boy from UK
How does this extend to select, insert, update, delete, create statements being run at the same time.
Or more so what is the best approach. One execute immediate with multiple statements of multiple execute immediate each with a single statement.
Does this differ to allow for exception handling including commit and rollback.
October 23, 2019 - 4:45 pm UTC
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:
execute immediate q'!
execute immediate 'create table t ( x int )';
execute immediate 'insert into t values ( 1 )';
execute immediate 'select x from t'
using out l1;
dbms_output.put_line('l1 = ' || l1);
l1 = 1
If you find yourself trying to do this, it's time to walk away and rethink the problem...