Re: Oracle 9i: Problems with SQL parameters

"Dmitry Bond." <dima_ben_at_ukr.net> wrote in message news:<1095265416.440592_at_moxa.united.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) Received on Thu Sep 16 2004 - 10:50:55 CEST