Re: Returning Ref cursor between two oracle database

From: basant <basantshanker_at_hotmail.com>
Date: 9 May 2005 06:58:59 -0700
Message-ID: <1115647139.310773.119040@f14g2000cwb.googlegroups.com>

Step 1:

Creating a package which returns Ref Cursor in database A Any table can be used here. Here I have taken emp table as example

CREATE OR REPLACE PACKAGE sun_test IS

TYPE return_cur IS REF CURSOR RETURN emp%ROWTYPE;

PROCEDURE child(p_return_rec OUT return_cur); PROCEDURE parent(p_NumRecs PLS_INTEGER);

END sun_test;
/

CREATE OR REPLACE PACKAGE BODY sun_test IS

PROCEDURE child (p_return_rec OUT return_cur) IS BEGIN
   OPEN p_return_rec FOR
   SELECT * FROM emp;

END child;

--==============================================

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS

p_retcur return_cur;
at_rec emp%ROWTYPE;

BEGIN
   child(p_retcur);

   FOR i IN 1 .. p_NumRecs
   LOOP

      FETCH p_retcur
      INTO at_rec;
      END LOOP;

END parent;

END sun_test;
/

SQL> SET SERVEROUTPUT ON
SQL> exec strongly_typed.parent(1)
SQL> exec strongly_typed.parent(8)

This works fine..

Step 2:

In Database B
Here I have another package , where I need to make use of RefCursor from the above package in Database A.

I have to do two operations in database B, need to retrieve data from database A using Refcursor and also insert data in to table in database A by passing refcursor from database B to database A.

Thanks
Basant Received on Mon May 09 2005 - 08:58:59 CDT