Systems Engineering and RDBMS

Using the recordset returned by the Oracle procedure

Posted by decipherinfosys on August 18, 2007

In one of our previous blog post, we discussed how one can return record set(s) from stored procedures in Oracle. In the same post, we had also shown how we can test it from SQL*Plus by declaring a variable of type refcursor. Today we will show how can we actually use a returned record set in a calling program. It is merely an extension of our test snippet, but we thought that it is worth to give an actual example. We will do this in small PL/SQL block and will get the result into temporary table. Once result is in the temporary table, you can join it with other tables or perform certain operations based on the returned result.

Please create necessary table and procedure as shown in previous blog post. This example is based on the objects created in that post.

Let us first create a temporary table to hold the data returned from the procedure.

SQL> CREATE GLOBAL TEMPORARY TABLE TEMP_INV
(
INVOICE_NUMBER NUMBER(9),
INVOICE_DATE DATE,
INVOICE_AMT NUMBER(9,2),
CLIENT_ID NUMBER(9)
) ON COMMIT PRESERVE ROWS
/

Table created.

Now let us call the procedure from PL/SQL block which stores data into temporary table we created above.

SQL> DECLARE
c types.cursortype;
l_Invoice_no NUMBER(9);
l_Invoice_Dt DATE;
l_Invoice_Amt NUMBER(9,2);
l_Client_ID NUMBER(9);

BEGIN
DEC_RTN_RECORDSET(sysdate,c);
LOOP
FETCH C INTO
l_Invoice_no,l_Invoice_Dt,l_Invoice_Amt,l_client_ID;
exit when c%notfound;
INSERT INTO TEMP_INV
VALUES(l_Invoice_no,l_Invoice_Dt,l_Invoice_Amt,l_client_ID);
END LOOP;
CLOSE c;
COMMIT;
END;
/

Following is the output from the temporary table:

SQL> SELECT * FROM TEMP_INV;

INVOICE_NUMBER INVOICE_D INVOICE_AMT CLIENT_ID
————– ——— ———– ———-
1 17-AUG-07 101 1100
2 17-AUG-07 102 1100
3 17-AUG-07 103 1100
4 17-AUG-07 104 1100

This was an example of how to use records returned by a stored procedure in a calling program. We are using refcursor which was opened during the execution of the procedure. So, we don’t need to re-open it in the calling program. In a calling program, we can just start fetching data from it until we exhaust the data. We can process individual record or we can put it in the temporary table and then we can use it for bulk operation later on. One thing to remember though is that the calling program is responsible for closing the cursor else you will have cursor leaks.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: