Oracle prepare statement batch mode

marrowyung
marrowyung used Ask the Experts™
on
hi,

I am reading:

https://www.oracle.com/technetwork/testcontent/jdbc-ch5-131209.pdf

https://docs.oracle.com/database/121/JJDBC/oraperf.htm#JJDBC28767

for Oracle prepared statement.

and I do not understand what it means as it says:

Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.

Open in new window


when I check standard JDBC batching it says:

The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Even though Oracle JDBC supports the use of standard batching for Statement and CallableStatement objects, you are[b][u] unlikely[/u][/b] to see performance improvement.

Open in new window


so in order to use a  REAL batch mode for PL/SQL prepare statement, what should I do ?

or it means must be handle in java library when calling:

...
Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO emp VALUES(1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO dept VALUES(260, 'Sales')");
stmt.addBatch("INSERT INTO emp_dept VALUES(1000, 260)");
...

Open in new window


?

then it still related to prepared statement ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Franck PachotOracle DBA

Commented:
Hi,

For prepared statement it will be something like:

...
PreparedStatement stmt = conn.prepareStatement("INSERT INTO emp VALUES(?, ?)");
stmt.setInt(1,1000);
stmt.setString(2,'Joe Jones');
stmt.addBatch();
stmt.setInt(1,260);
stmt.setString(2,'Salues');
stmt.addBatch();
...

AFAIK the doc you mention say there's no improvement for generic (no bind)  and callable (stored functions) statements. But I think there is for prepared statements.

Regards,
Franck.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

"But I think there is for prepared statements."

that add batch if for prepared statement and it list it down, but for me, what I wan to know is, oracle standard batching is different from Java standard batching , right?

so what is the REAL and BEST way to do prepare statement by batch ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial