My system id 9.2.0.5 on Windows 2000 with sp 4
One of my old table have columns build with char, one is order_id as
char(11) and other one order_lineID as char(6). The table was built in
8.0.5. I recent built a sql like
Select count(*)
From orders
Where order_id = ‘TEST0040800’ and order_lineID = ‘0001’;

When I run this under the SQL> mode, I got count(*) = 1, however when I
put this inside a procedure as
Procedure getCount(v_order_id IN mytable.order_id%TYPE,
V_lineID IN
mytable.order_lineID%TYPE,
V_count OUT number);

BEGIN
Select count(*)
INTO v_count
From orders
Where order_id = v_order_id and order_lineID = V_lineID;

END;

I got v_count = 0 with the same set of input as those of standalone sql.
Unless I included the Trim(order_lineID) = Trim(v_lineID) in where
condition, then I got v_count = 1. Does anyone hear about any bug of
such issue with 9.2? I was definitely sure that the input values were
exact those in SQl mode becasue I wrote out the input.

C Chang

On Mon, 21 Feb 2005 21:17:20 -0500, cschang <cschang (AT) maxinter (DOT) net>
wrote:

I see a mismatch between the parameter definition
(mytable.order_id%type) and the actual statement (orders.order_id)
are you sure the definition of mytable is exactly the same as the
definition of orders?


--
Sybrand Bakker, Senior Oracle DBA

Sybrand Bakker wrote:
Actually I found the cause when I typed this post. Apparently in 9.2 I
can not use the VARCHAR2 to replace the orders.order_id%TYPE in the
definition of the procedure because of the CHAR type of original column.
If I use v_order_id IN VARCHAR2, then the SQL in the procedure won't
find anything. Thanks.

C Chang