Oracle 9i: Problems with SQL parameters

From: Dmitry Bond. <dima_ben_at_ukr.net>
Date: Wed, 15 Sep 2004 19:23:36 +0300
Message-ID: <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. Received on Wed Sep 15 2004 - 18:23:36 CEST

Original text of this message