Sybrand Bakker wrote:
> On Mon, 21 Feb 2005 21:17:20 -0500, cschang <cschang_at_maxinter.net>
> wrote:
>
>
>>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
>
>
> 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
Sorry, it was my typo in the post. The mytable should be the Orders.
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
Received on Tue Feb 22 2005 - 21:57:24 CST
