Different result running sql in procedure and in SQL mode

cschang · *nix forums beginner Joined: 05 Feb 2005 Posts: 20

Sybrand Bakker wrote:

Sybrand Bakker · *nix forums Guru Joined: 03 Apr 2005 Posts: 1766

On Mon, 21 Feb 2005 21:17:20 -0500, cschang <cschang@maxinter.net>
wrote:

cschang · *nix forums beginner Joined: 05 Feb 2005 Posts: 20

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

Google