What is Nested Table in Oracle?

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.