1

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?

Community
  • 1
  • 1
patvax
  • 343
  • 3
  • 14
  • 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 Nov 21 '18 at 08:48

2 Answers2

0

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • This could work but you have used the INSERT INTO syntax which means that I'd have to do an insert for every row i want to insert. What I am looking for is the usage of INSERT ALL INTO ... statement (or something similar to it) mainly for performance reasons. I can imagine it can't be done because INSERT ALL INTO can insert values into different tables. – patvax Nov 20 '18 at 21:40
  • Your INSERT ALL is substituted by a combination of BULK COLLECT and FORALL (the first and the second statement in my code). As I've said, I don't think that INSERT ALL, itself, can be used with the RETURNING INTO clause. – Littlefoot Nov 20 '18 at 21:42
0

use:

statement.executeBatch

not:

statement.executeUpdate
Khalid
  • 1