Re: puzzling thing with oracle...

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; Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Mar 12 2000 - 00:00:00 CST