Solved

Oracle PLSQL : Muti-level Nested Tables

Posted on 2011-03-02
9
1,231 Views
Last Modified: 2012-05-11
I have some complex multi-level nested tables that needs to be handled. Below is the sample structure:

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;
BEGIN
      -------------
      -------------
      -------------
END;



Questions:
1) Considering there are 3 tables in the database with data (PERSON_TAB, ADDRESS_TAB & PHONE_TAB), how to create or load the data into the variable v_person?
   (I tried with BULK Collect, but costantly getting an error "PLS-00597: expression 'v_person' in the INTO list is of wrong type").

2) In scenarios like some address doesnt have any phone numbers, the address_phones should be NULL. How to assign a complete nested table as NULL within a nested table ?
0
Comment
Question by:LiNuS949
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35024825
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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35025626
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 ?
0
 

Author Comment

by:LiNuS949
ID: 35035495
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35054176
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.
0
 

Author Comment

by:LiNuS949
ID: 35055204
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


0
 
LVL 11

Expert Comment

by:yuching
ID: 35064718
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;

Open in new window

0
 

Author Comment

by:LiNuS949
ID: 35064894
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...
0
 
LVL 11

Expert Comment

by:yuching
ID: 35079047
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;

Open in new window

0
 
LVL 1

Accepted Solution

by:
sunil_rangineni earned 500 total points
ID: 35186944
Linus949, using PLSQL nested tables has some limitations when there are multi level nestings. So i would sugguest you to use Objects instead where you have much flexibility.

For more information refer
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjint.htm
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question