How to Return ResultSet from Stored Procedure in Oracle?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

In this tutorial, I am giving an example to return resultset from a stored procedure in Oracle using sys_refcursor.

Follow these steps to return resultset from a stored procedure in Oracle.

1. Create a Stored Procedure in Oracle

The following stored procedure (p_dept) will return department names in sys_refcursor (p_result) from the department table where the department number is less than or equal to the parameter value.

CREATE OR REPLACE PROCEDURE p_dept (p_deptno IN dept.deptno%TYPE,
p_result OUT SYS_REFCURSOR)
IS
BEGIN
OPEN p_result FOR
SELECT dname
FROM dept
WHERE deptno <= p_deptno;
END;
/

2. Get The Returned ResultSet and Process

The following PL/SQL block will get the resultset from the stored procedure (p_dept) and will print the department names.

SET SERVEROUTPUT ON;
DECLARE
v_result SYS_REFCURSOR;
v_dname VARCHAR2 (100);
BEGIN

/* call the procedure by passing department numbner and sys_refcursor for resultset */
p_dept (30, v_result);

LOOP
FETCH v_result INTO v_dname;

EXIT WHEN v_result%NOTFOUND;
DBMS_OUTPUT.put_line (v_dname);
END LOOP;
END;
/

Output:

ACCOUNTING
RESEARCH
SALES
PL/SQL procedure successfully completed.

See also: