RE: OC4J Bind Variables: Positional Notation

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

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

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ray Feighery
Sent: Sunday, July 13, 2008 7:11 PM
To: Oracle-L List
Subject: Re: OC4J Bind Variables: Positional Notation

Hello

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 childOrg.id = :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.

Ray
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 14 2008 - 07:30:34 CDT