Home » SQL & PL/SQL » SQL & PL/SQL » Interaction between type inheritance and nested tables (Oracle 11g, Windows XP 32bit)
Interaction between type inheritance and nested tables [message #360732] Sun, 23 November 2008 14:17 Go to next message
G_Morgan
Messages: 3
Registered: November 2008
Junior Member
I'm trying to create a table of a type Person that is a superclass of two other types SalesPerson and Applicant. Both SalesPerson and Applicant have nested tables of references to a further type Appointment.

When creating the table with the command:

create table PersonTable of Person
    (pNumber PRIMARY KEY)
    nested table makes store as SalesPersonAppTable,
    nested table attends store as ApplicantAppTable;


it complains about attends being an invalid identifier. If I reverse the order of the nested tables it then complains about makes being invalid.

I've tried to specify the identifiers as below

create table PersonTable of Person
    (pNumber PRIMARY KEY)
    nested table SalesPerson.makes store as SalesPersonAppTable,
    nested table Applicant.attends store as ApplicantAppTable;


but I get a similar complaint (except it now says Applicant.attends is invalid).

Any idea how I can set up the nested tables here while still allowing me to store the SalesPerson and Applicant types in the same table? Unfortunately it has to be done this way otherwise I'd just move the table of Appointments up to the Person type. They're both computationally the same thing if not semantically.
Re: Interaction between type inheritance and nested tables [message #360736 is a reply to message #360732] Sun, 23 November 2008 17:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8623
Registered: November 2002
Location: California, USA
Senior Member
What happens if you omit the storage clause? Please post the code for all related types and/or tables.
Re: Interaction between type inheritance and nested tables [message #360740 is a reply to message #360732] Sun, 23 November 2008 18:10 Go to previous messageGo to next message
G_Morgan
Messages: 3
Registered: November 2008
Junior Member
Last time I tried it without a store clause it complained about needing the nested tables declared or some such. I'll try it again.

The code for the types is

CREATE TYPE Appointment;
/

CREATE TYPE AppointmentList AS TABLE of REF Appointment;
/

CREATE OR REPLACE TYPE Person AS OBJECT (
    pNumber         number(10),
    pSurname        varchar2(200),
    pForname        varchar2(200),
    pDateOfBirth    date
    ) not final;
/

CREATE OR REPLACE TYPE SalesPerson under Person (
    makes           AppointmentList
    ) final;
/

CREATE OR REPLACE TYPE Applicant under Person (
    attends         AppointmentList
    ) final;
/

CREATE OR REPLACE TYPE Appointment AS OBJECT (
    appNumber       number,
    madeBy          REF SalesPerson,
    attendedBy      REF Applicant
    );
/


I've omitted most of the data members other than the links because the individual types are created just fine. I'll go try modifying it without a store clause.
Re: Interaction between type inheritance and nested tables [message #360741 is a reply to message #360736] Sun, 23 November 2008 18:32 Go to previous message
G_Morgan
Messages: 3
Registered: November 2008
Junior Member
Right I tried to remove the store clauses. At first it gave me the same problem as before. Then I blitzed and rebuilt the entire DB and it worked that time. I don't know if the actual nested tables work because I haven't tried any insertions to them yet but will do so shortly.
Previous Topic: Howto Initialize collection containing collection?
Next Topic: find value from table
Goto Forum:
  


Current Time: Sat Dec 03 13:42:28 CST 2016

Total time taken to generate the page: 0.05593 seconds