By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,781 Members | 1,470 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,781 IT Pros & Developers. It's quick & easy.

Oracle 9i: Problems with SQL parameters

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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)
Jul 19 '05 #2

P: n/a
"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)...
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.