Skip to Main Content
  • Questions
  • How to assign one ref cursor to another ref cursor and return the same to calling procedure

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, chetan.

Asked: June 28, 2017 - 1:25 pm UTC

Last updated: June 30, 2017 - 4:06 am UTC

Version: 11g, 12c

Viewed 10K+ times! This question is

You Asked

Hello Team,

Firstly Thank You all for your efforts and quick responses.

----------------------------------------------------------------------------

I have one procedure "abc" which return sys_refcursor fetching data from another database using DBLINK.

Now, I want to copy all the data from this sys_refcursor into another sys_refcursor (cursor to cursor assignment) and return it to second procedure "xyz".


----------------------------------------------------------------------------
create or replace procedure abc(ref_cur out sys_refcursor)
as
begin

open ref_cur for select * from table_name@dblink_for_another_database;

end abc;

----------------------------------------------------------------------------

create or replace procedure xyz(ref_cur out sys_refcursor)
as
p_out sys_refcursor;
begin
abc(p_out);

ref_cur := p_out;
-- Return this ref_cur to another procedure.

end xyz;

----------------------------------------------------------------------------


How can I assign one cursor to another cursor and then return it to calling procedure.

Please help.

and Connor said...

Works for me ....

SQL> create or replace procedure abc(ref_cur out sys_refcursor)
  2  as
  3  begin
  4  open ref_cur for select * from scott.emp;
  5  end abc;
  6  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure xyz(ref_cur out sys_refcursor)
  2  as
  3  p_out sys_refcursor;
  4  begin
  5  abc(p_out);
  6  ref_cur := p_out;
  7  end xyz;
  8  /

Procedure created.

SQL>
SQL> variable rc refcursor
SQL> exec xyz(:rc);

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.


You'll need to give us more details as to why it is not working for you

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

chetan kasar, June 29, 2017 - 9:04 am UTC

Hello Team,

I just found out that problem is not in cursor to cursor assignment, but it occures when I call procedure "abc" over dblink in another procedure "xyz".


----------------------------------------------------------------------------
-- Procedure "abc" is in First Database "DB1"
----------------------------------------------------------------------------
create or replace procedure abc(ref_cur out sys_refcursor)
as
begin

open ref_cur for select * from table_name;

end abc;
----------------------------------------------------------------------------


----------------------------------------------------------------------------
-- Procedure "xyz" is in Second Database "DB2"
----------------------------------------------------------------------------

create or replace procedure xyz(ref_cur out sys_refcursor)
as
p_out sys_refcursor;
begin

abc@dblink_for_DB1(p_out);

ref_cur := p_out;

end xyz;
----------------------------------------------------------------------------


exec xyz(:rc);


Got Error:
----------------------------------------------------------------------------
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor


So, here the problem occures when I call a procedure returning ref cursor over DBLINK.

Please look and suggest.
Connor McDonald
June 30, 2017 - 4:06 am UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library