execute several queries in one variable

Question

I have in a variable DDL and DML queries.

example:

 var1='insert into tb1 (id) values(1); insert into tb1 (id) values(2); insert into tb1 (id) values(3);'

I want to execute the var1 that contains several inserts. how to do that? execute immediate didnt worked.

Edit: I tryd this , It didnt work because there is ';' inside it.

 DECLARE COL_COUNT NUMBER;
 BEGIN
 EXECUTE IMMEDIATE ('insert into ex_employee (id) values (3); insert into ex_employee (id) values (4);');
 END;
 /

I made a slight change to your version. Kindly check the same. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jun 23, 2014 at 14:11

Maheswaran Ravisankar · Accepted Answer · 2014-06-23 14:20:50Z

2

Embed your Inserts into a BEGIN-END anonymous block like one below.

Oracle document

DECLARE COL_COUNT NUMBER;
 BEGIN
 EXECUTE IMMEDIATE 'BEGIN insert into ex_employee (id) values (3); insert into ex_employee (id) values (4); END;';
 EXCEPTION
 WHEN OTHERS THEN
        --Handle Exception here.
 END;
 /

edited Jun 23, 2014 at 14:20

answered Jun 23, 2014 at 14:08

Maheswaran Ravisankar

17.9k6 gold badges51 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maheswaran Ravisankar Over a year ago

Yes, same like a Normal PL/SQL. Exception inside the dynamic execution, would be thrown to the Executor block itself.

Moudiz Over a year ago

ah tahts great , another question IF it was several blocks example: execute immediate 'begin ... end; begin ... end;' would it work ?

Maheswaran Ravisankar Over a year ago

No,instead you can again nest the begin-end; like begin-begin-end;begin-end;end; So, it looks like only BEGIN-END

Collectives™ on Stack Overflow

execute several queries in one variable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related