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 |
Typed cursors give compile-time type checking but are less flexible than SYS_REFCURSOR.
