RE: OC4J Bind Variables: Positional Notation

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <rjfeighery@xxxxxxxxx>, "'Oracle-L List'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 14 Jul 2008 08:30:34 -0400

If I understand you correctly, your tracing on the server side appears to
have confirmed there is nothing broken in the general driver conversion

Now that you have identified a particular problem statement, I would look
for where that is generated on the application side and check that it is not
being submitted through the drivers with some encapsulating characters or
quotations the differ from the the working ones. What you're looking for is
something that is peeled off at some point in the driver stack (or else we'd
see them in the traced output) that somehow interferes with the positional

For example, I'm not clear on why you've got the extra parentheses, though
they appear legal, it might indicate a different coding style section.
That's all speculation, useful only to help choose the next test to make.

Instrumenting the application so you can log the client side string before
it is submitted and pair up the inbound versus server logged sql strings is
useful, and is plausible to implement if you use a wrapper in place of the
direct calls. In this case, since you know a particular "broken" statement,
it may be easier to find and log that one particular client side string.

Good luck.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ray Feighery
Sent: Sunday, July 13, 2008 7:11 PM
To: Oracle-L List
Subject: Re: OC4J Bind Variables: Positional Notation


Thanks to Chris and Cary for the posts.

I've traced the session. The application submits multiple SQL queries.
The first few have the desired format:

  select t.distance,
  from tree_xref t
   where = :1

Then it submits a query like this :

select id,
from ila_custom_string
WHERE (site_id = ? and locale = ?)

And errors with ORA-0911

There's no conversion that I can see.

If the JDBC driver handles replacement of bind variables, then all
bind variables submitted to the database should be in
Oracle-understandable format (i.e. :1).  This should be handled by
Oracle Application Server and the JDBC driver.
As an aside, I think in JDeveloper there is actually a setting to
change how bind variables are submitted.

I'm confused as to why it changes in this application. Can it be set
in the code?

Any information is appreciated. I've raised a Metalink SR, but given
the quality of response, my hopes aren't high.



Other related posts: