REF Cursors (Strong vs. Weak)
Oracle Fusion / EBS · Technical · PL/SQL
GeneralHigh confidence
A REF CURSOR is a pointer to a result set that can be passed around, unlike a fixed named cursor.
Grounded in: Curated Oracle knowledge layer — PL/SQL
How it works
A REF CURSOR is a pointer to a cursor's result set that can be passed between procedures, or out to a client (a report, a Java/OIC caller), instead of being tied to one fixed named cursor. A strong REF CURSOR type pins down a specific return structure (a RETURN clause matching a %ROWTYPE or record type), so a mismatched fetch fails at compile time. A weak REF CURSOR — or the built-in SYS_REFCURSOR — has no declared return type, is more flexible, and is what most OUT-parameter result-set patterns use today.
How it works
CREATE OR REPLACE PROCEDURE get_employees_by_dept(
p_dept_id IN employees.department_id%TYPE,
p_result OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_result FOR
SELECT employee_id, last_name, salary FROM employees WHERE department_id = p_dept_id;
END;
/
-- Caller:
DECLARE
v_cursor SYS_REFCURSOR;
v_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
get_employees_by_dept(90, v_cursor);
LOOP
FETCH v_cursor INTO v_name, v_salary;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE v_cursor;
END;
/Related Questions
Have a follow-up, or a different question?
Continue in Ask Oracle AI