Home » SQL & PL/SQL » SQL & PL/SQL » How to call a procedure which will return a refcursor from another procedure?
icon1.gif  How to call a procedure which will return a refcursor from another procedure? [message #185474] Tue, 01 August 2006 23:35 Go to next message
kittoos1
Messages: 1
Registered: July 2006
Location: ch
Junior Member
How to call a procedure which will return a refcursor from another procedure?

Can anyone help?
Re: How to call a procedure which will return a refcursor from another procedure? [message #185509 is a reply to message #185474] Wed, 02 August 2006 01:45 Go to previous message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

First create table
CREATE TABLE AMIT (
ID NUMBER,
NAME VARCHAR2(10 BYTE)
)

then insert some values in that table.

Create this procedure whuch uses REF CURSOR from another procedure.


CREATE OR REPLACE PROCEDURE ref_parent AS
TYPE my_cursor IS REF CURSOR RETURN AMIT%ROWTYPE;
return_p_rec my_cursor;
TYPE store_rec_t IS TABLE OF AMIT%ROWTYPE INDEX BY PLS_INTEGER;
store_rec store_rec_t;
--Creating Inner procedure
PROCEDURE ref_child (return_rec OUT my_cursor)AS
BEGIN
OPEN return_rec FOR SELECT * FROM AMIT;
END;
--Main Procedure
BEGIN
ref_child(return_p_rec);
FETCH return_p_rec BULK COLLECT INTO store_rec;
FOR i IN store_rec.FIRST..store_rec.LAST LOOP
DBMS_OUTPUT.PUT_LINE (store_rec(i).ID ||' ' ||store_rec(i).NAME);
END LOOP;
END;

Previous Topic: Effects of dropping a table
Next Topic: Sql query need to pivot table format
Goto Forum:
  


Current Time: Sun Dec 04 10:15:07 CST 2016

Total time taken to generate the page: 0.08543 seconds