How to declare ref cursor parameter to a object method?

Question

I'm a little bit new to PL/SQL and need something that looks a bit like this:

create type base as object (
  unused number,
  member procedure p( c in ref cursor )
) not final;

create type child1 under base (
  overriding member procedure p( c in ref cursor ) as
    t table1%rowtype
  begin
    fetch c into t;
    -- process table1 row
  end;
);

create type child2 under base (
  overriding member procedure p( c in ref cursor ) as
    t table2%rowtype
  begin
    fetch c into t;
    -- process table2 row
  end;
);

procedure generic_handler( o in base, c in ref cursor ) as
begin
  o.p( c );
end;

o1 child1 := child1(0)
o2 child2 := child2(0)

c ref cursor
open c for select * from table1;
generic_handler( o1, c );

open c for select * from table2;
generic_handler( o2, c );

Basically, I need a single generic routine that knows how to perform a table-independent action delegating table-specific tasks to a derived class.

The above object methods taking 'ref cursor's don't compile - compiler says 'cursor needs to be defined'. So of course I've tried 'type generic_cursor as ref cursor' all over the place but can't get it to compile.

I found pretty much nothing when trying to track down the syntax for passing ref cursors to object methods. And this made me think that perhaps I'm trying to do something stupid.

Does what I'm trying to do make sense? If so, what am I missing? Where can I define the generic_cursor so that I can use it as an object method parameter type?

Answer 1

Your code will work once you sort out the syntactical errors.

SQL> create or replace type base as object
  2  (  unused number
  3      ,  member procedure p( c in sys_refcursor )
  4  )
  5  not final;
  6  /

Type created.

SQL>
SQL> create or replace type child1 under base (
  2      overriding member procedure p( c in sys_refcursor )
  3  );
  4  /

Type created.

SQL> create or replace type body child1 as
  2      overriding member procedure p( c in sys_refcursor )
  3          as
  4              t dept%rowtype;
  5          begin
  6              loop
  7                  fetch c into t;
  8                  exit when c%notfound;
  9                  dbms_output.put_line('dname='||t.dname);
 10              end loop;
 11          end;
 12  end;
 13  /

Type body created.

SQL>
SQL> create or replace type child2 under base (
  2      overriding member procedure p( c in sys_refcursor )
  3   );
  4  /

Type created.

SQL> create or replace type body child2 as
  2      overriding member procedure p( c in sys_refcursor )
  3          as
  4              t emp%rowtype;
  5          begin
  6              loop
  7                  fetch c into t;
  8                  exit when c%notfound;
  9                  dbms_output.put_line('ename='||t.ename);
 10              end loop;
 11          end;
 12  end;
 13  /

Type body created.

SQL>
SQL>
SQL> create or replace procedure generic_handler
  2          ( o in out base, c in sys_refcursor )
  3          as
  4  begin
  5      o.p( c );
  6  end;
  7  /

Procedure created.

SQL>
SQL> set serveroutput on size unlimited
SQL>
SQL> declare
  2      o1 child1 := child1(0);
  3      o2 child2 := child2(0);
  4      rc sys_refcursor;
  5  begin
  6      open rc for select * from dept where deptno = 10;
  7      o1.p(rc);
  8      open rc for select * from emp where deptno = 10;
  9      o2.p(rc);
 10  end;
 11  /
dname=ACCOUNTING
ename=BOEHMER
ename=SCHNEIDER
ename=KISHORE

PL/SQL procedure successfully completed.

SQL>

The Oracle documentation is pretty hard to understand when you're new. I think in your case you need to know that the Object_Oriented stuff is in a different book from the regular PL/SQL information. You will probably need to check both whenever you're stumped.