Q: Using SELF's nested table in a function?

From: Wolfgang May <may_at_informatik.uni-freiburg.de>
Date: 8 Dec 1998 18:16:05 GMT
Message-ID: <74jqd5$qjj$1@n.ruf.uni-freiburg.de>

Hi,

I have a problem with using a table-valued attribute of an object table in a member function (setting: a geo-database for educational purposes):

CREATE TYPE Member_Type AS OBJECT
 (Country VARCHAR2(4),
  Type VARCHAR2(30));
/

CREATE TYPE Member_List_Type AS
 TABLE OF Member_Type;
/

CREATE TABLE Country
(Code VARCHAR2(4));

CREATE OR REPLACE TYPE Organization_Type AS OBJECT  (Abbrev VARCHAR2(12),
  Members Member_List_Type,
  MEMBER FUNCTION people RETURN NUMBER,   PRAGMA RESTRICT_REFERENCES (people,WNDS,WNPS));
/

CREATE TABLE Organization_ObjTab OF Organization_Type  (Abbrev PRIMARY KEY)
 NESTED TABLE Members STORE AS Members_nested;

CREATE OR REPLACE TYPE BODY Organization_Type IS MEMBER FUNCTION people RETURN NUMBER IS p NUMBER;
BEGIN
  SELECT SUM(population) INTO p
  FROM Country
  WHERE Code IN (SELECT Country

                 FROM TABLE (Members)); -- <<<<<<<<<<<<<<<<<<<

  RETURN p;
END;
END;
/

INSERT INTO Organization_ObjTab VALUES
 (Organization_Type('EU',
  Member_List_Type(member_type('D','full'))));

compiles without problem ...

SELECT abbrev, o.people()
FROM Organization_ObjTab o;

complains:

SQL> SQL> SELECT abbrev, o.people()
FROM Organization_ObjTab o;
  2 SELECT abbrev, o.people()

               *
FEHLER in Zeile 1:

ORA-00903: Ungueltiger Tabellenname [engl.: invalid table name]
ORA-06512: in "MAY.ORGANIZATION_TYPE",  Zeile 5
ORA-06512: in  Zeile 1

where "members" is a collection-valued attribute of the object:

SQL> select members from Organization_ObjTab;

MEMBERS(COUNTRY, TYPE)

---

MEMBER_LIST_TYPE(MEMBER_TYPE('D', 'full'))

Question: How can this be used in the function ? (I tried SELF.members and some other things which did not succeed)

Can someone perhaps mail/post an example where a nested table is used in a member function/procedure?

THX, Wolfgang

• ----------------------------------------------------------------- Wolfgang May E-mail: may_at_informatik.uni-freiburg.de Universitaet Freiburg http://www.informatik.uni-freiburg.de/~may/ Institut fuer Informatik D-79110 Freiburg / Germany Lehrstuhl fuer Datenbanken und Informationssysteme

Received on Tue Dec 08 1998 - 12:16:05 CST