Handling Recordsets in Oracle Procedures!!

Handling Recordsets in Oracle Procedures!!

It is not a big deal to develop a stored procedure for performing select on a table. However it is really tricky to return a record set from a stored procedure as we have to use reference cursor in order to accomplish this task. In this article we will discuss how a resultset can be returned in an Oracle procedure and how we can use it in our calling program.
Create Table:
First of all we will create a table.
CREATE TABLE MYTABLE ( MYPK NUMBER(9) NOT NULL, MYDATE DATE NOT NULL, MYCLIENT NUMBER(9) NOT NULL, MYAMOUNT NUMBER(9,2) DEFAULT 0 NOT NULL, PAYMENT_STATUS NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid CONSTRAINT PK_MYTABLE PRIMARY KEY(MYPK) ) /

Create Sequence:
Now we will create a sequence for it.
CREATE SEQUENCE MYSEQ START WITH 1 CACHE 200 /
Insert Data:
Now we will insert some data in our table.
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,201,1200.00);
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,202,1300.00);
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,203,1400.00);
INSERT INTO MYTABLE(MYPK, MYDATE, MYCLIENT, MYAMOUNT) VALUES(MYSEQ.NEXTVAL, sysdate,204,1500.00);
Now if we want to pass resultset between PL/SQL sub programs and client application then we must define a reference cursor. Declaring same ref cursor in every program will add overhead and therefore we will be creating a single global type in a package specification. Once the package is defined then we just have to declare the cursor of that type in our procedures.
Create Package:
Now we will create a package that defines a cursor variable of type MYCURSOR. Remember MYCURSOR will be acting as an output variable.

CREATE OR REPLACE PACKAGE MYCURSOR_TYPE AS TYPE MYCURSOR IS REF CURSOR; END; /
Create Procedure:
Now we will create a procedure.


CREATE OR REPLACE PROCEDURE RETURN_MY_RECORDSET ( MYTABLEDATE IN DATE, MYRESULTSET OUT MYCURSOR_TYPE.MYCURSOR ) AS BEGIN OPEN MYRESULTSET FOR SELECT MYPK, MYDATE, MYCLIENT, MYAMOUNT FROM MYTABLE WHERE MYDATE <= MYTABLEDATE ORDER BY MYPK;END RETURN_MY_RECORDSET;

Return Resultset:
Now we will call the above procedure in order to return the resultset from our Oracle procedure.
VARIABLE MyVarResultSet REFCURSOR EXEC RETURN_MY_RECORDSET(sysdate, :MyVarResultSet); PRINT :MyVarResultSet
The output will be as follows.
MYPK MYDATE MYCLIENT MYAMOUNT ——— —————— ————— —————– 1 17-FEB-08 201 1200 2 17-FEB-08 202 1300 3 17-FEB-08 203 1400 4 17-FEB-08 204 1500
Create Temporary Table:
Now we will create a temporary table that will be holding the data returned from our procedure.
CREATE GLOBAL TEMPORARY TABLE MYTEMPTABLE ( MYPK NUMBER(9), MYDATE DATE, MYAMOUNT NUMBER(9,2), MYCLIENT NUMBER(9) ) ON COMMIT PRESERVE ROWS /
Store data in Temporary Table:
Now we will store data into our temporary table.
DECLARE c MYCURSOR_TYPE.MYCURSOR; MYTEMPTABLE_PK NUMBER(9); MYTEMPTABLE_DATE DATE; MYTEMPTABLE_AMOUNT NUMBER(9,2); MYTEMPTABLE_CLIENT NUMBER(9);

BEGIN RETURN_MY_RECORDSET(sysdate,c); LOOP FETCH C INTO MYTEMPTABLE_PK,MYTEMPTABLE_DATE,MYTEMPTABLE_AMOUNT,MYTEMPTABLE_CLIENT; exit when c%notfound; INSERT INTO MYTEMPTABLE VALUES(MYTEMPTABLE_PK,MYTEMPTABLE_DATE,MYTEMPTABLE_AMOUNT,MYTEMPTABLE_CLIENT); END LOOP; CLOSE c; COMMIT; END; /
Retrieve Data:


Now just we have to do is to query our temporary table to get the output from the temporary table. Hence you are able to easily use the records returned by the Oracle procedure in your calling program. You can process individual records or put it in the temporary table in your calling program. One thing you should keep in mind is that your calling program should close the cursor otherwise it will result in the problem of cursor leaks.
SQL> SELECT * FROM MYTEMPTABLE;
MYPK MYDATE MYCLIENT MYAMOUNT ——— —————— ————— —————– 1 17-FEB-08 201 1200 2 17-FEB-08 202 1300 3 17-FEB-08 203 1400 4 17-FEB-08 204 1500 Read Again!!