using sequence in Dynamic sql

Question

I have a dynamic sql :

forall k in 1..Job_ID.COUNT Save exceptions
EXECUTE IMMEDIATE 'insert into XYZ values(:1,:2,:3)' using sequence_one.NextVal,job_id(k),Name(k);
 ///Exception handling.

On running the abouve query only one row is getting inserted.And following error is thrown:

ORA-24381: error(s) in array DML.

Any idea why above thing is happening?

Tony Andrews · Accepted Answer · 2011-07-14 10:30:48Z

4

Do you really want every inserted row to have the same sequence value in the first column? If not you should do this:

forall k in 1..Job_ID.COUNT Save exceptions
  EXECUTE IMMEDIATE 'insert into XYZ values(sequence_one.NextVal,:2,:3)'
    using job_id(k),Name(k);

Or even better (unless you have a good reason for using dynamic SQL:

forall k in 1..Job_ID.COUNT Save exceptions
  insert into XYZ values(sequence_one.NextVal, job_id(k), Name(k));

answered Jul 14, 2011 at 10:30

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

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

Collectives™ on Stack Overflow

using sequence in Dynamic sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related