How can I do forward declarations in Oracle?

Here is my situation: type A wants to have a metho开发者_运维问答d that returns a type that is a table of type A entries. Can I do this?

Here is a pair of SQL Type declarations:

SQL> create or replace type a as object
  2      ( attr1 number
  3        , attr2 date )
  4  /

Type created.

SQL> create or replace type a_nt as table of a
  2  /

Type created.

SQL>

Now what we want is a method which returns A_NT(). Hmm, let's see:

SQL> alter type a
  2      add member function getme (p1 number) return a_nt
  3  /
alter type a
*
ERROR at line 1:
ORA-04055: Aborted: "A" formed a non-REF mutually-dependent cycle with "A_NT".


SQL>

Uh-oh. We need another approach. This is the sort of occasion when we should be using inheritance.

SQL> create or replace type abstract_a as object
  2      ( attr1 number
  3        , attr2 date )
  4  not final not instantiable
  5  /

Type created.

SQL> create or replace type a_nt as table of abstract_a
  2  /

Type created.

SQL> create or replace type a under abstract_a
  2      ( member function getme (p1 number) return a_nt )
  3  instantiable
  4  /

Type created.

SQL>

Looks good. So, we'll add an implementation and then try it out:

SQL> create or replace type body a as
  2      member function getme (p1 number) return a_nt
  3      is
  4          l_nt a_nt;
  5      begin
  6          select a(empno, hiredate)
  7          bulk collect into l_nt
  8          from emp
  9          where deptno = p1;
 10          return l_nt;
 11      end;
 12  end;
 13  /

Type body created.

SQL>

Let's roll!

SQL> set serveroutput on
SQL> declare
  2      v a := a(null, null);
  3      n a_nt;
  4  begin
  5      n := v.getme(50);
  6
  7      for i in n.first()..n.last() loop
  8          dbms_output.put_line(n(i).attr1 ||'::'||to_char(n(i).attr2, 'DD-MON-YYYY'));
  9      end loop;
 10  end;
 11  /
8085::08-APR-2010
8060::08-APR-2008
8061::27-FEB-2010
8100::

PL/SQL procedure successfully completed.

SQL>

The bootstrapping is cumbersome but it works. This may be yet another area where Oracle's OOP implementation is incomplete. But perhaps it's just a reflection of the fact that is not a good idea. It took me a while to figure it out, because the underlying model doesn't really make sense. Although maybe with Real World names it would have become obvious :)