is there any way to get the values of affected rows using RETURNING INTO ? I have to insert the same rows x times and get the ids of inserted rows.
The query looks like below:
public static final String QUERY_FOR_SAVE =
"DECLARE " +
" resultId NUMBER ; " +
"BEGIN " +
" INSERT INTO x " +
" (a, b, c, d, e, f, g, h, i, j, k, l, m) " +
" values (sequence.nextVal, :a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l) " +
" RETURNING a INTO :resultId;" +
"END;";
Now i can add thise query to batch, in JAVA loop using addBatch
IntStream.range(0, count)
.forEach(index -> {
try {
setting parameters...
cs.addBatch();
} catch (SQLException e) {
e.printStackTrace();
}
});
cs.executeBatch();
Is there any way to return an array or list from batch like this ? I can execute those insert x times using just sql but in this case i also wondering how to return an array of ids.
Thanks in advance
I'm assuming this is about Oracle. To my knowledge, this isn't possible, but you can run a bulk insertion using
FORALL
in your anonymous PL/SQL block, as described in this article I wrote, recently: https://blog.jooq.org/2018/05/02/how-to-run-a-bulk-insert-returning-statement-with-oracle-and-jdbc/This is a self-contained JDBC example from the article that inserts an array of values and bulk collects the results back into the JDBC client: