Re: Bind variables in anonymous PL/SQL blocks.

In article <thd1v1ggho701b_at_corp.supernews.com>, "John says...
>
>(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
>
>

well, you could -- but this seems to be screaming "STORED PROCEDURE" if you ask me.

Rather then have java glue together some 30/40 odd statements, wrap a begin/end around them and then bind -- why not just bind the inputs to a stored procedure that has the 30/40 odd statements.

If the reason is "well the 30 or 40 odd statements change in the number of statments and their order" -- then you would be defeating the shared sql concept (as bad as not using bind variables at all). You would be creating a whole bunch of unique, BIG plsql blocks that others would tend to not reuse.

You could almost forget about "forcing yourself to use bind variables" by using stored procedures. It would become very very natural.

Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Thu May 31 2001 - 15:01:35 CDT