Oracle Deref function : wrong numer or type of arguments

I'm trying to create an object method in Oracle like this :

CREATE OR REPLACE TYPE BODY TheType AS
    MEMBER FUNCTION getAtt RETURN VARCHAR2 IS
    BEGIN
        RETURN DEREF(SELF.Att).Att2;
    END;
END;
/

But I got the following error :

PLS-00306: wrong number or types of arguments in call to 'DEREF'

Also the type TheType is declared like that :

CREATE OR REPLACE TYPE TheType UNDER SuperType ();
/

...

ALTER TYPE TheType ADD ATTRIBUTE ( Att REF TheType ) CASCADE;

...

ALTER TYPE TheType 
    ADD MEMBER FUNCTION getAtt RETURN VARCHAR2
CASCADE;

And the definition of Supertype :

CREATE OR REPLACE TYPE SuperType AS OBJECT ( Att2 VARCHAR2(50) )
NOT FINAL NOT INSTANTIABLE;
/

I give to DEREF function a var with the right type, why this error occurs ?

It should work if I trust the Oracle doc

Thanks.

Answers


shift the DEREF into SQL. e.g.

SQL> create or replace type supertype as object ( att2 varchar2(50) )
  2  not final not instantiable;
  3  /

Type created.

SQL> create or replace type thetype under supertype (
  2  att ref thetype,
  3  member function getatt return varchar2);
  4  /

Type created.

SQL> show errors type thetype
No errors.
SQL> create or replace type body thetype as
  2    member function getatt return varchar2 is
  3      v_t thetype;
  4    begin
  5             select deref(self.att) into v_t from dual;
  6             return v_t.att2;
  7    end;
  8  end;
  9  /

Type body created.

SQL> show errors type body thetype
No errors.
SQL> create table thetypes of thetype;

Table created.

SQL> insert into thetypes values ('hi there', null);

1 row created.

SQL> set serverout on
SQL> declare
  2    v_t thetype;
  3  begin
  4    select thetype(null, ref(a)) into v_t from thetypes a;
  5    dbms_output.put_line(v_t.getatt);
  6  end;
  7  /
hi there

PL/SQL procedure successfully completed.

SQL>

Need Your Help

JPA Constraint for date year

java jpa unique-constraint

I have an entity representing Documents that looks like this:

Mutually exclusive random sampling from a list. Python

python function random sample mutual-exclusion

input = ['beleriand','mordor','hithlum','eol','morgoth','melian','thingol']

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.