Friday, October 7, 2011

Oracle Stored Procedure to return multiple records and call it in SQLPLUS

To return multiple records we need a REF CURSOR type variable to assign each record. The following is a script which return a REF CURSOR  as OUT parameter.

--OracleStroredProc.sql

CREATE OR REPLACE PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR;
END Types;
/

-------

CREATE OR REPLACE PROCEDURE getEmployeeInfo(p_recordset1 OUT  Types.cursor_type) AS

BEGIN
    OPEN p_recordset1 FOR
    SELECT empNumber, empName, phone, address FROM Employees;
   
END;

/

We can run the above script in SQLPLUS as below.

SQL> START OracleStroredProc.sql

So procedure will be created by now. To call the procedure we have to create a variable of type REFCURSOR and pass it as a parameter to procedure call. Then we can
print the variable to see the result.


SQL> var a REFCURSOR;
SQL> call getEmployeeInfo(:a);

Call completed.

SQL> print :a;

1 comment: