Oracle Database Discussions

Hi,

I have recursive procedure which should return an output as cursor. So in the procedure it is as an IN OUT paramter.

Case 1:
In a loop for each record I am checking some conditions and store the result in the cursor as an array of data. But at the end I can see only the last record as result in the cursor. Can some one explain why?.

Case 2: (Not yet happened.. But like to know)
Same procedure calls itself in the loop if the condition not satisifed. So I am passing the cursor as again parameter to the recursive call. Will it retain the value at end of calls?.

I have listed the code here. I will call the EXMET2 from SQL PLUS for this code.

CREATE OR REPLACE PACKAGE ItemPackage AS

TYPE outputCursor IS REF CURSOR ;

PROCEDURE PROC_JDE_ITMMET (C1 IN OUT ItemPackage.outputCursor, ITMNUM IN NUMBER, ITMSTKT IN VARCHAR, ITMSRP6 IN VARCHAR, ITMMCU IN VARCHAR);

PROCEDURE PROC_JDE_EXMET2 (ITMNUM IN NUMBER, ITMSTKT IN VARCHAR, ITMSRP6 IN VARCHAR, ITMMCU IN VARCHAR);

END ItemPackage;
/

CREATE OR REPLACE PACKAGE BODY ItemPackage AS
PROCEDURE PROC_JDE_ITMMET (C1 IN OUT ItemPackage.outputCursor, ITMNUM IN NUMBER, ITMSTKT IN VARCHAR, ITMSRP6 IN VARCHAR, ITMMCU IN VARCHAR) IS
BEGIN
DECLARE
STKT TESTDTA.F4102.IBSTKT%TYPE;
SRP6 TESTDTA.F4102.IBSRP6%TYPE;
MCU TESTDTA.F4102.IBMCU%TYPE;
CURSOR F3002_C1 IS SELECT IXITM, IXLITM, IXMMCU, IXQNTY, IXUM FROM TESTDTA.F3002 WHERE IXKIT = ITMNUM AND IXTBM ='TM';
CURSOR F3002_C2 IS SELECT IXITM, IXLITM, IXMMCU, IXQNTY, IXUM FROM TESTDTA.F3002 WHERE IXKIT = ITMNUM AND IXTBM ='M';
BEGIN
IF (ITMSTKT = 'K' OR ITMSTKT = 'M' AND ITMSRP6 = 'METAL') THEN
FOR F3002_C1_rec IN F3002_C1 LOOP
SELECT IBSTKT, IBSRP6, IBMCU INTO STKT, SRP6, MCU FROM TESTDTA.F4102
WHERE IBITM = F3002_C1_rec.IXITM and IBMCU = F3002_C1_rec.IXMMCU;
IF (STKT = 'P' AND SRP6 = 'METAL') THEN
OPEN C1 FOR SELECT
F3002_C1_rec.IXLITM, F3002_C1_rec.IXMMCU, F3002_C1_rec.IXQNTY, F3002_C1_rec.IXUM
FROM DUAL;
ELSE
ItemPackage.PROC_JDE_ITMMET(C1, F3002_C1_rec.IXITM, STKT, SRP6, F3002_C1_rec.IXMMCU);
END IF;
END LOOP;
FOR F3002_C2_rec IN F3002_C2 LOOP
SELECT IBSTKT, IBSRP6, IBMCU INTO STKT, SRP6, MCU FROM TESTDTA.F4102
WHERE IBITM = F3002_C2_rec.IXITM and IBMCU = F3002_C2_rec.IXMMCU;
IF (STKT = 'P' AND SRP6 = 'METAL') THEN
OPEN C1 FOR SELECT
F3002_C2_rec.IXLITM, F3002_C2_rec.IXMMCU, F3002_C2_rec.IXQNTY, F3002_C2_rec.IXUM
FROM DUAL;
ELSE
ItemPackage.PROC_JDE_ITMMET(C1, F3002_C2_rec.IXITM, STKT, SRP6, F3002_C2_rec.IXMMCU);
END IF;
END LOOP;
END IF;
END;
END;

PROCEDURE PROC_JDE_EXMET2 (ITMNUM IN NUMBER, ITMSTKT IN VARCHAR, ITMSRP6 IN VARCHAR, ITMMCU IN VARCHAR) IS
BEGIN
DECLARE
IXLITM TESTDTA.F3002.IXLITM%TYPE;
IXMMCU TESTDTA.F3002.IXMMCU%TYPE;
IXQNTY TESTDTA.F3002.IXQNTY%TYPE;
IXUM TESTDTA.F3002.IXUM%TYPE;
C1 ItemPackage.outputCursor;
BEGIN
-- OPEN C1 FOR SELECT ITMNUM, ITMSTKT, ITMSRP6, ITMMCU FROM DUAL
ItemPackage.PROC_JDE_ITMMET(C1, 5000106, 'M', 'METAL', '006999');
LOOP
FETCH C1 INTO IXLITM, IXMMCU, IXQNTY, IXUM;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(IXLITM || IXMMCU || IXQNTY || IXUM);
END LOOP;
END;
END;
END ItemPackage;