DBA Tips: Handling Recordsets in Oracle Procedures!!

Author: J. K. Millar

Page: 1 2

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!!

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle-SQL Guide: Look out for Fragmented Indexes
   Great Tips for Growing Listener Log Files!!
   Great Tips on Optimizing Oracle Network Configuration!!
   Effective Tips for Fixing Online Redo Log Corruption!!