How do I create a nested table using Oracle

Question

I have created the following in Oracle PSL/SQL:

I have created a type Animal.
This has the attributes: Name, Age

I have created the type Dog. This inherits from type Animal
The only extra field in Dogis a nested table of references of place lived. I want to store all instances of Dogin the Animal table.

This is the bit I am confused about: When creating the Animal table of type Animal, how do I create the nested table for places lived? There is no field in Animal for this, only in Dog.

this may be an example only - but not a good schema design. the animal should not contain any reference to places lived - that is a normalization problem — Randy
– Randy, Commented Dec 2, 2012 at 16:18
@Randy - object-oriented programming is largely about hierarchies rather than relationships. Hence normalization is irrelevant here. It's the root cause of the "object-relational impedance". But given that Oracle's OO implementation allows nested table it is legitimate to want to know how to use them. — APC
– APC, Commented Dec 2, 2012 at 16:34
@vikiiii - fnord. I don't think you grasp how inheritance and polymorphism work in an object-oriented programming. — APC
– APC, Commented Dec 2, 2012 at 16:35
@APC - acknowledged - i was just pointing out that maybe this is not the best real world example. — Randy
– Randy, Commented Dec 3, 2012 at 15:38

APC · Accepted Answer · 2012-12-02 16:39:06Z

6

"When creating the Animal table of type Animal, how do I create the nested table for "places lived"? when there is no field in Animal for this, only in Dog."

This is the mystery of inheritance. A table built from the type ANIMAL actually has columns to support the attributes of its sub-types. However, they are only accessible when we explicitly use the DOG sub-type.

Here is your data structure.

create or replace type animal_t as object 
  ( name varchar2(10)
    , age number (3,0))
not final;
/

create or replace type places_nt as table of varchar2(20)
/

create or replace type dog_t under animal_t
 ( residence_history places_nt)
/

create table animals of animal_t;

To create a record for a goldfish we do this:

insert into animals
  values (animal_t('BOB', 7))
/

To create a record for a dog we need to do this:

insert into animals
  values (dog_t('FIDO', 12, places_nt('Balham', 'Tooting')))
/

This query will just select the generic columns:

SQL> select * from animals
  2  /

NAME              AGE
---------- ----------
BOB                 7
FIDO               12

SQL>

To gain sight of the details pertaining to dogs we need to cast the record to the relevant sub-type:

SQL> select treat(value(a) as dog_t)
  2  from animals a
  3  where value(a) is of (dog_t)
  4  /

TREAT(VALUE(A)ASDOG_T)(NAME, AGE, RESIDENCE_HISTORY)
--------------------------------------------------------------------------------
DOG_T('FIDO', 12, PLACES_NT('Balham', 'Tooting'))

SQL>

There is an entire book in the Oracle documentation devoted to its Object-Relational features. Find out more.

Note: I have used an object table here simply because it is easy to illustrate how nested tables work. I do not recommend using types for data storage: OO is a programming paradigm and should only be used for writing programs. Data should always be persisted in relational structures.

edited Dec 2, 2012 at 16:39

answered Dec 2, 2012 at 16:25

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dewijones92 Over a year ago

thanks so much for this This has helped me tremendously!! :D Just out of interest, what is your favourite editor for writing PSL/SQL?

APC Over a year ago

@dewijones92 - for most of my career I have been happy with TextPad and SQL*Plus. However, recently I have become enamoured of Allround Automation's PL/SQL Developer: the license is quite reasonable and it works very well.

Collectives™ on Stack Overflow

How do I create a nested table using Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related