Re: puzzling thing with oracle...

I do not have time to study this in depth, but you might want to try the following: under item 2 change you logic to be like this: change " lfkey LIKE key " to " lfkey like rtrim(lfkey)||'%' " The double quotes are just to highlight the target code.

Your problem almost has to be due to character vs varchar comparision rules, i.e, "TOMbb" != "TOM" where b represents a space and the first column is char while the second is varchar or it could be the position of the '%' in your data value.

It the above does not pan out then it may be worth checking your table data to be sure no trailing spaces or newlines characters where stored as part of the value. You can use the dump function to check this.

In article <8agpn5$cg7$1_at_nnrp1.deja.com>,   skudsi_at_my-deja.com wrote:
> HI,
> Here is the code is below, I call get_zbase with parameter one as a
> null char[5] and parameter2 with a char[64] both are null terminated.
> note that the data length is always 2 and 36 in the case of calling
> get_zbase. I have bigger arrays for calling other procedures only.
>
> prefix type is CHAR(2)
> and Key type is CHAR(36) in the code below.
>
> in the case of LIKE ( option = 2 ) I append a '%' to parameter2 before
> calling.
>
> CREATE OR REPLACE PROCEDURE get_zbase(prefix IN OUT zbase.lfpfx%TYPE,
> key IN OUT zbase.lfkey%TYPE, choice IN NUMBER, gdata OUT zbase.cdata%
> TYPE, origkey zbase.lfkey%TYPE DEFAULT NULL)
> AS
> mkey CHAR(36) DEFAULT NULL;
> BEGIN
> IF choice = 1 THEN
> SELECT MIN(lfkey) INTO mkey FROM zbase
> WHERE lfpfx = prefix and lfkey = key;
>
> ELSIF choice = 2 THEN
>
> IF origkey IS NULL THEN
> SELECT MIN(lfkey) INTO mkey FROM zbase
> WHERE lfpfx = prefix and lfkey LIKE key;
> ELSE
> SELECT MIN(lfkey) INTO mkey FROM zbase
> WHERE lfpfx = prefix and lfkey LIKE origkey ESCAPE '/';
>
> END IF;
>
> ELSIF choice = 3 THEN
> SELECT MIN(lfkey) INTO mkey FROM zbase
> WHERE lfpfx = prefix and lfkey > key;
>
> ELSIF choice = 4 THEN
>
> IF origkey IS NULL THEN
> SELECT MIN(lfkey) INTO mkey FROM zbase
> WHERE lfpfx = prefix and lfkey >= key;
>
> ELSE
> SELECT MIN(lfkey) INTO mkey FROM zbase
> WHERE lfpfx = prefix and lfkey > key;
>
> END IF;
>
> END IF;
>
> IF mkey IS NOT NULL THEN
> SELECT * INTO prefix, key, gdata from zbase WHERE lfpfx = prefix and
> lfkey = mkey;
>
> END IF;
> END;
>

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.