Oracle PLSQL : Muti-level Nested Tables

hi

read this for assigning Table types in PL/SQl blocks.

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

can you give the table structures with data types of these tables ( PERSON_TAB, ADDRESS_TAB & PHONE_TAB )

Also just give 1 sample record in each table and tell what you need to load into v_person ?

Imagin the exact structure as the types. But with multiple records in each table.

Note: the structure i provided is a sample.. With in app or db, we are using nesting upto 7 levels. i.e.

I am sucessful in loading the data or assigning NULL to a nested table only when it is a simple nested table (i.e., no table within a table).. But couldn't get the things done when i have multiple nesting.

i don't need 7 level tables scripts.

All i need is a sample table script ( for 2 levels ) to understand your issue. i do not have enough time to setup these tables for you and hence asking the script.

nav_kum_v, i didn't mean that you got to create all the 7 level table scripts. I just wanna tell that in my program there will be 7 levels.. Sorry for the confusion. Here you go.. [ structure with sample data]

PERSON
--------
person_id      person_name
------------------------------------
100            Sunil
200            Linus


PERSON_ADDRESS
--------------------------
Address_id      person_id      add_line1      add_line2      street      city      country
----------------------------------------------------------------------------------------------------------------
1            100            xyz            abc            lmn      123      india
2            100            bac            iuds            xyd      934      india
3            200            poi            yua            ier      343      india



PERSON_PHONES
--------------------
Address_id      country_code      area_code      phone_num      phone_ext
---------------------------------------------------------------------------------------------------
1            001            383            32843903      3879
1            001            763            23937731            
2            091            80            36432873      3243
2            091            80            32984237      8742

try this

Declare
  TYPE phone IS RECORD (
    country_code NUMBER,
    area_code      NUMBER,
    phone_num      NUMBER,
    phone_ext      NUMBER                 
    );
  TYPE phone_tab IS TABLE OF phone;

  TYPE address IS RECORD (
    address_id      number, 
    add_line1      varchar2(100), 
    add_line2      varchar2(100),
    street            varchar2(100),
    city            varchar2(100),
    country            varchar2(100),
    address_phones      phone_tab);

  TYPE address_tab IS TABLE OF address;

  TYPE person IS RECORD (
    person_id      NUMBER,
    person_name      VARCHAR2(100),
    person_address      address_tab);

  v_person      person;
  v_addlist address_tab;
  v_phonelist phone_tab;
  i Integer:=0;
  j Integer:=0;
Begin
	
  For r In (Select * From test_person Where person_id =100) Loop
    v_person.person_id := r.person_id ;
	v_person.person_name := r.person_name ;
	i :=0;
	v_addlist := address_tab();
	For x In (
		Select address_id, add_line1, add_line2, street, city, country
		From test_address a Where a.person_id = r.person_id
	) Loop
		v_addlist.Extend;
		i:= i+ 1;
		v_addlist(i).address_id := x.address_id;
		v_addlist(i).add_line1 := x.add_line1;
		v_addlist(i).add_line2 := x.add_line2;
		v_addlist(i).street := x.street;
		v_addlist(i).city := x.city;
		v_addlist(i).country := x.country;	
			
		j:=0;
		v_phonelist := phone_tab();	
			
		For y In (
			Select country_code, area_code, phone_num, phone_ext
			From test_phone b Where b.address_id = x.Address_Id
		) Loop
			v_phonelist.Extend;
			j := j +1;
			v_phonelist(j).country_code := y.country_code;
			v_phonelist(j).area_code := y.area_code;
			v_phonelist(j).phone_num := y.phone_num;
			v_phonelist(j).phone_ext := y.phone_ext;
		End Loop;
			
		v_addlist(i).address_phones := v_phonelist;
	End Loop;
	v_person.person_address := v_addlist;
  End Loop;
END;

Select allOpen in new window

thanks Yuching. Yes by looping through we can acheive this. But I was rather looking at something using an SQL (i.e., something like BULK COLLECT) looking from performace perspective.

Also how to assign a complete nested table to NULL within a nested table...

