What is the difference between varray,nested table and asociative array in oracle sql plsql interview question and answer?

Collection:

It is the group of elements having the same data type and it is made of arrays, lists and sets. It is divided into three parts i.e. 

1. Varray.
2. Nested Tables.
3. Associative array or index by tables.

It is used for storing large chunks of data and performing manipulation operations with the help of keyword BULK Collect. Collection has built in methods which are as follows:

count: It gives the number of methods in the collection.

delete: It will delete all the elements from the collection and setting count to zero.

delete(n): It will delete nth element from the collection. If mth element is null, then no action is performed.

delete(n,q): It will delete elements from nth position to q position.

exists(n): Return true if n element is present in the collection else returns false.

extend: It adds null element to the collection end.

extend(n): It add m element to the collection end.

extend(n,q): It add n copies of the qth element to the collection end.

first: It gives start index number in collection with integer subscript.

last: It gives end index number in collection with integer subscript.

next(n): It gives the index that succeeds nth index.

prior(n): It gives the index that precedes nth index.

trim: It will delete an element from the end of collection.

trim(n): It will delete nth element from the end of collection.

Collection Exceptions:

COLLECTION_IS_NULL:

This exception is thrown if the collection is null by default.

NO_DATA_FOUND:

It is thrown if either a select statement fetches no row, or a program point to an element that is deleted in a nested table. It is raised by an element which is unitialised in an index by table.

SUBSCRIPT_BEYOND_COUNT:

It is thrown if the subscript is more than the total number of elements in the collection.

SUBSCRIPT_OUTSIDE_LIMIT:

It is thrown when the subscript is beyond the threshold range.

VALUE_ERROR:

It is thrown if a subscript cannot be converted to the key type or its NULL. It is raised if a key is of type PLS_INTEGER range & subscript lies beyond the range. It can be held in the column of the database. It is used for doing manipulation on SQL operational work by joining tables. It is like the dynamic array so that the upper limit can be used of any type.

 VARRAY:

It stores the fixed number of elements, and it is like one dimensional array. Number of elements can be modified at the runtime. It is consecutive set of elements which is having the similar data type. It can be handled with the help of SQL statements. Process is not easy and flexible as in the nested channel.

 Maximum size of varray is defined in its type definition. It has sequentially memory management beginning with 1st subscript. Lowest location address point is the starting element. Highest location address point is the last element. All the elements presents are identified with an index.

This collection type has numeric subscript and dense characteristics.

TYPE V_Array_type IS VARRAY(12) OF VARCHAR2(20);

v_year V_Array_type :=V_Array_type();

BEGIN

v_year.extend(12);

v_year(1):='JANUARY';

v_year(2):='FEBRUARY'; 

v_year(3):='MARCH'; 

v_year(4):='APRIL'; 

v_year(5):='MAY'; 

v_year(6):='JUNE'; 

v_year(7):='JULY'; 

v_year(8):='AUGUST'; 

v_year(9):='SEPTEMBER'; 

v_year(10):='OCTOBER';

v_year(11):='NOVEMBER';

v_year(12):='DECEMBER';

END;

create table v_year_det(v_year varchar2(20));

DECLARE

TYPE V_Array_type IS VARRAY(12) OF VARCHAR2(20);

v_year V_Array_type :=V_Array_type();

BEGIN

v_year.extend(12);

v_year(1):='JANUARY';

v_year(2):='FEBRUARY'; 

v_year(3):='MARCH'; 

v_year(4):='APRIL'; 

v_year(5):='MAY'; 

v_year(6):='JUNE'; 

v_year(7):='JULY'; 

v_year(8):='AUGUST'; 

v_year(9):='SEPTEMBER'; 

v_year(10):='OCTOBER';

v_year(11):='NOVEMBER';

v_year(12):='DECEMBER';

FORALL I in v_year.first..v_year.last

insert into v_year_det values(v_year(i));

END;

/

select * from v_year_det;

NESTED TABLE:

It is like the single column table database or 1 dimensional array where the array size is dynamic. Its having subscript is of numeric type. It can be variable by providing the rows with subscript starting from 1.It is similar like an array. It can be held in the column of the database. It has both dense and sparse collection characteristics which means that any element can be deleted randomly (sparse feature) by DELETE procedure. 

Data deletion can cause discontinuity in an index. Next function helps to iterate to the next subscript. Data is retrieved with select statements which is stored in the tables. It can be built at the schema level or in the PLSQL block. It is like the database object which is accessible within the database or the subprogram.

Syntax:

 TYPE <<type>> IS TABLE OF <<element>> [NOT NULL];

Here, type is the type specifier and element is the data type.

DECLARE

TYPE V_Array_type IS TABLE OF VARCHAR2(20);

v_year V_Array_type :=V_Array_type();

BEGIN

v_year.extend(12);

v_year(1):='JANUARY';

v_year(2):='FEBRUARY'; 

v_year(3):='MARCH'; 

v_year(4):='APRIL'; 

v_year(5):='MAY'; 

v_year(6):='JUNE'; 

v_year(7):='JULY'; 

v_year(8):='AUGUST'; 

v_year(9):='SEPTEMBER'; 

v_year(10):='OCTOBER';

