Collection (VARRAY, NESTED TABLE, INDEX BY TABLE) And Record Concepts In PLSQL

Collection and Record concept is very big in PLSQL, ill try to  split it and explain with possible real time scenario's.

1st let's see about VARRAY.

VARRAY: Variable Size Array

Varray is Data structure provided by PLSQL to store fixed size(Limit) of elements/subscripts.

You need to remember is ,VARRAY can hold Similar Data type only  with FIXED size/Elements/subscripts.

One more important is ,

VARRAY And Nested table can be created at SQL level also using "Create or Replace " Command.

It means you can use as Data type whenever you required in your program Or create table  scenario's.

(Ill explain details on this in continuation posts)

Here Elements means data storage points/Indexes.

                                 1.1 VARRAY data storage structure

VARRAY data storage looks as above .

here 1,2,3,4,5,6 are Elements/Index and VENKY, TEJA...... is the data which is stored in respective Indexes.

Syntax:

CREATE OR REPLACE TYPE VARRAY_TYPE_NAME IS VARRAY(SIZE) OF <ELEMENT TYPE>

Above Syntax for SQL level.

In PLSQL you need to follow below Syntax.

TYPE VARRAY_TYPE_NAME IS VARRAY(SIZE) OF <ELEMENT TYPE>.

If you observe both SQL and PLSQL syn-taxes ,Only difference is "Create or Replace ".

VARRAY_TYPE_NAME is the name which you can specify meaningfully according to your requirement.

SIZE :- Need to specify size based on your requirement to hold /store in your program.

ELEMENT TYPE :- Need to specify Data type which reflects to what kind of data you want to store/Hold.

Eg:

CREATE OR REPLACE TYPE SALARY IS VARRAY(8) OF INTEGER;

SQL> CREATE OR REPLACE TYPE EMP_NAME IS VARRAY(5) OF VARCHAR2(100);

  2  /

Type created

 Here "EMP_NAME" Varray will hold maximum 5 elements only and also it can store only varchar2 data.

let's see one Eg. Program

Below Program is created to hold Maximum 5 Elements and to hold only Varchar2 data.

Pls Refer following links for Normal error which you may face while using Collection Concepts.

Ref  for Uninitialized error https://mysqlplsqltutorial.blogspot.in/2017/02/ora-06531-reference-to-uninitialized_6.html

Ref for Subscript Outside Limit https://myplsqltutorial.blogspot.in/2017/02/ora-06532-subscript-outside-of-limit.html

Ref For Subscript Beyond Count https://myplsqltutorial.blogspot.in/2017/02/ora-06533-subscript-beyond-count.html

General issues which you may face is Un initialization  error    and  Subscript beyond count error and Subscript outside of limit error if you dont initialize and do not extend allocation and if you allocate more than allocated elements to store data.

Another eg: for VARRAY using FOR loop.

Pls check below post to understand Collection Methods in collection.

https://myplsqltutorial.blogspot.in/2017/02/methods-in-collection-concepts.html

In Further posts ,we ill check it about Nested table and Index by table with real time scenario's.

Ref : For Nested table check here https://myplsqltutorial.blogspot.in/2017/02/nested-table-in-plsql.html

Pls post your queries if you have doubts on this topic.