Re: Bind variables in anonymous PL/SQL blocks.

A copy of this was sent to "John Peterson" <johnp_at_azstarnet.com> (if that email address didn't require changing) On Thu, 31 May 2001 19:42:49 -0700, you wrote:

>Hello, Thomas!
>
>Thank you again for the information. If I understand you correctly, my
>approach will NOT work because it's not taking advantage of caching? (That
>is, Oracle can't cache the EXECUTE IMMEDIATE in the way that I'm hoping?)
>

You originally said:

>>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;"

the execute immediate would be using SHARED SQL however, the statement as a whole that JAVA would execute would be:

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

end;
                                                                ^^^^^^ constants

in order to insert 4,5,6 you would have to execute:

begin
execute immediate 'insert into MyTable values (:x,:x,:x)' using 4,5,6;

end;
                                                       ^^^^^ different constants

the problem is the anon-PLSQL block submitted by JAVA would not be using bind variables -- that begin/end block submitted by java would trash the shared pool -- java would be submitting totally unique statements -- even though the execute immediate within them would be reusing statements.

>I noticed this blurb 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>
>
>I wasn't sure what was meant by re-prepares the statement. Does this mean
>it won't use the cache?
>
>From what I can tell from your suggestion, I would need to create a series
>of prepared statements where I do "n" repetitive operations upon, and use
>the .addBatch() method to cut down on network traffic. (And I believe it
>supports bind variables.)
>

either addBatch or just use the Oracle "setDefaultExecuteBatch" on a prepared statement.

>I was hoping my technique would cut down on the number of network calls, but
>also offer a non-homogenous statement capability. (That is, not all of my
>"batch" activity needed to be done with the *same* prepared statement.) But
>it sounds as if I won't be able to take advantage of any caching capability
>if I go that route. :-(
>

the two concepts conflict with other. thats the problem with non-homogenous batching. If you have but one statement -- and that context of that statement can change for each batched set of inputs, you'll be parsing/reparsing over and over.

you should use a prepared statement PER unique statement. I tend to wrap mine in a class -- the class prepares the statement ONCE per program execution. I set the default execute batch to whatever and when I commit -- it flushes the batch over to the server for me.

>Is there any other Oracle/JDBC batching facility I could leverage that you
>know of?
>
>Please let me know if I've understood you correctly. And thank you again!
>:-)
>

--
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