Htykuut

I already found out about the INSERT ALL syntax and it works fine as long I don't want to retrieve the inserted id values.

As an opposite to INSERT ALL syntax I could just use mutliple INSERT INTO statements in a transaction which would work but is bad for performance as stated here: Best way to do multi-row insert in Oracle?.

This is my current code:

//Creation of INSERT INTO statement

//...    

Statement statement = dbConnection.createStatement();

statement.executeUpdate(INSERT_SQL, new String {"someIDColumn"});

ResultSet idResulSet = statement.getGeneratedKeys();

//Usage of the generated keys

It works for a single row but if I try the INSERT ALL syntax I get an:

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Caused by: Error : 933, Position : 187, Sql = INSERT ALL INTO bpos(artnr, bstnr, menge) VALUES (3, 31, 4) INTO bpos(artnr, bstnr, menge) VALUES (5, 31, 6) INTO bpos(artnr, bstnr, menge) VALUES (1, 31, 2) SELECT * FROM dual RETURNING artnr INTO :1 , OriginalSql = INSERT ALL INTO bpos(artnr, bstnr, menge) VALUES (3, 31, 4) INTO bpos(artnr, bstnr, menge) VALUES (5, 31, 6) INTO bpos(artnr, bstnr, menge) VALUES (1, 31, 2) SELECT * FROM dual RETURNING artnr INTO ?, Error Msg = ORA-00933: SQL command not properly ended

Is it possible to retrieve all inserted ids after an INSERT ALL statement?

Well, as far as I can tell, it is possible, but not directly (as you could do it with updates or deletes); a little workaround has to be used.

Here's an example:

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



Table created.



SQL> declare

  2    type    tt_test is table of test%rowtype index by binary_integer;

  3    l_test  tt_test;

  4    l_id    sys.odcinumberlist;

  5  begin

  6    select id, name

  7      bulk collect into l_test

  8      from (select 111 id, 'Little' name from dual union all

  9            select 222 id, 'Foot'   name from dual

 10           );

 11

 12    forall i in l_test.first .. l_test.last

 13      insert into test (id, name) values (l_test(i).id, l_test(i).name)

 14      returning l_test(i).id bulk collect into l_id;

 15

 16    for i in l_id.first .. l_id.last loop

 17      dbms_output.put_line('Inserted ID = ' || l_id(i));

 18    end loop;

 19  end;

 20  /

Inserted ID = 111

Inserted ID = 222



PL/SQL procedure successfully completed.



SQL>

I don't know, though, can you use it in your (Java?) code as I don't speak that language.