Oracle – Returning a recordset from a stored procedure

In Oracle, returning a record set from a stored procedure to the calling client application is not as straight forward as simply doing a select from a table in the stored procedure code.  One needs to make use of a reference cursor.  In this blog post, we will cover how one can achieve this in Oracle.  First, let us create a table and populate it with small set of data.

CREATE TABLE Invoice
(
INVOICE_NUMBER      NUMBER(9) NOT NULL,
INVOICE_DATE        DATE NOT NULL,
CLIENT_ID           NUMBER(9)     NOT NULL,
INVOICE_AMT         NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
— TABLESPACE Clause
/

CREATE SEQUENCE INVOICE_SEQ
START WITH 1
CACHE 100
/

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,101,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,102,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,103,1100.00);

INSERT INTO INVOICE(Invoice_Number, Invoice_date, client_ID, Invoice_Amt)
VALUES(INVOICE_SEQ.NEXTVAL, sysdate,104,1100.00);

Oracle uses cursor variables to pass query result sets between PL/SQL sub programs and to the client application. A cursor variable has data type REF CURSOR and that is what is normally known as a ref cursor (we will cover more on the ref cursors in one of our future posts). Following is the code snippet to return a result set from the stored procedure.

To create a cursor variable first we need to define a REF CURSOR type and then declare the cursor variable of that type.  To avoid declaring the same REF CURSOR type in each program, we will create one global type in a package specification and declare cursor of that type in our procedure.

Run following scripts to create package and procedure from SQL*Plus prompt. Testing script is to test the execution of the procedure.

/* Create package */
CREATE OR REPLACE PACKAGE types
AS
type cursorType is ref cursor;
END;
/

— Here we have declared cursor variable of type cursorType as an output variable.
CREATE OR REPLACE PROCEDURE DEC_RTN_RECORDSET
(
p_InvoiceDate      IN   DATE,
p_ResultSet        OUT  TYPES.cursorType
)
AS
BEGIN
OPEN p_ResultSet FOR
SELECT Invoice_Number, Invoice_Date, Client_ID, Invoice_Amt
FROM Invoice
WHERE Invoice_date <= p_InvoiceDate
ORDER BY Invoice_number;

END DEC_RTN_RECORDSET;
/

— Testing
VARIABLE resultSet  REFCURSOR
EXEC DEC_RTN_RECORDSET(sysdate, :resultSet);
PRINT :resultSet

Following is the output. It is formatted for more readability.

Invoice_Number       Invoice_Date  Client_ID     Invoice_Amt
————–       ————  ———     ———–
1       25-JAN-07           101            1100
2       25-JAN-07           102            1100
3       25-JAN-07           103            1100
4       25-JAN-07           104            1100