What is VARRAY in Oracle?

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

They are variable length,1D array and store elements of same type as arrays of other languages. We need to specify the size at the time of VArray declaration.

Let us say that a person has 2 mobile numbers. We can store these mobile numbers into a varray. Let us see how.

Step 1:First we need to create a Varray Type as under

Declare

Type Mobile_Numbers_Type IS VARRAY(2) OF NUMBER;
Mobile_Numbers Mobile_Numbers_Type;


Step 2: Then we need to initialize it

Declare 


Type Mobile_Numbers_Type IS VARRAY(2) OF NUMBER;

Mobile_Numbers Mobile_Numbers_Type;

Begin

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


Step3:We can access the values as under

SQL> Set ServerOutput On;

SQL> Declare
2
3 Type Mobile_Numbers_Type IS VARRAY(2) 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 DBMS_OUTPUT.PUT_LINE('Second value is: ' || Mobile_Numbers(2));
13 End;
14 /
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