Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Bind variables in anonymous PL/SQL blocks.
(Oracle 8.1.7 and JDBC 2.0)
Hello, all!
I'm helping to investigate some potential performance enhancements with my company's application and I've got a couple of questions that I hope you all can help me with. We employ a 3-tier architecture where our 3rd tier is the database access tier. All of the business logic and queries reside in this tier (Java-based) and not in the database (in the form of stored procedures, for example).
We've identified a number of "business units" in this tier which may make a
number of database calls (sometimes as many as 30-40 queries). We also try
to make heavy use of bind variables wherever possible to leverage Oracle's
query preprocessing capability. We are hoping to be able to somehow "batch"
these 30-40 statements together instead of submitting them one-at-a-time.
It appears that the batch facility of JDBC 2.0 works great for identical
statements, but it seems to fall a little short with different types of
statements. (See Section 3.4 of
http://developer.java.sun.com/developer/Books/JDBCTutorial.)
So...we were thinking of potentially "wrapping" these 30-40 statements into an anonymous PL/SQL block and making a single submission to the database. But, I wasn't sure if this approach would allow us the ability to incorporate bind variables.
If anyone has any ideas/suggestions and code samples, I'd *greatly* appreciate the feedback! :-)
Regards,
John Peterson Received on Thu May 31 2001 - 13:05:19 CDT