Solved

Oracle PLSQL : Muti-level Nested Tables

Posted on 2011-03-02
9
1,209 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:LiNuS949
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now