Skip to Main Content
  • Questions

  • Multiple SQL statements in a single Execute Immediate

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Terry.

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

Last updated: September 03, 2020 - 2:53 am UTC

Version: 11.2.0.3

Viewed 50K+ 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 Chris said...

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

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

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

Rating

  (2 ratings)

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

Comments

How about other types of stements

tractor boy, October 23, 2019 - 1:06 pm UTC

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:

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

not showing in OEM

vep, September 02, 2020 - 12:24 pm UTC

Tom, I have the the similar case, not sneaking in the query but how to ensure its real parallel

begin
execute immediate q'!
begin
execute immediate 'update table1';
execute immediate 'update table2';
execute immediate 'update table3';

end;!'


Each update runs for 5 mins so I would expect everything to complete in less than 6 mins atleast.

When I checked in OEM, its running in sequence however the query completes in 7 to 8 mins.

Anything, I am missing? please help
Connor McDonald
September 03, 2020 - 2:53 am UTC

Use jobs to achieve this.

declare
  j number;
begin
  dbms_job.submit(j,'begin update table1; end;');
  dbms_job.submit(j,'begin update table2; end;');
  dbms_job.submit(j,'begin update table3; end;');
  commit;
end;


They will each run as separate sessions




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library