Fetch and bulk collect from a REF CURSOR returned by a procedure
- by Rachcha
I have a procedure that has a SYS_REFCURSOR as an OUT parameter. The signature is, for example, as follows:
PROCEDURE myProc(p_someID IN INTEGER, p_cursor OUT SYS_REFCURSOR);
I call this procedure from a function, where I have to copy a column named clientID from the p_cursor to a scalar nested table.
I am doing as follows:
CREATE OR REPLACE FUNCTION myFunction
RETURN sys_refcursor
IS
someID INTEGER := 1234;
myCursor SYS_REFCURSOR;
TYPE t_clientID_nt IS TABLE OF NUMBER(16,0);
clientID_nt t_clientID_nt;
otherID SYS_REFCURSOR;
BEGIN
myProc (someID, myCursor);
FOR i IN myCursor
LOOP
clientID_nt.EXTEND;
clientID_nt (clientID_nt.COUNT) := i.clientID;
END LOOP;
-- Other code that opens the cursor otherID
-- based on the IDs in clientID_nt
...
...
RETURN otherID;
END;
/
When I try to compile this function, the error I get is:
PLS-00221: 'CLIENTID_NT' is not a procedure or is undefined
and it is at line 11 of the code.
Any help on how to fetch and bulk collect from such a cursor is greatly appreciated.
