Insert multiple rows in a oracle database from java using getGeneratedKeys() in one statement

Question

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?

Have you tried using batch execution instead? I'm not sure if generated keys retrieval is supported for batch execution by the Oracle driver (given JDBC defines that as optional/implementation-specific), but it is likely better than generating your own query string. — Mark Rotteveel

Littlefoot Littlefoot · Accepted Answer · 2018-11-20T21:15:16

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.

Insert multiple rows in a oracle database from java using getGeneratedKeys() in one statement

2 Answers