RE: OC4J Bind Variables: Positional Notation

From: Mark W. Farnham <>
Date: Mon, 14 Jul 2008 08:30:34 -0400
Message-Id: <>

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: [] 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.



The information contained in this message and any attachment may be proprietary, confidential, and privileged or subject to the work product doctrine and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify me immediately by replying to this message and deleting it and all copies and backups thereof. Thank you.

-- Received on Mon Jul 14 2008 - 07:30:34 CDT

Original text of this message