Multiple id's input for select query with oracle bpel dbadapter

Question

In Oracle SOA one can do insert multiple records in Database in single transaction.This is supported out of the box and nothing you need to do special to achieve it. If you create database adapter in your BPEL process with INSERT operation it is exposed as collection of object as Input. You can use XSLT to assign that Collection and all records will be inserted in one atomic transaction.

Is there an equivalent feature for pure sql query?

I have a complicated query that requires only a single id for it's input. But I like this query to be repeated for multiple id's. Rather than defining a for loop and what not, is there a flag/switch/way when creating the bpel process to allow for multiple id's as the input?

Would something like INSERT ALL fulfill this requirement? Here are some examples : techonthenet.com/oracle/questions/insert_rows.php — g00dy

Ponder Stibbons Ponder Stibbons · Accepted Answer · 2017-08-01T13:42:28

Maybe this helps:

create table test (id number(3), name varchar2(20));

insert into test 
  select t.column_value, dbms_random.string('A', 20) 
    from table(sys.odcinumberlist(4, 17, 105, 91, 212)) t;

Pure SQL, one insert, five rows with defined ids. If id is varchar use sys.odcivarchar2list or define custom type at first.

Multiple id's input for select query with oracle bpel dbadapter

1 Answers