What is Nested Table in Oracle?

 Posted by Niladri.Biswas on 5/2/2013 | Category: Oracle Interview questions | Views: 1573 | Points: 40
Answer:

They are 1D array like VArrays and can be use either in a relational table or in functions.Like VArrays, they are use for columns having multiple values.But they does not need any size to be define on them like VArrays and hence are unbound.They are,however, limited to the amount of memeory available.Each row of the nested table should be of the same type.

e.g. Let us say that a person has 2 mobile numbers. We can store these mobile numbers into a Nested Table.

Step 1:First we need to create a Nested Table Type

Declare

Type Mobile_Numbers_Type IS TABLE OF NUMBER;
Mobile_Numbers Mobile_Numbers_Type;


Step 2:Then we need to initialize it

Declare 


Type Mobile_Numbers_Type IS TABLE OF NUMBER;

Mobile_Numbers Mobile_Numbers_Type;

Begin

Mobile_Numbers := Mobile_Numbers_Type(1111111111,1000000000);
End;


Step 3: We can access the values as under

SQL> Set ServerOutput On;

SQL> Declare
2
3 Type Mobile_Numbers_Type IS TABLE OF NUMBER;
4
5 Mobile_Numbers Mobile_Numbers_Type;
6
7 Begin
8
9 Mobile_Numbers := Mobile_Numbers_Type(1111111111,1000000000);
10
11 DBMS_OUTPUT.PUT_LINE('First value is: ' || Mobile_Numbers(1));
12
13 DBMS_OUTPUT.PUT_LINE('Second value is: ' || Mobile_Numbers(2));
14 End;
15 /
First value is: 1111111111
Second value is: 1000000000

PL/SQL procedure successfully completed.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response