How to assign a Cursor to another Cursor?

[Roeland]

Hi,

I have declared a cursor in one package and i want to assign it in another package:

Example:

Package Test1 ...

cursor c1 is
select 'a', 'b' from dual;
...
end Test1;

Package Test2...

cursor c2 = Test1.c1; -- Something like this??
-- or
type c2 is cursor Test1.c1; -- Something like this??

end Test2;

I hope it's clear what i want to achieve. (to make a new cursor type that references another cursor type).

Does anyone know how to do this?

 

[CTzen]

Not sure if this helps, but you can use cursor declared in another package without assigning it to a variable.

create or replace package pkg_rc is
    -- Public cursor declarations
    cursor test_c is select count(*) cnt from dual;
end pkg_rc;
/

create or replace package pkg_test_rc is
    -- Public function and procedure declarations
    procedure test;
end pkg_test_rc;
/

create or replace package body pkg_test_rc is
  -- Function and procedure implementations
  procedure test
  is
      l_rec pkg_rc.test_c%rowtype;
  begin
      open pkg_rc.test_c;
      fetch pkg_rc.test_c into l_rec;
      dbms_output.put_line(l_rec.cnt);
      close pkg_rc.test_c;
  end;
end pkg_test_rc;
/

set serveroutput on
exec pkg_test_rc.test

 

[Marco Kalter]

You can only assign cursor variables if they are of the same cursor type. For example:

declare
  type EmpCurTyp is ref cursor return emp%rowtype;
  EmpCur1 EmpCurTyp;
  EmpCur2 EmpCurTyp;
  EmpRec emp%rowtype;
begin
  open EmpCur1 for select * from emp;
  EmpCur2 := EmpCur1;
  fetch EmpCur2 into EmpRec;
  dbms_output.put_line(EmpRec.ename);
  close EmpCur1;
end;

 

[Roeland]

Thanks for the help, but it's not what i want.

The problem is that I have a package that's different for some customers due to historically and practical reasons. (The most of the methods is the same, but not all). Now i want to "interface" that old package with a standard package and subtype everything in this new package.

I can delegate functions, procedures and types, but it seems i can't do this for cursors.

I don't want to access the old package directly, because that would make my other packages also customer specific...

So it's not about cursor variables, it's about the cursors themselves.

--
Thanks,

Roeland