Re: Different result running sql in procedure and in SQL mode

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