v_year(11):='NOVEMBER';

v_year(12):='DECEMBER';

END;

truncate table v_year_det;

select * from v_year_det;

DECLARE

TYPE V_Array_type IS TABLE OF VARCHAR2(20);

v_year V_Array_type :=V_Array_type();

BEGIN

v_year.extend(12);

v_year(1):='JANUARY';

v_year(2):='FEBRUARY'; 

v_year(3):='MARCH'; 

v_year(4):='APRIL'; 

v_year(5):='MAY'; 

v_year(6):='JUNE'; 

v_year(7):='JULY'; 

v_year(8):='AUGUST'; 

v_year(9):='SEPTEMBER'; 

v_year(10):='OCTOBER';

v_year(11):='NOVEMBER';

v_year(12):='DECEMBER';

FORALL I in v_year.first..v_year.last

insert into v_year_det values(v_year(i));

END;

select * from v_year_det;

ASSOCIATIVE ARRAY:

It is also called as the index by tables. It has subscript to access the elements. It is represented by the key value pair. Each unique keys are used to identify the value in the array. Data type of key can be integer or string. Key is added to the index by table by simply assigning value to first time. For modification of the same entry, same key is used. Key should be unique by combining strings together to show unique value or using primary key in the table.

It provides an easy maintenance of subscript which are created in PLSQL block. It is like the SQL table where the values are obtained with primary key. It is used for temporary data storage. It can be used instead of sql tables for avoiding the network traffic and disk storage which is required by sql tables. It does not store the persistent data. So that, it can be used with sql statements like select and insert. It is used for unending sessions of the database by declaring its datatype as package and defining them inside the body of the package.

Syntax:

TYPE type IS TABLE OF element [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER| VARCHAR2(size)];

INDEX BY key;

Here, the key is numeric. It can be either BINARY_INTEGER or PLS_INTEGER.Its data type can be var,varchar2,long or string. Varchar based key should be mentioned with the length.

element is the data type

size is the maximum no of elements, and it should be positive integer value.

type is the specifier.

DECLARE

TYPE marks IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

marks_student marks;

student varchar2(20);

BEGIN

marks_student('Rahul'):=90;

marks_student('Shyam'):=99;

student :=marks_student.FIRST;

WHILE student IS NOT null LOOP

dbms_output.put_line('Student name is ' || student || ' and marks is ' || TO_CHAR(marks_student(student)));

student :=marks_student.NEXT(student);

END LOOP;

END;

/

DECLARE

TYPE V_Array_type IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(10);

v_year V_Array_type;

BEGIN

v_year(1):='JANUARY';

v_year(2):='FEBRUARY'; 

v_year(3):='MARCH'; 

v_year(4):='APRIL'; 

v_year(5):='MAY'; 

v_year(6):='JUNE'; 

v_year(7):='JULY'; 

v_year(8):='AUGUST'; 

v_year(9):='SEPTEMBER'; 

v_year(10):='OCTOBER';

v_year(11):='NOVEMBER';

v_year(12):='DECEMBER';

END;

truncate table v_year_det;

select * from v_year_det;

DECLARE

TYPE V_Array_type IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(10);

v_year V_Array_type;

BEGIN

v_year(1):='JANUARY';

v_year(2):='FEBRUARY'; 

v_year(3):='MARCH'; 

v_year(4):='APRIL'; 

v_year(5):='MAY'; 

v_year(6):='JUNE'; 

v_year(7):='JULY'; 

v_year(8):='AUGUST'; 

v_year(9):='SEPTEMBER'; 

v_year(10):='OCTOBER';

v_year(11):='NOVEMBER';

v_year(12):='DECEMBER';

FOR I in v_year.first..v_year.last

LOOP

insert into v_year_det values(v_year(i));

END LOOP;

END;

/

DIFFERENCE:

Array	Nested Table
Upper limit is fixed but it is not fixed for nested table.	Size can be increased dynamically.
Due to consecutive subscript, it is dense.	It is dense at the time of creation but become sparse once the elements are deleted in between.

VARRAY	NESTED TABLE	ASSOCIATIVE ARRAY
·        Number of elements is bounded or fixed. Variable size is fixed at the definition type.	Number of elements is not bounded or fixed.	Number of elements is not bounded or fixed. It is user defined.
·        Subscript type or index type is an integer.	Subscript type or index type is an integer.	Subscript type or index type is varchar or integer.
·        It is always continuous collection. It will not delete the elements in between.	It starts with continuous and became sparse latter.	It is neither continuous nor sparse. It is sorted as per the index information.
·        It is created in schema object and plsql block.	It is created in schema object and plsql block.	It is created in plsql block.
·        It is applicable for extend function.	It is applicable for extend function.	It is not applicable for extend function.
·        It is applicable for trim function.	It is applicable for trim function.	It is not applicable for trim function. This is because there is no separate step for an initialization process.
·        It is applicable for limit function.	It is not applicable for limit function. It will throw the null value.	It is not applicable for limit function. It will throw the error exception.
·        It is applicable for count,first,last,delete,prior,next and exits.	It is applicable for count, first, last, delete,prior,next and exits.	It is applicable for count, first,last,delete, prior, next and exits.