Here's the PL/SQL:
PROCEDURE load_party_by_partyname (
p_party_name IN party.party_name%TYPE,
dataset OUT daltypes_pkg.ref_cursor
) AS
BEGIN
DECLARE
OUTREF sys_refcursor;
IN_PARTY_NAME VARCHAR2(60);
IN_SCORE NUMBER;
IS_ACTIVE char(1);
max_rows_returned NUMBER DEFAULT NULL;
BEGIN
IN_PARTY_NAME := 'Eric';
IN_SCORE := 80;
IS_ACTIVE := 'Y'; -- Y will only return Active, any other character will return Active and Deleted parties
MAX_ROWS_RETURNED := 100; -- -1 will return all values
--OUTREF := NULL;
PARTY_SEARCH_PKG.SEARCH_BY_PARTY_NAME ( IN_PARTY_NAME, IN_SCORE, IS_ACTIVE, MAX_ROWS_RETURNED, OUTREF );
LOOP
FETCH OUTREF INTO CURSOR crz;
EXIT WHEN OUTREF%NOTFOUND;
FOR rec IN OUTREF LOOP
-- Get everything from tempset and add what's needed to it:
SELECT rec.*, ru.registered_user_name
INTO dataset
FROM party_relationship pr, registered_user ru
WHERE pr.party_id = rec.party_id
AND ru.party_relationship_id = pr.party_relationship_id;
dbms_output.put_line( TO_CHAR( SQL%ROW));
END LOOP;
END;
END load_party_by_partyname;
Here's the problem, I can't figure out how to loop through the returned cursor from the first stored procedure that my stored procedure calls... Any help is much appreciated.