Oracle 9i: Problems with SQL parameters

[Dmitry Bond.]

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.

[Ben Graham]

"Dmitry Bond." <di******@ukr.net> wrote in message news:<10***************@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)

[Dmitry Bond.]

"Ben Graham" <be*******@xsmail.com> wrote in message
news:f8**************************@posting.google.c om...

"Dmitry Bond." <di******@ukr.net> wrote in message news:<10***************@moxa.united.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)...