Skip to Main Content
  • Questions
  • Multiple SQL statements in a single Execute Immediate


Question and Answer

Chris Saxon

Thanks for the question, Terry.

Asked: October 23, 2015 - 11:06 am UTC

Answered by: Chris Saxon - Last updated: October 23, 2019 - 4:45 pm UTC

Category: Developer - Version:

Viewed 10K+ times! This question is

You Asked

What is the exact syntax to be able to execute multiple sql statements from within a single execute immediate statement.

and we said...

If you want to do this, you need to wrap the SQL statements in a PL/SQL begin ... end block.

  l1 integer;
  l2 integer;
  execute immediate 'begin
    select 1 into :out1 from dual;
    select 2 into :out2 from dual;
  using out l1, out l2;
  dbms_output.put_line('l1 = ' || l1);
  dbms_output.put_line('l2 = ' || l2); 

PL/SQL procedure successfully completed.
l1 = 1
l2 = 2

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review


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.
Chris Saxon


October 23, 2019 - 4:45 pm UTC

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:

  l1 integer;
  execute immediate q'!
    execute immediate 'create table t ( x int )';
    execute immediate 'insert into t values ( 1 )';
    execute immediate 'select x from t'
      into :l1;
  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...

More to Explore


More on PL/SQL routine DBMS_OUTPUT here