Hi LiNuS949:
I can't think of better way besides using types,
The code attached is to create different types for each of the records and doing the selection in constructor.

After create the types in the database, do the selection as below:-

---- ################  CREATE TYPES ##################
-- Drop the types if they exists
Drop Type Person;
Drop Type address_tab;
Drop Type address;
Drop Type phone_tab;
Drop Type phone;

-- Create phone type
CREATE OR REPLACE TYPE phone As Object (
  country_code NUMBER,
  area_code      NUMBER,
  phone_num      NUMBER,
  phone_ext      Number
 );

CREATE OR REPLACE TYPE phone_tab IS TABLE OF phone;

-- Create phone Address
CREATE OR REPLACE TYPE address As Object (
  address_id      number,
  add_line1      varchar2(100),
  add_line2      varchar2(100),
  street            varchar2(100),
  city            varchar2(100),
  country            varchar2(100),
  address_phones      phone_tab,
	
  CONSTRUCTOR FUNCTION address(aaddress_id Number, aadd_line1 Varchar2, aadd_line2 Varchar2, astreet Varchar2, acity Varchar2, acountry Varchar2 )  RETURN SELF AS Result
 );

Create Or Replace Type Body address is
  CONSTRUCTOR FUNCTION address(aaddress_id Number, aadd_line1 Varchar2, aadd_line2 Varchar2, astreet Varchar2, acity Varchar2, acountry Varchar2 )  RETURN SELF AS Result Is 
  Begin
    SELF.address_id := aaddress_id;
    SELF.add_line1 := aadd_line1;
    SELF.add_line2 := aadd_line2;
    SELF.street := astreet;
    SELF.city := acity;
    SELF.country := acountry;

    Select phone( country_code, area_code,phone_num, phone_ext)
    Bulk Collect Into Self.address_phones
    From test_phone
    Where address_id = aaddress_id;

   Return;
  End address;
End;
	
CREATE OR REPLACE TYPE address_tab IS TABLE OF address;

-- Create Person type 
CREATE OR REPLACE TYPE person As Object  (
  person_id      NUMBER,
  person_name      VARCHAR2(100),
  person_address      address_tab,
	
  CONSTRUCTOR FUNCTION person(aperson_id Number, aperson_name Varchar2) RETURN SELF AS Result
);

Create Or Replace Type Body Person is
  CONSTRUCTOR FUNCTION person(aperson_id Number, aperson_name Varchar2) RETURN SELF AS Result Is
  Begin
    SELF.person_id := aperson_id;
    SELF.person_name  := aperson_name;
    Select address( address_id, add_line1,add_line2, street,city, country)
    Bulk Collect Into Self.address_tab
    From test_address
    Where person_id = aperson_id;

    Return;
  End person;
end;

--- ##########################################################
---          SELECTION OF DATA
--- ##########################################################
Declare
  Type tPerson Is Table Of person;
  v_person  tperson;
  v_address address_tab;
  v_phone phone_tab;
  fRowNum Integer := 0;
Begin

  Select person(person_id, person_name ) 
  Bulk Collect Into v_person
  From test_person;

  -- TO PRINT DETAILS
  fRowNum := v_person.First;
  WHILE (fRowNum IS NOT NULL)
  Loop
    -- Get Person
    dbms_output.put_line('Person Id:' || v_person(fRowNum).person_id);
		
    -- Get address
    v_address := v_person(fRowNum).person_address;
    If v_address.Count > 0 Then
      For i In v_address.First.. v_address.Count Loop
        dbms_output.put_line('Address ID:' || v_address(i).address_id);
        v_phone := v_address(i).address_phones;
        dbms_output.put_line(v_phone.Count);
        -- Get Phone
        If v_phone.Count > 0 Then
          For j In v_phone.First.. v_phone.Count Loop
            dbms_output.put_line('Phone Area Code:' || to_char(v_phone(j).area_code));
          End Loop;
        End If;
      End Loop;
    End If;
		
    fRowNum := v_person.NEXT (fRowNum); -- next person

  End Loop;
END;

Select allOpen in new window