How to iterate thru a cursor returned from an oracle function

Question

I want to loop through the records returned from a function that returns a SYS_REFCURSOR Is this possible? Thanks in advance for any help.

  FOR R_RECORD IN get_direct_reports(23) LOOP

    IF R_RECORD.EMAIL = 'Hi@.com' THEN
      do a bunch of stuff
    ELSE
      do a bunch of different stuff,etc
    END IF;
  END LOOP;

Example function CREATE OR REPLACE FUNCTION get_direct_reports( in_manager_id IN employees.manager_id%TYPE) RETURN SYS_REFCURSOR AS c_direct_reports SYS_REFCURSOR; BEGIN

OPEN c_direct_reports FOR 
SELECT 
   employee_id, 
   first_name, 
   last_name, 
   email
FROM 
  employees 
WHERE 
  manager_id = in_manager_id 
ORDER BY 
     first_name,   
     last_name;

RETURN c_direct_reports;
END;

example code to call function, which I want to change to loop thru the data returned

DECLARE
c_direct_reports SYS_REFCURSOR;
l_employee_id employees.employee_id%TYPE;
l_first_name  employees.first_name%TYPE;
l_last_name   employees.last_name%TYPE;
l_email       employees.email%TYPE;
BEGIN
-- get the ref cursor from function
c_direct_reports := get_direct_reports(46); 

-- process each employee
LOOP
  FETCH
     c_direct_reports
  INTO
     l_employee_id,
     l_first_name,
     l_last_name,
     l_email;
  EXIT
WHEN c_direct_reports%notfound;
   dbms_output.put_line(l_first_name || ' ' || l_last_name || ' - ' ||    l_email );
END LOOP;
-- close the cursor
CLOSE c_direct_reports;
END;

Popeye Popeye · Accepted Answer · 2020-11-18T07:32:18

Following is a sample code of how you can use the SYS_REFCURSOR from function:

SQL> CREATE OR REPLACE FUNCTION DUMMY_FUNCTION RETURN SYS_REFCURSOR AS
  2  LV_CUR SYS_REFCURSOR;
  3  BEGIN
  4  OPEN LV_CUR FOR SELECT 1 AS COL1, 2 AS COL2 FROM DUAL
  5                  UNION ALL
  6                  SELECT 11 AS COL1, 12 AS COL2 FROM DUAL;
  7  RETURN LV_CUR;
  8  END DUMMY_FUNCTION;
  9  /

Function created.

SQL>
SQL> SET SERVEROUT ON
SQL> DECLARE
  2  LV_CUR SYS_REFCURSOR;
  3  LV_COL1 NUMBER;
  4  LV_COL2 NUMBER;
  5  BEGIN
  6  LV_CUR := DUMMY_FUNCTION;
  7  LOOP
  8  FETCH LV_CUR INTO LV_COL1, LV_COL2;
  9  EXIT WHEN LV_CUR%NOTFOUND;
 10  DBMS_OUTPUT.PUT_LINE('COL1: ' || LV_COL1 || ', COL2: ' || LV_COL2);
 11  END LOOP;
 12  CLOSE LV_CUR;
 13  END;
 14  /
COL1: 1, COL2: 2
COL1: 11, COL2: 12

PL/SQL procedure successfully completed.

SQL>

How to iterate thru a cursor returned from an oracle function

1 Answers