|
|
| 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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
