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

Breadcrumb

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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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: 11.2.0.3

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Attribute clustering (part 3)

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.

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

and you rated our response

  (1 rating)

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

Reviews

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

Followup  

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

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here