Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Bind variables in anonymous PL/SQL blocks.

Bind variables in anonymous PL/SQL blocks.

From: John Peterson <johnp_at_azstarnet.com>
Date: Thu, 31 May 2001 11:05:19 -0700
Message-ID: <thd1v1ggho701b@corp.supernews.com>

(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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US