How to Return a Result Set from an Oracle Procedure Using SYS_REFCURSOR

Learn to pass `varchar` parameters in an Oracle PL/SQL procedure and return a dynamic result set using `sys_refcursor`. A practical code example.

-- Declare a typed cursor type
CREATE OR REPLACE PACKAGE emp_pkg AS
    TYPE emp_cur_t IS REF CURSOR RETURN employees%ROWTYPE;

    PROCEDURE get_employees (
        p_dept_id IN  NUMBER,
        p_cursor  OUT emp_cur_t
    );
END emp_pkg;
/

DECLARE
    l_cursor   SYS_REFCURSOR;
    l_emp_id   employees.employee_id%TYPE;
    l_fname    employees.first_name%TYPE;
    l_lname    employees.last_name%TYPE;
    l_salary   employees.salary%TYPE;
BEGIN
    get_employees(p_dept_id => 10, p_cursor => l_cursor);

    LOOP
        FETCH l_cursor INTO l_emp_id, l_fname, l_lname, l_salary;
        EXIT WHEN l_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(l_fname || ' ' || l_lname || ': ' || l_salary);
    END LOOP;

    CLOSE l_cursor;   -- always close to release resources
END;
/

Key Rules

Rule	Detail
Always `CLOSE` the cursor	Unclosed ref cursors hold server-side resources until the session ends
Callers own the cursor	Once passed back, the procedure is done — the caller fetches and closes
Use bind variables	Avoid SQL injection and improve plan reuse when building dynamic SQL
`SYS_REFCURSOR` vs typed `REF CURSOR`	`SYS_REFCURSOR` (weak) accepts any query; a typed ref cursor is tied to a specific row type and gives compile-time checking
Exception handling	If an exception is raised after `OPEN`, the cursor may never be returned — close it in the `EXCEPTION` block if opened locally