Re: Bind variables in anonymous PL/SQL blocks.

Thomas,

Thank you for such a quick reply! I agree *wholeheartedly* that this is an ideal candidate for a stored procedure. I'm trying to get the necessary "buy in" from the management folks to retrofit the application to leverage stored procedures. But I think that we're still a ways off.

In the interim, I thought if we could come up with a clever way to convert our existing statements into equivalent PL/SQL *and* get the benefits of batching, that would bring us one step closer.

Looking at Oracle 8i's Native Dynamic SQL "execute immediate" functionality, I think we might have hit upon something that could work nicely!

Since we use JDBC and bind variables, our queries currently look something like:

sQuery = "insert into MyTable values (?,?,?)"

And we have a corresponding object array for the replaceable parameters.

It would be a simple matter to replace the "?" characters with a ":x" (or some such) and then produce:

sQuery = "begin execute immediate 'insert into MyTable values (:x,:x,:x)' using 1,2,3; end;"

Then I could batch up each of these statements using an "encapsulating" BEGIN/END block and submit it.

I dunno if this will yield a performance improvement, but that's my next exercise. ;-)

One thing that caught my eye from
http://technet.oracle.com/doc/server.815/a67842/10_dynam.htm#13131:

<Quote>
Caution: You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution. </Quote>

What does this mean, exactly? Will the queries that participate in EXECUTE IMMEDIATE statements *not* be available in the query cache? If so...that kind of defeats the purpose of the bind variable approach I've outlined above. If you have any more clarification, I'd sure welcome it!

Thank you again for your insight!

Regards,

John Peterson

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9f67uv02ob9_at_drn.newsguy.com...
> 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 - 16:12:44 CDT