FAQ
Hello All

Red Hat Linux 4.0
Oracle Database 10.1.0.5
Oracle Application Server 10.1.2.0.2

An OC4J application that had been running on Oracle Application Server
9.0.4.3 has recently been upgraded. Since then queries have been
failing as they are being submitted like this:

SELECT *

from table1
WHERE (col1 = ? and col2 = ?);

instead of

SELECT *

from table1
WHERE (col1 = :b1 and col2 = :b2);

This is more like an ODBC or Perl query using positional placeholders.
I had thought that the jdbc driver handled this and converted the
former to the latter.

Has anyone seen this before or know what setting controls this? Google
and Metalink are not showing anything similar.

Ray

Search Discussions

  • Cary Millsap at Jul 11, 2008 at 2:37 pm
    Ray,

    I *think* that if you execute an extended SQL trace with
    dbms_monitor.session_trace_enable(null,null,true,true) upon the simple test
    case you show here, you'll see evidence of the conversion you're wondering
    about in your trace file.

    Cary
    On Thu, Jul 10, 2008 at 8:47 PM, Ray Feighery wrote:

    Hello All

    Red Hat Linux 4.0
    Oracle Database 10.1.0.5
    Oracle Application Server 10.1.2.0.2

    An OC4J application that had been running on Oracle Application Server
    9.0.4.3 has recently been upgraded. Since then queries have been
    failing as they are being submitted like this:

    SELECT *
    from table1
    WHERE (col1 = ? and col2 = ?);

    instead of

    SELECT *
    from table1
    WHERE (col1 = :b1 and col2 = :b2);

    This is more like an ODBC or Perl query using positional placeholders.
    I had thought that the jdbc driver handled this and converted the
    former to the latter.

    Has anyone seen this before or know what setting controls this? Google
    and Metalink are not showing anything similar.

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Christian Antognini at Jul 12, 2008 at 9:28 am
    Hi Ray
    This is more like an ODBC or Perl query using positional placeholders.
    I had thought that the jdbc driver handled this and converted the
    former to the latter.
    JDBC uses the "?" as placeholder to define a bind variable. Of course
    the JDBC driver is responsible to replace them. In your case the query
    sent to the database should be the following:

    SELECT *

    from table1
    WHERE (col1 = :1 and col2 = :2);

    As pointed out by Cary, if you want to see the query you should enable
    SQL trace.

    HTH

    Chris Antognini

    Troubleshooting Oracle Performance, Apress 2008
    (http://top.antognini.ch)
  • Ray Feighery at Jul 13, 2008 at 11:11 pm
    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
  • Mark W. Farnham at Jul 14, 2008 at 12:30 pm
    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
    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
  • Nigel Thomas at Jul 14, 2008 at 1:31 pm
    Another check to make is in the java surrounding the execution of this
    statement:

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

    make sure that the developer has actually remembered to bind the values for
    site_id and locale

    Regards Nigel

    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.
  • Nigel Thomas at Jul 14, 2008 at 1:31 pm
    Another check to make is in the java surrounding the execution of this
    statement:

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

    make sure that the developer has actually remembered to bind the values for
    site_id and locale

    Regards Nigel
  • Mark W. Farnham at Jul 14, 2008 at 12:30 pm
    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
    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

    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Christian Antognini at Jul 14, 2008 at 1:47 pm
    Hi Ray
    As an aside, I think in JDeveloper there is actually a
    setting to change how bind variables are submitted.
    I (almost) never used JDeveloper. But, honestly, I see no reason for
    doing that for plain JDBC code. On the other side, when using a
    persistence framework, influencing the use of bind variables can be
    useful.
    I'm confused as to why it changes in this application.
    Can it be set in the code?
    From the JDBC API point-of-view I see only one possibility. The SQL
    statement is executed through the class Statement instead of
    PreparedStatement. If you have access to the code, I would have a look
    to the snippet that executes one of the failing SQL statements.

    HTH

    Chris Antognini

    Troubleshooting Oracle Performance, Apress 2008
    (http://top.antognini.ch)

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

    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Christian Antognini at Jul 14, 2008 at 1:47 pm
    Hi Ray
    As an aside, I think in JDeveloper there is actually a
    setting to change how bind variables are submitted.
    I (almost) never used JDeveloper. But, honestly, I see no reason for
    doing that for plain JDBC code. On the other side, when using a
    persistence framework, influencing the use of bind variables can be
    useful.
    I'm confused as to why it changes in this application.
    Can it be set in the code?
    From the JDBC API point-of-view I see only one possibility. The SQL
    statement is executed through the class Statement instead of
    PreparedStatement. If you have access to the code, I would have a look
    to the snippet that executes one of the failing SQL statements.

    HTH

    Chris Antognini

    Troubleshooting Oracle Performance, Apress 2008
    (http://top.antognini.ch)
  • Ray Feighery at Jul 15, 2008 at 5:19 am
    Thanks for all the replies.

    The SQL statements are stored in XML files. The XML files have a
    parameter called BindingStyle which is set to either JDBC or Oracle.
    When it is set the JDBC the variables are denoted by a '?'. When it is
    set to Oracle the variables are denoted by ':1' (or :2,:3).

    Changing them does not seem to make any difference.

    There is a bug 5510048 filed on Metalink against JDeveloper which
    seems similar; using different BindingStyles results in an error.

    I'll update if I find a solution.

    Ray
  • Jaromir nemec at Jul 16, 2008 at 6:07 am
    Hi Ray,
    Thanks for all the replies.

    The SQL statements are stored in XML files. The XML files have a
    parameter called BindingStyle which is set to either JDBC or Oracle.
    When it is set the JDBC the variables are denoted by a '?'. When it is
    set to Oracle the variables are denoted by ':1' (or :2,:3).

    Changing them does not seem to make any difference.
    There is a note in Expert Oracle JDBC Programming p. 156 that "previous
    versions of JDBC drivers" allowed binding of :1 with setString(1,'xx'). This
    would explain the incompatible behaviour on upgrade.

    The former driver (if this theory is valid - I didn't tested it) tolerated
    the incompatible setting of your BindingStyle flag, the new driver not. But
    you would spot problems in the BindingStyle (i.e. statement using :1,:2,..
    and BindingStyle = JDBC) on the problematic statement immediately, wouldn't
    you?

    Regards,

    Jaromir
  • Yechiel Adar at Jul 16, 2008 at 11:51 am
    You may want to check the NLS_LANG settings.
    If I am not mistaken, '?' is unknown character in USASCII7, meaning you
    have a value, length 1 character, that does not translate between the
    client and the server.

    Adar Yechiel
    Rechovot, Israel

    Ray Feighery wrote:
    Thanks for all the replies.

    The SQL statements are stored in XML files. The XML files have a
    parameter called BindingStyle which is set to either JDBC or Oracle.
    When it is set the JDBC the variables are denoted by a '?'. When it is
    set to Oracle the variables are denoted by ':1' (or :2,:3).

    Changing them does not seem to make any difference.

    There is a bug 5510048 filed on Metalink against JDeveloper which
    seems similar; using different BindingStyles results in an error.

    I'll update if I find a solution.

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


    --
    http://www.freelists.org/webpage/oracle-l
  • Ray Feighery at Jul 24, 2008 at 6:17 am
    Hello All

    The actual problem turned out to be a mismatch between binding styles.
    One of the processes reads the XML for the SQL query. The query was
    set to Binding Style Oracle. The process then adds a "WHERE (col1 = ?
    and col2 = ?);" clause to the select statement. As the Binding Style
    had already been set to Oracle the '?' notation causes the error.

    Crucially the process and the XML were set to different binding
    styles.As noted above, the JDBC driver could handle both, but not at
    the same time.

    Solution: Be consistent.

    Thanks for all the replies

    Ray

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 11, '08 at 1:47a
activeJul 24, '08 at 6:17a
posts14
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase