Collections in Oracle - 1

Collections(Basic Information)

Collection consists of multiple elements (chunks of data), each element of which is located at a certain index value in the list.

Sometimes elements are also referred to as a ‘row’ and index value referred to as the 'row number’

Collection Type:

Each collection variable in a program must be declared based on the pre-defined collection type.

A)     Index by table / Associative arrays /PL SQL table

B)      Nested table

C)      Varrays

The term “collection” may refer to any of the following:

• A PL/SQL variable of type associative array, nested table, or VARRAY
• A table column of type nested table or VARRAY

Regardless of the particular type or usage, however, a collection is at its core a single-dimensional list of homogeneous elements.

 A collection instance is an instance of a particular type of collection.

Unbounded vs Bounded:

A collection is set to bounded if you limit the possible values for the row numbers in that collection.

                VARRAY’s or variable sized arrays are always bounded. When you define them, you specify the maximum number of rows allowed in that collection (the first row number is always 1).

A collection is set to be unbounded if there are no upper or lower limits on those row numbers.

Nested tables and Associative arrays are unbounded.

Sparse vs Dense:

A collection is called dense if all the rows between the first and last is defined and given a value (including NULL).

                VARRAY’s are always dense

A collection is sparse if the rows are not defined and populated sequentially.

                Nested table always start as dense collections but can be made sparse.

                Associative arrays can be sparse or dense.

Note:  Sparseness is a very valuable feature, as it gives you the flexibility to populate rows in a collection using a primary key or other intelligent key data as the row number. By doing so, you can define an order on the data in a collection or greatly enhance the performance of lookups.

Indexed by integers

All collections support the ability to reference a row via the row number, an integer value. The associative array TYPE declaration makes that explicit with its INDEX BY clause, but the same rule holds true for the other collection types.

Indexed by strings

 Starting with Oracle9i Database Release 2, it is possible to index an associative array by string values (currently up to 32K in length) instead of by numeric row numbers. This feature is not available for nested tables or VARRAYs.
                E.g.: Index by varchar2(20);

--------------------------------------------------EXAMPLES---------------------------------------------------------

Associative Arrays

Lets display employee names and  salary from ‘emp’ table using BULK COLLECT.

Declare
type t is record(v_name varchar2(10), v_sal number);
type emp_sal is table of t
index by binary_integer;
v_rec emp_sal;
begin
select ename, sal bulk collect into v_rec from emp;
for i in 1..v_rec.last loop
dbms_output.put_line(v_rec(i).v_name||' '||v_rec(i).v_sal);
end loop;
end;
/
Output

A)     SMITH 801

B)      ALLEN 1602

C)      WARD 1251

D)     JONES 2978

E)      MARTIN 1251

F)      BLAKE 2853

G)     CLARK 2453

Insert values in EMP table

Declare
type t is table of number
index by binary_integer;
v_empno t;
begin
v_empno(1) := 1101;
v_empno(2) := 1102;
for I in 1..v_empno.last loop;
insert into emp (empno) values(v_empno(i));
dbms_output.put_line(sql%rowcount||’ rows : inserted’);
end loop;
end;

Output:

1rows:inserted
1rows:inserted

1.       1101       NULL     NULL     NULL     NULL     NULL     NULL                                                                                    

2.       1102       NULL     NULL     NULL     NULL     NULL     NULL                                                                                    

3.       7369       SMITH   CLERK    7902       17-DEC-80 801   20

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

Declare
type sal  is table of number index by varchar2(20);
sal_list sal;
name varchar2(20);
begin
--Adding elements to the table
sal_list(‘Vinay’) := 10000;
sal_list(‘Kumar’) := 20000;
sal_list(‘Raghu’) := 30000;
name := sal_list.first;
while name is not null loop
dbms_output.put_line(name||’ is receiving sal of ’||sal_list(name));
name := sal_list.next(name);
end loop;
end;

PL/SQL procedure successfully completed.

Kumar is receiving sal of 20000
Raghu is receiving sal of 30000
Vinay is receiving sal of 10000

Example: Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the EMP table stored in our database.

