Let's create two test procedures:
CREATE OR REPLACE PROCEDURE Aaaa_Test1(
pDog SYS_REFCURSOR
) IS
TYPE tDogRec is record (objid varchar2(7), lim number, debt number);
TYPE tDog IS TABLE OF tDogRec;
vDog tDog;
BEGIN
IF pDog%ISOPEN THEN
FETCH pDog BULK COLLECT INTO vDog;
IF vDog.count >= 1 THEN
FOR i IN vDog.First..vDog.Last LOOP
Dbms_Output.Put_Line('Aaaa_Test1 = '||vDog(i).Objid);
END LOOP;
END IF;
END IF;
END; -- Aaaa_Test1 Procedure
/
CREATE OR REPLACE PROCEDURE Aaaa_Test2(
pDog SYS_REFCURSOR
) IS
TYPE tDogRec is record (objid varchar2(7), lim number, debt number);
TYPE tDog IS TABLE OF tDogRec;
vDog tDog;
BEGIN
IF pDog%ISOPEN THEN
FETCH pDog BULK COLLECT INTO vDog;
IF vDog.count >= 1 THEN
FOR i IN vDog.First..vDog.Last LOOP
Dbms_Output.Put_Line('Aaaa_Test2 = '||vDog(i).Objid);
END LOOP;
END IF;
END IF;
END; -- Aaaa_Test2 Procedure
Then let's try to open cursor and pass it to these procedures in order:
DECLARE
Vcdogcur SYS_REFCURSOR;
BEGIN
OPEN Vcdogcur FOR
select '6518535' objid, 10000 lim,0 debt
from dual
union all
select '6518536', 0,500
from dual
union all
select '5656058', 0,899
from dual
union all
select '2180965', 5000,0
from dual
union all
select '2462902', 0,100
from dual;
Aaaa_Test1(Vcdogcur);
Aaaa_Test2(Vcdogcur);
CLOSE Vcdogcur;
END;
As you can see, I can't use already fetched cursor in second procedure, because ORACLE cursors are forward-and-read-only. What ways can help to solve this task?
I can't simply bring these procedures into one. Need to keep their logic separate from each other.
You need to open the cursor twice. Using a string variable to hold the query will prevent you from writing the query twice.
DECLARE
Vcdogcur SYS_REFCURSOR;
dyn_query varchar2(500);
BEGIN
dyn_query := 'select ''6518535'' objid, 10000 lim,0 debt
from dual
union all
select ''6518536'', 0,500
from dual
union all
select ''5656058'', 0,899
from dual
union all
select ''2180965'', 5000,0
from dual
union all
select ''2462902'', 0,100
from dual' ;
open Vcdogcur for dyn_query ;
Aaaa_Test1(Vcdogcur);
CLOSE Vcdogcur;
open Vcdogcur for dyn_query ;
Aaaa_Test2(Vcdogcur);
close Vcdogcur;
END;
/