DBA Tips: Handling Recordsets in Oracle Procedures!!

Author: J. K. Millar

Page: 1 2

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;
/ Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   SQL - Best Practices to improve Performance
   Troubleshooting Oracle Performance Problems!!
   Efficient Tips for Renaming Oracle Instance!!
   Inside Automating Histogram Sampling!!