Insert multiple rows in a oracle database from java using getGeneratedKeys() in one statement
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?
java sql oracle jdbc ojdbc
asked Nov 20 '18 at 20:01
patvaxpatvax
238
add a comment |
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?
java sql oracle jdbc ojdbc
asked Nov 20 '18 at 20:01
patvaxpatvax
238
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 8:48
add a comment |
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?
java sql oracle jdbc ojdbc
asked Nov 20 '18 at 20:01
patvaxpatvax
238
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?
java sql oracle jdbc ojdbc
java sql oracle jdbc ojdbc
asked Nov 20 '18 at 20:01
patvaxpatvax
238
asked Nov 20 '18 at 20:01
patvaxpatvax
238
asked Nov 20 '18 at 20:01
patvaxpatvax
238
asked Nov 20 '18 at 20:01
patvaxpatvax
238
asked Nov 20 '18 at 20:01
patvaxpatvax
238
238
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 8:48
add a comment |
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 8:48
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 8:48
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 8:48
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
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
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53400680%2finsert-multiple-rows-in-a-oracle-database-from-java-using-getgeneratedkeys-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
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
add a comment |
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.
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
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
add a comment |
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.
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
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.
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
answered Nov 20 '18 at 21:15
LittlefootLittlefoot
23.2k71533
23.2k71533
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
add a comment |
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
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
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
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
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53400680%2finsert-multiple-rows-in-a-oracle-database-from-java-using-getgeneratedkeys-in%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 8:48