Oracle 9i: Problems with SQL parameters

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Dmitry Bond.
    #1

    Oracle 9i: Problems with SQL parameters

    Hello All.

    Currently we are porting some software from NSK (HP Hon-Stop) SQL to Oracle
    and I faced with the following problem...
    The SQL statement:

    select * from BOM where ordnr = :1

    where value of parameter ":1" is "ORD195" returns no data (sqlcode 1403).
    But the SQL:

    select * from BOM where ordnr = 'ORD195'

    returns 12 rows of data.
    The field ORDNR is CHAR(16) and that should be so (VARCHAR(n) is not
    acceptable here by requirements!).
    I have tried to bind this parameters value as VARCHAR (not null-terminated)
    and as STRING (null terminated) data types but the result was the same. :-(
    As I got the

    EXEC SQL DESCRIBE BIND VARIABLES FOR SqlDynaStmt INTO SqlDaIn;

    does not provide information about parameters data types and lengthes then I
    can not make any assumption about expected parameters values, datatypes,
    lengthes, etc.

    Later I found that this SQL statement works fine:

    select * from BOM where trim(ordnr) = :1

    But I do not wish to add TRIM() anywhere for CHAR(n) fields!!! :-\

    It is pretty big problem for us because we want to use SQL parameters (like
    it was in NSK SQL).
    I wondering that even examples that delivered with Oracle9i (ansidyn1.pc,
    sample10.pc) does not work correctly with CHAR(n) parameters in this case!

    Could you please share some your experience concerning the case?
    Could you please provide us with some adivices, etc.?


    WBR, Dmitry.

    ps. To repeat this case you can get SAMPLE10.EXE example from standard
    Oracle, create "BOM" table with some CHAR(16) field, fill table with some
    test data, enter that SQL with parameter and with constant instead of
    parameter and compare results - that is exactly this case.


    Tags: None
    • Ben Graham
      #2
      Re: Oracle 9i: Problems with SQL parameters

      "Dmitry Bond." <dima_ben@ukr.n etwrote in message news:<109526541 6.440592@moxa.u nited.net.ua>.. .
      Hello All.
      >
      Currently we are porting some software from NSK (HP Hon-Stop) SQL to Oracle
      and I faced with the following problem...
      The SQL statement:
      >
      select * from BOM where ordnr = :1
      >
      where value of parameter ":1" is "ORD195" returns no data (sqlcode 1403).
      But the SQL:
      >
      select * from BOM where ordnr = 'ORD195'
      >
      returns 12 rows of data.
      The field ORDNR is CHAR(16) and that should be so (VARCHAR(n) is not
      acceptable here by requirements!).
      I have tried to bind this parameters value as VARCHAR (not null-terminated)
      and as STRING (null terminated) data types but the result was the same. :-(
      As I got the
      >
      EXEC SQL DESCRIBE BIND VARIABLES FOR SqlDynaStmt INTO SqlDaIn;
      >
      does not provide information about parameters data types and lengthes then I
      can not make any assumption about expected parameters values, datatypes,
      lengthes, etc.
      >
      Later I found that this SQL statement works fine:
      >
      select * from BOM where trim(ordnr) = :1
      >
      But I do not wish to add TRIM() anywhere for CHAR(n) fields!!! :-\
      >
      It is pretty big problem for us because we want to use SQL parameters (like
      it was in NSK SQL).
      I wondering that even examples that delivered with Oracle9i (ansidyn1.pc,
      sample10.pc) does not work correctly with CHAR(n) parameters in this case!
      >
      Could you please share some your experience concerning the case?
      Could you please provide us with some adivices, etc.?
      >
      >
      WBR, Dmitry.
      >
      ps. To repeat this case you can get SAMPLE10.EXE example from standard
      Oracle, create "BOM" table with some CHAR(16) field, fill table with some
      test data, enter that SQL with parameter and with constant instead of
      parameter and compare results - that is exactly this case.
      String data stored in CHAR columns is padded to be the length of the
      column. E.g 'ORD195 ' (of length 16) is actually stored in
      the ordnr column.

      String literals are of datatype CHAR. When comparing CHARs the shorter
      string is right padded to the length of the longer one. Thus select *
      from BOM where ordnr = 'ORD195' and select * from BOM where ordnr =
      'ORD195 ' are equivalent.

      Can you bind using a CHAR variable? If not, and you don't want to use
      TRIM, then you could consider using RPAD to pad the variable value to
      the length of the column, e.g.

      select * from BOM where ordnr = rpad(:1,16)

        Comment

        • Dmitry Bond.
          #3
          Re: Oracle 9i: Problems with SQL parameters

          "Ben Graham" <bengraham@xsma il.comwrote in message
          news:f858471c.0 409160050.38e3d 80b@posting.goo gle.com...
          "Dmitry Bond." <dima_ben@ukr.n etwrote in message
          news:<109526541 6.440592@moxa.u nited.net.ua>.. .
          [...]
          String data stored in CHAR columns is padded to be the length of the
          column. E.g 'ORD195 ' (of length 16) is actually stored in
          the ordnr column.
          [...]

          Thank you very much!
          It is working when I set input parameter type to CHAR (96)...


            Comment

            Previous template Next
            Working...