declare
type t is table of emp.ename%type index by binary_integer;
v_ename t;
v_count number := 0;
cursor c is select ename from emp;
begin
for i in c loop
v_count := v_count+1;
v_ename(v_count) := i.ename;
dbms_output.put_line(v_count|v_ename(v_count));
end loop;
end;
/

PL/SQL procedure successfully completed.

1SMITH
2ALLEN

For displaying multiple columns
Declare
type t_rec is record(ename emp.ename%type, sal emp.sal%type);
type t is table of t_rec index by binary_integer;
cursor c is select ename, sal from emp;
v_tbl t;
v_count binary_integer := 0;
begin
for i in c loop
v_count := v_count+1;
v_tbl(v_count) := i;
dbms_output.put_line(v_tbl(v_count).ename||v_tbl(v_count).sal);
end loop;
end;

NESTED TABLE

Declare
type names_table is table of varchar2(10);
type grades is table of number;
names names_table;
marks grades;
v_count integer;
begin
names := names_table (‘Vin’, ‘Kum’, ‘Rag’, ‘Raj);
marks:=grades(80,90,100,15);
v_count := names.count;
for I in 1..v_count loop
dbms_output.put_line(names(i)||marks(i));
end loop;
end;

2)

 DECLARE
 CURSOR c_customers is
 SELECT  ename FROM emp;
 TYPE c_list IS TABLE of emp.ename%type;
 name_list c_list := c_list();
 counter integer :=0;
 BEGIN
 FOR i IN c_customers LOOP
 counter := counter +1;
 name_list.extend;
 name_list(counter)  := i.ename;            dbms_output.put_line('Customer('||counter||'):'||name_list(counter));
END LOOP;
END;
/

PL/SQL procedure successfully completed.

Customer(1):SMITH
Customer(2):ALLEN
Customer(3):WARD
Customer(4):JONES

VARRAY’s

declare
type t is varray(10) of varchar2(10);
v_tbl t;
begin
v_tbl := t(1101,1102);
for I in 1..v_tbl.last loop
dbms_output.put_line(v_tbl(i));
end loop;
end;

2) Creating family_names table and inserting data using varray’s

create type first_name_t is varray(2) of varchar2(50);

create type child_name is varray(1) of varchar2(50);

create table family_name(surname varchar2(50), parent_name first_name_t, children_names child_name);

declare
parents first_name_t := first_name_t();
children child_name := child_name();
begin
parents.extend(2)
parents(1) := 'Viny';
parents(2) := 'Kumar';
children.extend(1);
children(1) := 'Shree';
insert into family_name(surname, parent_name,children_names) values('NoSurName',parents,children);
end;

Output
Surname              PARENT_NAME                                        CHILDREN_NAMES
NoSurName FIRST_NAME_T('Viny', 'Kumar') CHILD_NAME('Shree')

3) Inserting multiple values  into one column

create type t_ph is varray(2) of number;
create table office (name varchar2(20), ph t_ph);
insert into office values('Vinay', t_ph(284234087, 456633453));

Output

SQL> select * from office;

NAME                 PH

Vinay                T_PH(284234087, 456633453)

Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose:

S.N.	Method Name & Purpose
1	EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2	COUNT Returns the number of elements that a collection currently contains.
3	LIMIT Checks the Maximum Size of a Collection.
4	FIRST Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5	LAST Returns the last (largest) index numbers in a collection that uses integer subscripts.
6	PRIOR(n) Returns the index number that precedes index n in a collection.
7	NEXT(n) Returns the index number that succeeds index n.
8	EXTEND Appends one null element to a collection.
9	EXTEND(n) Appends n null elements to a collection.
10	EXTEND(n,i) Appends n copies of the ith element to a collection.
11	TRIM Removes one element from the end of a collection.
12	TRIM(n) Removes n elements from the end of a collection.
13	DELETE Removes all elements from a collection, setting COUNT to 0.
14	DELETE(n) Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15	DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Exceptions

The following table provides the collection exceptions and when they are raised:

Collection Exception	Raised in Situations
COLLECTION_IS_NULL	You try to operate on an atomically null collection.
NO_DATA_FOUND	A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the allowed range.
VALUE_ERROR	A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.