Collections and Bulk Binds
Introduction to Object Types and Records
Collections
Associative Arrays (Index-by Tables)
Nested Tables
Varrays
Table Functions
When to use What
Using Collection Methods (Count, First, Last, etc)
Handle Collections (Check if null, Assign Elements, Compare Collections, Operations with Collections)
Collections and DB Tables
Moving from Cursor-Loops to Collections

Bulk Binding
Select with Record Bind
Insert with Record Bind
Update with Record Bind (set ROW)
Delete and Update using RETURNING
Bulk Binding in Native Dynamic SQL
Handling and Reporting Exceptions
Multi-Dimensional Arrays
Returning Result Sets
          Returning s Single Row
          Returning Cursor variables using REF CURSOR
          Returning Collections
         
Returning Collections with Dynamic SQL
          Return using Table Functions (pipelined)
Cursor Attributes
Improvements to Bulk Bind and Collections in 10g
Improvements in Oracle 11g


Introduction to Object Types and Records

A database object type is very similar to a CREATE TABLE statement, but it does not create a "container" for data. Rather it is a "template" for data. Example:

CREATE OR REPLACE TYPE "TYPE_VARCHAR2_ARRAY" as TABLE OF VARCHAR2(300);
/
CREATE OR REPLACE TYPE "TYPE_NUMBER_ARRAY" as TABLE OF NUMBER;
/

CREATE TYPE type_food AS OBJECT (
      name        VARCHAR2(100),
      food_group  VARCHAR2 (100),
      grown_in    VARCHAR2 (100)      );
/

DECLARE
   -- Create a new object with a constructor
   my_favorite_vegetable_rec
type_food := type_food('Brussel Sprouts', 'VEGETABLE', 'Farm,Greenhouse,Backyard');
BEGIN
   --Read an attribute value
   DBMS_OUTPUT.put_line (my_favorite_vegetable_rec.name);
   --Modify an attribute value
   my_favorite_vegetable_rec.food_group := 'SATISFACTION';
END;
/

A PL/SQL RECORD is a composite datatype, is composed of multiple pieces of information called fields. Records can be declared using relational tables or explicit cursors as "templates" with the %ROWTYPE declaration attribute. You can also declare records based on TYPES that you define yourself. The easiest way to define a record is by using the %ROWTYPE syntax in your declaration. For example, the statement:  bestseller books%ROWTYPE; creates a record that has a structure corresponding to the books table; for every column in the table, there is a field in the record with the same name and datatype as the column. The %ROWTYPE keyword is especially valuable because the declaration is guaranteed to match the corresponding schema-level template and is immune to schema-level changes in definition of the shape of the table. If we change the structure of the books table, all we have to do is recompile the above code and bestseller will take on the new structure of that table.
A second way to declare a record is to define your own RECORD TYPE:

DECLARE
   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   emp_tab EmpTabTyp;
   TYPE extra_book_info_t IS RECORD (

            title          books.title%TYPE,
            is_bestseller  BOOLEAN,
            reviewed_by    names_list );
   first_book extra_book_info_t;

Notice that the user-defined record datatype above includes a field (“title”) that is based on the column definition of a database table, a field (“is_bestseller”) based on a scalar data type (PL/SQL Boolean flag), and a collection (list of names of people who reviewed the book). Next, we can declare a record based on this type (you do not use %ROWTYPE in this case, because you are already referencing a type to perform the declaration). Once you have declared a record, you can then manipulate the data in these fields (or the record as a whole) as you can see below:

 DECLARE
   bestseller       books%ROWTYPE; --Based on a DB Table
   required_reading books%ROWTYPE;
BEGIN
   -- Modify a field value
   bestseller.title := 'ORACLE PL/SQL PROGRAMMING';
   -- Copy one record to another
   required_reading := bestseller;
END;

Note that in the above code we have used the structure of the books table to define our PL/SQL records, but the assignment to the title field did not in any way affect data inside that table.

You can also pass records as arguments to procedures and functions. This technique allows you to shrink down the size of a parameter list (pass a single record instead of a lengthy and cumbersome list of individual values). Here is an example of a function with a record in the parameter list:

CREATE OR REPLACE PROCEDURE calculate_royalties ( book_in IN books%ROWTYPE, quarter_end_in IN DATE )
IS ...


Another Full Example:

DECLARE
-- Declare a basic Table Type Array
TYPE a_char_data IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

-- Declare a complex record type with an embedded index by table. So now we have a ragged record type. A single record with a dimensional name column.
TYPE r_data IS RECORD (
ssn VARCHAR2(9) NOT NULL := -1,
name a_char_data, -- Notice the table_type used here
dob DATE );

-- Declare an "Associative Array (or index-by table)" using the complex record type. This creates an array of ragged records.
TYPE a_multi IS TABLE OF r_data INDEX BY BINARY_INTEGER;

-- Declare a variable using the complex array
v_data a_multi;

BEGIN
-- Populate the ssn and dob columns of the first record of the v_data variable.
v_data(1).ssn := '123456789';
v_data(1).dob := '01-JAN-1900';

-- Populate the first and second rows of the name table in the first row of the v_data variable.
v_data(1).name(1) := 'Lewis';
v_data(1).name(2) := 'Joe';

--Finally, display the ssn of the first row of the v_data variable and then looped through the name table of the first row of the v_data variable.
dbms_output.put_line(v_data(1).ssn);

-- Loop through the v_data(1).name table
FOR i IN v_data(1).name.FIRST..v_data(1).name.LAST LOOP
dbms_output.put_line(v_data(1).name(i));
END LOOP;
END;
/


Collections
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes.
Each element has a unique subscript that determines its position in the collection.
PL/SQL offers these collection types:
Nested tables and Varrays must have been initialized before you can use them.

The following scenarios generally indicate a need for collections:
IMPORTANT NOTES:
Memory for collections comes out of the PGA or Process Global Area, One per session, so a program using collections can consume a large amount of memory.
Use the NOCOPY hint to reduce overhead of passing collections in and out of program units.
Encapsulate or hide details of collection management.
Don't always fill collections sequentially. Think about how you need to manipulate the contents.
Try to read a row that doesn't exist, and Oracle raises NO_DATA_FOUND.


1-Associative Arrays (ALSO INDEX-BY_TABLES)


Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements.
It is like a simple version of a SQL table where you can retrieve values based on the primary key.
Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body. You don't need to initialize the Associative Array.

Declaration:
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

Probably the most familiar collection type is the associative arrays. The code block below is a typical use of an associative array:

DECLARE
  TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  my_num_array num_array;
  TYPE emp_array IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
  my_emp_array emp_array;
  my_emp_array2 emp_array;
  TYPE char_array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  my_chars
_array char_array;

BEGIN
  FOR i IN 1..100 LOOP
    my_num_array(i) := power(2, i);
  END LOOP;

 
my_chars_array(1) := 'Diego';
  my_chars_array(2) := 'Diego2';

  --In the example below, you store a single record in the index-by table, and its subscript is 7468 rather than 1
  SELECT * INTO
my_emp_array2(7468) FROM emp WHERE empno = 7468;

  FOR rec IN (select * from emp)
  LOOP
    my_emp_array(rec.empno) := rec;
  END LOOP;

  --I can also use BULK to Grab all the data without a Cursor
  SELECT * BULK COLLECT INTO my_emp_array
     FROM emp;
  DBMS_OUTPUT.PUT_LINE ('Fetched ' || TO_CHAR ( my_emp_array.COUNT ) ||' records from EMP TABLE.' );
END;
/


This first loop creates an array of unlimited size (up to your OS and DB version limitations) of NUMBER which is indexed by a BINARY_INTEGER datatype. The index is just the subscript and BINARY_INTEGER is just a numeric data type. An associative arrays does NOT have to be initialized and it can be sparse (non-consecutive numbers).

Let’s now look at a specific scenario in which a VARCHAR2 indexed array would be ideal. The requirement to look up a value via a unique non-numeric key is a generic computational problem. Suppose we have a set of English-French vocabulary pairs stored persistently in the most obvious way in a schema level table:

SELECT * FROM translations;
ENGLISH       FRENCH
------------- ----------
computer      ordinateur
tree          arbre
book          livre
cabbage       chou
country       pays

Our task is to allow lookup from French to English. What’s the most efficient way to implement the lookup procedure? We certainly have a wide set of choices, including:
Pure SQL approach: Simply query the English word for the French each time it’s needed. This will be performed with a simple select using on the where clause the english word.
• Full collection scan, a.k.a. “linear search”: Use the “traditional” INDEX BY BINARY_INTEGER collection to cache all the French-English pairs. Search the entire collection for a match each time a lookup is needed.
Hash-based indexing: Build our own VARCHAR2- based index using Oracle’s hashing algorithm.
• VARCHAR2-indexed associative array: Cache all French-English pairs using the French word as the key, allowing direct lookup of the English word, all within PL/SQL.
But by far the most optimized way would be to use Associative Array with the INDEX BY VARCHAR2 option.

Another Example Showing a Sparse Collection
DECLARE
   TYPE list_of_names_t IS TABLE OF VARCHAR2 (32767) INDEX BY PLS_INTEGER;
   happyfamily   list_of_names_t;
   l_row         PLS_INTEGER;
BEGIN
   happyfamily (2 ** 31 - 1) := 'Eli';
   happyfamily (-15070) := 'Steven';
   happyfamily (-90900) := 'Chris';
   happyfamily (88) := 'Veva';
   --
   l_row := happyfamily.FIRST;

   WHILE (l_row IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (   'Value at index ' || l_row || ' = '|| happyfamily (l_row));
      l_row := happyfamily.NEXT (l_row);
   END LOOP;

/*
   FOR l_row IN happyfamily.FIRST .. happyfamily.LAST
   LOOP
      DBMS_OUTPUT.put_line (happyfamily (l_row));
   END LOOP;
*/  
END;
/

Another Example showing string-indexed collections

DECLARE
     TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(30);
     phone_nos phone_no_tab;
BEGIN
     phone_nos('office') := '+44 (0) 117 942 2508';
     DBMS_output.put_line('phone_no(office) is '||phone_nos('office'));
END;
/

phone_no(office) is +44 (0) 117 942 2508



DECLARE

   SUBTYPE location_name_t IS VARCHAR2 (2000);
   TYPE population_type IS TABLE OF PLS_INTEGER INDEX BY location_name_t;

   country_population     population_type;
   continent_population   population_type;
   --
   howmany                PLS_INTEGER;
   l_limit                location_name_t;
BEGIN
   country_population ('Greenland') := 100000;
   country_population ('Iceland') := 750000;
   continent_population ('Australia') := 30000000;
   continent_population ('Antarctica') := 1000;
   continent_population ('antarctica') := 1001;
   --
   howmany := country_population.COUNT;
   DBMS_OUTPUT.put_line ('COUNT in
country_population = ' || howmany);
   l_limit := continent_population.FIRST;
   DBMS_OUTPUT.put_line ('FIRST row
in continent_population = ' || l_limit);
   DBMS_OUTPUT.put_line ('FIRST value
continent_population = ' || continent_population (l_limit));
   l_limit := continent_population.LAST;
   DBMS_OUTPUT.put_line ('LAST row in
continent_population = ' || l_limit);
   DBMS_OUTPUT.put_line ('LAST value
in continent_population = ' || continent_population (l_limit));

   /* THIS WILL NOT WORK

     FOR indx IN continent_population.FIRST .. continent_population.LAST LOOP
     NULL;
     END LOOP;
    */
END;
/


2-Nested Tables

A nested table is similar to an associative array in that there is no maximum size to the array; however prior to assign a new element to a nested table a PL/SQL program needs to explicitly extend the size before adding new elements.  A nested table is an object type and therefore needs to first be initialized with a constructor before being used.
Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts.
The size of a nested table can increase dynamically, i.e., nested tables are unbounded. Elements in a nested table initially have consecutive subscripts, but as elements are deleted, they can have non-consecutive subscripts. The range of values for nested table subscripts is 1..2147483647. To extend a nested table, the built-in procedure EXTEND must be used. To delete elements, the built-in procedure DELETE must be used.
An uninitialized nested table is atomically null, so the IS NULL comparison operator can be used to see if a nested table is null.

Declaration:
TYPE type_name IS TABLE OF element_type [NOT NULL];

In PL/SQL
Declare
   TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
 
In SQL
   CREATE [OR REPLACE] TYPE TYP_NT_NUM IS TABLE OF NUMBER ;


With nested tables declared within PL/SQL, element_type can be any PL/SQL datatype except : REF CURSOR

DECLARE
  TYPE nest_tab_t IS TABLE OF NUMBER;
  my_nt nest_tab_t := nest_tab_t();  --We need to initialize this type with a constructor
  TYPE emp_ntt IS TABLE OF emp%ROWTYPE;
  my_emp emp_ntt := emp_ntt();   --We need to initialize this type with a constructor

  TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
  Nt_tab TYP_NT_NUM ;
  TYPE CourseList IS TABLE OF VARCHAR2(16);
  my_courses CourseList;
  TYPE CourseList2 IS TABLE OF VARCHAR2(16);
  my_courses CourseList :=  CourseList('Art 1111', 'Hist 3100', 'Engl 2005');  --Here we initialize the Nested Table Defining its elements
BEGIN
  Nt_tab := TYP_NT_NUM( 5, 10, 15, 20 ) ;  
--Here we initialize the Nested Table Defining its elements inside the BEGIN
  my_courses := CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100');  --Here we initialize the Nested Table Defining its elements inside the BEGIN 

  FOR i IN 1..100 LOOP
    my_nt.EXTEND;
    my_nt(i) := i;
  END LOOP;
  FOR rec IN (select * from emp)
  LOOP
    my_emp.EXTEND;
    my_emp(my.emp.LAST) := rec;
  END LOOP;
END;
 
Note that the variable was initialized to an empty nested table using the constructor for its type.  Also, the example shows how the nested table EXTEND method is used to allocate a new element to the array so that it can be assigned to in the next statement.

Another Example using a DB Table containing a Nested DB Object
The following example illustrates how a simple nested table is created.

a) First, define a DB Object type as follows:
CREATE TYPE ELEMENTS AS OBJECT (
       ELEM_ID   NUMBER(6),
       PRICE     NUMBER(7,2));
/

b) Next, create a table type ELEMENTS_TAB which stores ELEMENTS objects:
CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;
/

c) Finally, create a database table STORAGE having type ELEMENTS_TAB as one of its columns:
CREATE TABLE STORAGE (
     SALESMAN  NUMBER(4),
     ELEM_ID   NUMBER(6),
     ORDERED   DATE,
     ITEMS     ELEMENTS_TAB)
     NESTED TABLE ITEMS STORE AS ITEMS_TAB;

This example demonstrates how to populate the STORAGE table with a single row:
INSERT INTO STORAGE VALUES (100, 123456, SYSDATE,
  ELEMENTS_TAB(ELEMENTS(175692,120.12),
               ELEMENTS(167295,130.45),
               ELEMENTS(127569,99.99)));

The following example demonstrates how to use the operator THE which is used in a SELECT statement to identify a nested table:
INSERT INTO
 THE
  (SELECT ITEMS FROM STORAGE WHERE ELEM_ID = 123456)
  VALUES (125762, 101.99);

The following example shows how to update the STORAGE table row where salesman column has value 100:
UPDATE STORAGE SET ITEMS = ELEMENTS_TAB(ELEMENTS(192512, 199.99)) WHERE SALESMAN = 100;

The following example shows how to retrieve data from a table to a nested Table:
DECLARE
   my_elements ELEMENTS;
BEGIN
   SELECT ITEMS INTO my_elements FROM STORAGE
      WHERE SALESMAN = 100;
END;
/

Within PL/SQL, you can manipulate the nested table by looping through its elements, using methods such as TRIM or EXTEND, and updating some or all of the elements. Afterwards, you can store the updated table in the database again.

New functions (Multi-set operations) on 10g for Nested Tables


3-Varrays
Varrays are ordered groups of items of type VARRAY.
With Varrays the number of elements in the array is variable up to the declared size. Arguably then, variable-sized arrays aren't that variable in size. Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
The maximum size of a varray needs to be specified in its type definition. The range of values for the index of a varray is from 1 to the maximum specified in its type definition. If no elements are in the array, then the array is atomically null, so the IS NULL comparison operator can be used to see if a varray is null. Varrays cannot be compared for equality or inequality.
The main use of a varray is to group small or uniform-sized collections of objects.
A varray can be assigned to another varray, provided the datatypes are the exact same type. For example, suppose you declared two PL/SQL types:
  TYPE My_Varray1 IS VARRAY(10) OF My_Type;
  TYPE My_Varray2 IS VARRAY(10) OF My_Type;
An object of type My_Varray1 can be assigned to another object of type My_Varray1 because they are the exact same type.
However, an object of type My_Varray2 cannot be assigned to an object of type My_Varray1 because they are not the exact same type, even though they have the same element type.

VARRAYs find their optimum application when data set, which has to be stored in order and is relatively small.

Declaration:
TYPE type_name IS VARRAY (size_limit) OF element_type [NOT NULL];
size_limit is a positive integer literal representing the maximum number of elements in the array.

Like nested tables, varrays can be both PL/SQL types and SQL types and therefore can take advantage of the many of the features listed above. The main differences with varrays in PL/SQL is that their maximum size must be specified when the type is declared. PL/SQL nested table or varray variables can be used to atomically insert values into tables that use them. Apart from this capability, varrays are of less interest than nested tables to the PL/SQL developer because they have the restriction of an upper bound and most anything one can do in code with a varray, one can do with a nested table.
Examples:

PL/SQL
declare
type v is varray(50) of number;
type Calendar is varray(366) OF DATE;

SQL
   CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20)

Initialization
Declare
TYPE TYP_V_DAY IS VARRAY(7) OF VARCHAR2(15) ;
v_tab TYP_V_DAY ;
TYPE Clientele IS VARRAY(100) OF Customer;
vips Clientele := Clientele(); -- initialize empty varray
Begin
v_tab := TYP_NT_NUM( ‘Sun’,’Mon’,’Tue’,’Wed’,’Thu’,’Fri’,’Sat’ ) ; --We can also
initialize inside the code
End ;


It is not required to initialize all the elements of a collection. You can either initialize no element. In this case, use an empty constructor.
v_tab := TYP_NT_NUM() ;

This collection is empty, which is different than a NULL collection (not initialized).



Another Example using a DB Table containing a Nested DB Object
The following example illustrates how a simple nested table is created.

a) First, define a object type ELEMENTS as follows:
CREATE or REPLACE TYPE MEDICINES AS OBJECT (
MED_ID NUMBER(6),
MED_NAME VARCHAR2(14),
MANF_DATE DATE);
/


b) Next, define a VARRAY type MEDICINE_ARR which stores MEDICINES objects:
CREATE TYPE MEDICINE_ARR AS VARRAY(40) OF MEDICINES;
/


Finally, create a relational table MED_STORE which has MEDICINE_ARR as a column type:
CREATE TABLE MED_STORE (
LOCATION VARCHAR2(15),
STORE_SIZE NUMBER(7),
EMPLOYEES NUMBER(6),
MED_ITEMS MEDICINE_ARR);

Each item in column MED_ITEMS is a varray that will several medicines for a given location.

The following example shows how to insert two rows into the MED_STORE table:
INSERT INTO MED_STORE VALUES ('BELMONT',1000,10, MEDICINE_ARR(MEDICINES(11111,'STOPACHE',SYSDATE)));
INSERT INTO MED_STORE VALUES ('REDWOOD CITY',700,5, MEDICINE_ARR(MEDICINES(12345,'STRESS_BUST',SYSDATE)));


The following example shows how to delete the second row we have inserted in example 6 above:
DELETE FROM MED_STORE WHERE LOCATION = 'REDWOOD CITY';

The following example shows how to update the MED_STORE table and add more medicines to the Belmont store:
UPDATE MED_STORE SET MED_ITEMS = MEDICINE_ARR (
MEDICINES(12346,'BUGKILL',SYSDATE),
MEDICINES(12347,'INHALER',SYSDATE),
MEDICINES(12348,'PAINKILL',SYSDATE));



Another Example:
Below SQL uses TABLE function to display the ORDERS table data in relational format.
To manipulate the individual elements of a collection with SQL, use the TABLE operator. The TABLE operator uses a subquery to extract the varray or nested table,
so that the INSERT, UPDATE, or DELETE statement applies to the nested table rather than the top-level table.
SELECT T1.LOCATION, T1.STORE_SIZE, T1.EMPLOYEES, T2.*
FROM MED_STORE T1, TABLE(T1.MANF_DATE) T2;

-- In the following example, you retrieve the title and cost of the Maintenance Department's fourth project from the varray column projects:

DECLARE
my_cost NUMBER(7,2);
my_title VARCHAR2(35);
BEGIN
SELECT cost, title INTO my_cost, my_title
FROM TABLE(SELECT projects FROM department
WHERE dept_id = 50)
WHERE project_no = 4;
...
END;
/



Example: Performing INSERT, UPDATE, and DELETE Operations on a Varray with SQL
Currently, you cannot reference the individual elements of a varray in an INSERT, UPDATE, or DELETE statement.
You must retrieve the entire varray, use PL/SQL procedural statements to add, delete, or update its elements, and then store the changed varray back in the database table.
In the following example, stored procedure ADD_PROJECT inserts a new project into a department's project list at a given position:
CREATE PROCEDURE add_project (
dept_no IN NUMBER,
new_project IN Project,
position IN NUMBER) AS
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE dept_no = dept_id FOR UPDATE OF projects;
my_projects.EXTEND; -- make room for new project
/* Move varray elements forward. */
FOR i IN REVERSE position..my_projects.LAST - 1 LOOP
my_projects(i + 1) := my_projects(i);
END LOOP;
my_projects(position) := new_project; -- add new project
UPDATE department SET projects = my_projects
WHERE dept_no = dept_id;
END add_project;

/


The following stored procedure updates a given project:
CREATE PROCEDURE update_project (
dept_no IN NUMBER,
proj_no IN NUMBER,
new_title IN VARCHAR2 DEFAULT NULL,
new_cost IN NUMBER DEFAULT NULL) AS
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE dept_no = dept_id FOR UPDATE OF projects;
/* Find project, update it, then exit loop immediately. */
FOR i IN my_projects.FIRST..my_projects.LAST LOOP
IF my_projects(i).project_no = proj_no THEN
IF new_title IS NOT NULL THEN
my_projects(i).title := new_title;
END IF;
IF new_cost IS NOT NULL THEN
my_projects(i).cost := new_cost;
END IF;
EXIT;
END IF;
END LOOP;
UPDATE department SET projects = my_projects
WHERE dept_no = dept_id;
END update_project;
/



Examples for nested tables and varrays

set serveroutput on
declare
  type nestab is table of number;
  someNumbers_nt   nestab;
  type varr is varray(50) of varchar2(30);
  someNames_varr   varr;
  i                binary_integer;
begin
  --Load some elements
  someNumbers_nt
:= nestab(10, 4, 6, 9, 2, 5);
  someNames_varr := varr('Fred','Joe','Caesar');
  i:=3;
  --Ask if Item on position 3 is a 6
  if
someNumbers_nt(i) = 6 then
    dbms_output.put_line ('someNumbers_nt(' || i || ')  = 6');
  else
    dbms_output.put_line ('someNumbers_nt(' || i || ') <> 6');
  end if;

  someNumbers_nt.delete(1);  --delete element 1
  someNumbers_nt.delete(4);  --delete element 4

  --More Ways to delete    -- If an element doesn't exist no exception rais
  -- someNumbers_nt.delete(20,30);  --delete elements 20 through 30
  -- someNumbers_nt.delete;         --delete entire PL/SQL Table

  --Step on 1st Position
  i :=
someNumbers_nt.first();
  while i is not null loop
    dbms_output.put_line ('Position = ' || i || ': ' || someNumbers_nt(i));
    i := someNumbers_nt.next(i);
  end loop;
end;
/

Output:
someNumbers_nt(3)  = 6
someNumbers_nt(3) <> 6
2: 4
3: 7
5: 2
6: 5

Differences between Nested Tables and Varrays


Example: Declaring Nested Tables, Varrays, and Associative Arrays
DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(20);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
   v1 := nested_type('Arbitrary','number','of','strings');
   v2 := varray_type(10, 20, 40, 80, 160); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements.
   v3(7) := 100; -- Subscripts can be any integer values.
   v4(42) := 'Cat'; -- Just start assigning to elements.
   v4(54) := 'Hat'; -- Subscripts can be any integer values.
   v5('Canada') := 'North America'; -- Just start assigning to elements.
   v5('Greece') := 'Europe';        -- Subscripts can be string values.
END;
/

Table Functions
To do this, the PL/SQL code executes a SQL statement passing the local nested table variable to the server.  There are two special functions necessary to achieve this functionality.  The TABLE function tells the server to bind over the values of the nested table, perform the requested SQL operation and return the results back as if the variable was a SQL table in the database.  The CAST function is an explicit directive to the server to map the variable to the SQL type that was defined globally in the previous step. With this capability, many new operations become possible..  For example, one can take a nested table of objects that have been created in code and send them to the server for ordering or aggregation.  Almost any SQL operation is possible. For example a nested table can be joined with other SQL tables in the database.  The next example shows a simple ordering of an array by the second field.
DECLARE
  eml_dmo_nt    email_demo_nt_t := email_demo_nt_t();  
BEGIN
  -- Some logic that populates the nested table …
  eml_dmo_nt.EXTEND(3);
  eml_dmo_nt(1) := email_demo_obj_t(45, 3, '23');
  eml_dmo_nt(2) := email_demo_obj_t(22, 3, '41');
  eml_dmo_nt(3) := email_demo_obj_t(18, 7, 'over_100k');
 
  -- Process the data in assending order of email id.
  FOR r IN (SELECT * FROM TABLE(CAST(eml_dmo_nt AS email_demo_nt_t))
               ORDER BY 1)

  LOOP
    dbms_output.put_line(r.email_id || ' ' ||  r.demo_id);
  END LOOP;
END;
 

When to use what
 
If you're new to PL/SQL collections, you may have a fair understanding of their mechanics by this point, but are uncertain when to use a particular type.The following table summarizes each collection's capabilities.

Has Ability To
Varray
Nested
Associative Array
be indexed by non-integer
No
No
Yes
preserve element order
Yes
No
No
be stored in database
Yes
Yes
No
have elements selected indidually in database
Yes
Yes
--
have elements updated indidually in database
Yes
Yes
--

The following guidelines will help you choose an associative array, nested table, or VARRAY:

The information in Table 1 will also help you make your choice.

Table 1: Associative array, nested table, or VARRAY
Characteristic Associative Array Nested Table VARRAY
Dimensionality Single Single Single
Use inside the FROM clause of query with TABLE operator No Yes Yes
Usable as column datatype in a table No Yes; data stored "out of line" (in separate table) Yes; data stored "in line" (in same table)
Uninitialized state Empty (cannot be null); elements undefined Atomically null; illegal to reference elements Atomically null; illegal to reference elements
Initialization Automatic, when declared Via constructor, fetch, assignment Via constructor, fetch, assignment
In PL/SQL, elements referenced via BINARY_INTEGER (-2,147,483,647 .. 2,147, 483,647) VARCHAR2 (Oracle9i Database Release 2 and above) Positive integer between 1 and 2,147,483,647 Positive integer between 1 and 2,147,483,647
Sparse Yes Initially, no; after deletions, yes No
Bounded No Can be extended Yes
Can assign value to any EXTEND element at any time Yes No; may need to use EXTEND first No; may need to use past upper bound first and cannot use EXTEND
Means of extending Assign value to element with a new subscript Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum Use EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality No Yes, in Oracle Database 10g No
Can be manipulated with set operators No Yes, in Oracle Database 10g No
Retains ordering and subscripts when stored in and retrieved from database N/A No Yes


In addition, the following bullet points can be referred to when deciding what collection best suits a particular solution.

Varray

Nested Table

Associative Array


Choosing Between Nested Tables and Associative Arrays
Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.
Nested tables can be stored in a database column, but associative arrays cannot. Nested tables are appropriate for important data relationships that must be stored persistently.
Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers when appropriate.
PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to use anonymous PL/SQL blocks to bulk-bind input and output host arrays to associative arrays.

Choosing Between Nested Tables and Varrays
Varrays are a good choice when the number of elements is known in advance, and when the elements are usually all accessed in sequence. When stored in the database, varrays retain their ordering and subscripts.
Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.
Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in astore table, a system-generated database table associated with the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection. You cannot rely on the order and subscripts of a nested table remaining stable as the table is stored and retrieved, because the order and subscripts are not preserved when a nested table is stored in the database.



Using Collection Methods

The following collection methods help generalize code, make collections easier to use, and make your applications easier to maintain:

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The syntax follows: collection_name.method_name[(parameters)]
Collection methods cannot be called from SQL statements. EXTEND and TRIM cannot be used with associative arrays.
Only EXISTS can be used on a null collection. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.
Some Examples:

EXISTS(index)
Returns TRUE if the index element exists in the collection, else it returns FALSE. Use this method to be sure you are doing a valid operation on the collection.
If my_collection.EXISTS(10) Then
   My_collection.DELETE(10) ;
End if ;

COUNT
Returns the number of elements in a collection.
Declare
   TYPE    TYP_TAB IS TABLE OF NUMBER;
   my_tab  TYP_TAB := TYP_TAB( 1, 2, 3, 4, 5 );
Begin
   Dbms_output.Put_line( 'COUNT = ' || To_Char( my_tab.COUNT ) ) ;
   my_tab.DELETE(2) ;
   Dbms_output.Put_line( 'COUNT = ' || To_Char( my_tab.COUNT ) ) ;
End ;   
/
COUNT = 5
COUNT = 4

  
LIMIT
For nested tables and associative arrays, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition, and can change later with the TRIM and EXTEND methods).
Declare
  TYPE TYP_ARRAY IS ARRAY(30) OF NUMBER ;
  my_array  TYP_ARRAY := TYP_ARRAY( 1, 2, 3 ) ;
Begin
  dbms_output.put_line( 'Max array size is ' || my_array.LIMIT ) ;
End;
/
Max array size is 30

 
FIRST and LAST
FIRST and LAST return the first and last (smallest and largest) index numbers in a collection. For an associative array with VARCHAR2 key values, the lowest and highest key values are returned; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
If the collection is empty, FIRST and LAST return NULL.
For varrays, FIRST always returns 1 and LAST always equals COUNT.
For nested tables, FIRST normally returns 1. But, if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1. Also for nested tables, LAST normally equals COUNT. But, if you delete elements from the middle of a nested table, LAST becomes larger than COUNT.
When scanning elements, FIRST and LAST ignore deleted elements.

Declare
   TYPE    TYP_TAB IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(1);
   my_tab  TYP_TAB;
   TYPE    TYP_TAB IS TABLE OF NUMBER;
   my_tab  TYP_TAB := TYP_TAB( 1, 2, 3, 4, 5 );
Begin

   For i in 65 .. 69 Loop
      my_tab( Chr(i) ) := i ;
   End loop ;
   Dbms_Output.Put_Line( 'First= ' || my_tab.FIRST || '  Last= ' || my_tab.LAST ) ;

   For i IN my_tab.FIRST .. my_tab.LAST Loop

      Dbms_output.Put_line( 'my_tab(' || Ltrim(To_Char(i)) || ') = ' || To_Char( my_tab(i) ) ) ;
   End loop ;
End ;
/
First= A  Last= E
my_tab(1) = 1
my_tab(2) = 2
my_tab(3) = 3
my_tab(4) = 4
my_tab(5) = 5


PRIOR(index) and NEXT(index)
Returns the previous or next index number that precedes index n. If n has no predecessor,PRIOR(n) returns NULL. If n has no successor, NEXT(n) returns NULL.

For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop. This is especially true for associative arrays, where the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').
PRIOR and NEXT do not wrap from one end of a collection to the other. For example, the following statement assigns NULL to n because the first element in a collection has no predecessor:
n := courses.PRIOR(courses.FIRST);  -- assigns NULL to n

PRIOR is the inverse of NEXT. For instance, if element i exists, the following statement assigns element i to itself:
projects(i) := projects.PRIOR(projects.NEXT(i));

You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. In the following example, you use NEXT to traverse a nested table from which some elements have been deleted:
i := courses.FIRST;  -- get subscript of first element
WHILE i IS NOT NULL LOOP
   -- do something with courses(i)
   i := courses.NEXT(i);  -- get subscript of next element
END LOOP;

When traversing elements, PRIOR and NEXT ignore deleted elements.

Declare
   TYPE    TYP_TAB IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(1) ;
   my_tab  TYP_TAB ;
   c       Varchar2(1) ;
Begin
   For i in 65 .. 69 Loop
      my_tab( Chr(i) ) := i ;
   End loop ;
   c := my_tab.FIRST ; -- first element
   Loop
      Dbms_Output.Put_Line( 'my_tab(' || c || ') = ' || my_tab(c) ) ;
      c := my_tab.NEXT(c) ; -- get the successor element
      Exit When c IS NULL ; -- end of collection
   End loop ;
End ;
/
my_tab(A) = 65
my_tab(B) = 66
my_tab(C) = 67
my_tab(D) = 68
my_tab(E) = 69


Use the PRIOR() or NEXT() method to be sure that you do not access an invalid element:
Declare
   TYPE    TYP_TAB IS TABLE OF PLS_INTEGER ;
   my_tab  TYP_TAB := TYP_TAB( 1, 2, 3, 4, 5 );
Begin 
   my_tab.DELETE(2) ;   -- delete an element of the collection
   For i in my_tab.FIRST .. my_tab.LAST Loop
      Dbms_Output.Put_Line( 'my_tab(' || Ltrim(To_char(i)) || ') = ' || my_tab(i) ) ;
   End loop ;
End ;
/
my_tab(1) = 1
Declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 7
 
In this example, we get an error because one element of the collection was deleted.
One solution is to use the PRIOR()/NEXT() method:
Declare
   TYPE    TYP_TAB IS TABLE OF PLS_INTEGER ;
   my_tab  TYP_TAB := TYP_TAB( 1, 2, 3, 4, 5 );
   v       Pls_Integer ;
Begin 
   my_tab.DELETE(2) ;
   v := my_tab.first ;
   Loop
      Dbms_Output.Put_Line( 'my_tab(' || Ltrim(To_char(v)) || ') = ' || my_tab(v) ) ;
      v := my_tab.NEXT(v) ; -- get the next valid subscript
      Exit When v IS NULL ;
   End loop ;
End ;
/
my_tab(1) = 1
my_tab(3) = 3
my_tab(4) = 4
my_tab(5) = 5
 
Another solution is to test if the index exists before use it:
Declare
   TYPE    TYP_TAB IS TABLE OF PLS_INTEGER ;
   my_tab  TYP_TAB := TYP_TAB( 1, 2, 3, 4, 5 );
Begin 
   my_tab.DELETE(2) ;
   For i IN my_tab.FIRST .. my_tab.LAST Loop
      If my_tab.EXISTS(i) Then
         Dbms_Output.Put_Line( 'my_tab(' || Ltrim(To_char(i)) || ') = ' || my_tab(i) ) ;
      End if ;
   End loop ;
End ;
/
my_tab(1) = 1
my_tab(3) = 3
my_tab(4) = 4
my_tab(5) = 5


 
EXTEND[(n[,i])]
Used to extend a collection (add new elements) of a nested table or varray. You cannot use EXTEND with Associative Arrays. The procedure has 3 forms:

You cannot use EXTEND to initialize an atomically null collection. Also, if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.
EXTEND operates on the internal size of a collection, which includes any deleted elements. So, if EXTEND encounters deleted elements, it includes them in its tally. PL/SQL keeps placeholders for deleted elements so that you can replace them if you wish.
Consider the following example:
DECLARE
   TYPE CourseList IS TABLE OF VARCHAR2(10);
   courses CourseList;
BEGIN
   courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
   courses.DELETE(3);  -- delete element 3
   /* PL/SQL keeps a placeholder for element 3. So, the next statement appends element 4, not element 3. */
   courses.EXTEND;  -- append one null element
   /* Now element 4 exists, so the next statement does not raise SUBSCRIPT_BEYOND_COUNT. */
   courses(4) := 'Engl 2005';

When it includes deleted elements, the internal size of a nested table differs from the values returned by COUNT and LAST. For instance, if you initialize a nested table with five elements, then delete elements 2 and 5, the internal size is 5, COUNT returns 3, and LAST returns 4. All deleted elements (whether leading, in the middle, or trailing) are treated alike

Declare
   TYPE TYP_NES_TAB is table of Varchar2(20) ;
   tab1 TYP_NES_TAB ;
   i    Pls_Integer ;
  
   Procedure Print( i in Pls_Integer ) IS

   BEGIN
      Dbms_Output.Put_Line( 'tab1(' || ltrim(to_char(i)) ||') = ' || tab1(i) ) ;
   END ;

  
   Procedure PrintAll IS

   Begin
     Dbms_Output.Put_Line( '* Print all collection *' ) ;
     For i IN tab1.FIRST..tab1.LAST Loop
        If tab1.EXISTS(i) Then
           Dbms_Output.Put_Line( 'tab1(' || ltrim(to_char(i)) ||') = ' || tab1(i) ) ;
        End if ;
     End loop ;
   End ;

Begin
   tab1 := TYP_NES_TAB('One') ;
   i := tab1.COUNT ;
   Dbms_Output.Put_Line( 'tab1.COUNT = ' || i ) ;
   Print(i) ;
   -- the following line raise an error because the second index does not exists in the collection --
   -- tab1(2) := 'Two' ;
   -- Add one empty element --
   tab1.EXTEND ;
   i := tab1.COUNT ;
   tab1(i) := 'Two' ;
   Printall ;

  
   -- Add two empty elements --

   tab1.EXTEND(2) ;
   i := i + 1 ;
   tab1(i) := 'Three' ;
   i := i + 1 ;
   tab1(i) := 'Four' ;
   Printall ;

  
   -- Add three elements with the same value as element 4 --

   tab1.EXTEND(3,1) ;
   i := i + 3 ;
   Printall ;

End;
/
tab1.COUNT = 1
tab1(1) = One
* Print all collection *
tab1(1) = One
tab1(2) = Two
* Print all collection *
tab1(1) = One
tab1(2) = Two
tab1(3) = Three
tab1(4) = Four
* Print all collection *
tab1(1) = One
tab1(2) = Two
tab1(3) = Three
tab1(4) = Four
tab1(5) = One
tab1(6) = One
tab1(7) = One


TRIM[(n)]
Used to decrease the size of a collection
· TRIM removes one element from the end of a collection.
· TRIM(n) removes n elements from the end of a collection.
Declare
   TYPE TYP_TAB is table of varchar2(100) ;
   tab  TYP_TAB ;
Begin
   tab := TYP_TAB( 'One','Two','Three' ) ;
   For i in tab.first..tab.last Loop
     dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
   End loop ;
   -- add 3 element with second element value --
   dbms_output.put_line( '* add 3 elements *' ) ;
   tab.EXTEND(3,2) ;
   For i in tab.first..tab.last Loop
     dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
   End loop ;
   -- suppress the last element --
   dbms_output.put_line( '* suppress the last element *' ) ;
   tab.TRIM ;
   For i in tab.first..tab.last Loop
     dbms_output.put_line( 'tab(' || ltrim( to_char( i ) ) || ') = ' || tab(i) ) ;
   End loop ;
End;
/
tab(1) = One
tab(2) = Two
tab(3) = Three
* add 3 elements *
tab(1) = One
tab(2) = Two
tab(3) = Three
tab(4) = Two
tab(5) = Two
tab(6) = Two
* suppress the last element *
tab(1) = One
tab(2) = Two
tab(3) = Three
tab(4) = Two
tab(5) = Two
 If you try to suppress more elements than the collection contents, you get a SUBSCRIPT_BEYOND_COUNT exception.


DELETE[(n[,m])]
· DELETE removes all elements from a collection.
· 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.
· DELETE(n,m) 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(n,m) does nothing

Caution :
LAST returns the greatest subscript of a collection and COUNT returns the number of elements of a collection. If you delete some elements, LAST != COUNT.

Suppression of the second element
Declare
  TYPE TYP_TAB is table of varchar2(100) ;
  tab  TYP_TAB ;
Begin
  tab := TYP_TAB( 'One','Two','Three' ) ;
  dbms_output.put_line( 'Suppression of the 2nd element' ) ;
  tab.DELETE(2) ;
  dbms_output.put_line( 'tab.COUNT = ' || tab.COUNT) ;
  dbms_output.put_line( 'tab.LAST  = ' || tab.LAST) ;
  For i IN tab.FIRST .. tab.LAST Loop
    If tab.EXISTS(i) Then
       dbms_output.put_line( tab(i) ) ;
    End if ;
  End loop ;
End;
/
Suppression of the 2nd element
tab.COUNT = 2
tab.LAST  = 3
One
Three

Caution:
For Varrays, you can suppress only the last element. If the element does not exists, no exception is raised.



Handle Collections

Checking if a Collection Is Null
Nested tables and varrays can be atomically null, so they can be tested for nullity:
DECLARE
   TYPE Staff IS TABLE OF Employee;
   members Staff;
BEGIN
  -- Condition yields TRUE because we haven't used a constructor.
   IF members IS NULL THEN ...
END;

While the collection is not initialized (Nested tables and Varrays), it is not possible to manipulate it. You can test if a collection is initialized:
Declare
  TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
  tab1 TYP_VAR_TAB ; -- declared but not initialized
Begin
  If Tab1 IS NULL Then
     -- NULL collection, have to initialize it --
     Tab1 := TYP_VAR_TAB('','','','','','','','','','');
  End if ;
  -- Now, we can handle the collection --
End ;

Accesing Specific Element
To access an element of a collection, we need to use a subscript value that indicates the unique element of the collection. The subscript is of type integer or varchar2.
Declare
   Type    TYPE_TAB_EMP IS TABLE OF Varchar2(60) INDEX BY BINARY_INTEGER ;
   emp_tab TYPE_TAB_EMP ;
   i       pls_integer ;
Begin
   For i in 0..10 Loop
     emp_tab( i+1 ) := 'Emp ' || ltrim( to_char( i ) ) ;
   End loop ;
End ;
 
Declare
  Type    TYPE_TAB_DAYS IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20) ;
  day_tab TYPE_TAB_DAYS ;
Begin
   day_tab( 'Monday' )    := 10 ;
   day_tab( 'Tuesday' )   := 20 ;
   day_tab( 'Wednesday' ) := 30 ;
End ;
 
Assign values between collections
It is possible to assign values of a collection to another collection if they are of the same type.
Declare
  Type TYPE_TAB_EMP  IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ;
  Type TYPE_TAB_EMP2 IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ; 
  tab1 TYPE_TAB_EMP  := TYPE_TAB_EMP( ... );
  tab2 TYPE_TAB_EMP  := TYPE_TAB_EMP( ... );
  tab3 TYPE_TAB_EMP2 := TYPE_TAB_EMP2( ... ); 
Begin
    tab2 := tab1 ; -- OK
    tab3 := tab1 ; -- Error : types not similar
    ...           
End ;
 

Comparing collections
Until the 10g release, collections cannot be directly compared for equality or inequality. The 10g release allows doing some comparisons between collections:
You can compare collections of same type to verify if they are equals or not equals
DECLARE
   TYPE      Colors IS TABLE OF VARCHAR2(64);
   primaries     Colors := Colors('Blue','Green','Red');
   rgb           Colors := Colors('Red','Green','Blue');
   traffic_light Colors := Colors('Red','Green','Amber');
BEGIN
    -- We can use = or !=, but not < or >.
    -- 2 collections are equal even if the membersare not in the same order.
   IF primaries = rgb THEN
      dbms_output.put_line('OK, PRIMARIES and RGB have same members.');
   END IF;

   IF rgb != traffic_light THEN
      dbms_output.put_line('RGB and TRAFFIC_LIGHT have different members');
   END IF;
END;
/
OK, PRIMARIES and RGB have same members.
RGB and TRAFFIC_LIGHT have different members
 
Another Example:
DECLARE
   TYPE clientele IS TABLE OF VARCHAR2 (64);

   client_list_12    clientele := clientele ('Customer 1', 'Customer 2');
   client_list_13    clientele := clientele ('Customer 1', 'Customer 3');
   client_list_31    clientele := clientele ('Customer 3', 'Customer 1');
   --
   client_list_133    clientele := clientele ('Customer 1', 'Customer 3', 'Customer 3');
   --
   client_list_13n   clientele := clientele ('Customer 1', 'Customer 3', NULL);
   client_list_3n1   clientele := clientele ('Customer 3', NULL, 'Customer 1');

   PROCEDURE compare_clients (title_in IN VARCHAR2, clients1_in IN clientele, clients2_in IN clientele)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (title_in);

      IF clients1_in = clients2_in
      THEN
         DBMS_OUTPUT.put_line ('   Client list 1 = Client list 2');
      ELSIF clients1_in != clients2_in
      THEN
         DBMS_OUTPUT.put_line ('   Client list 1 != Client list 2');
      ELSIF (clients1_in = clients2_in) IS NULL
      THEN
         DBMS_OUTPUT.put_line ('   NULL result');
      END IF;
   END compare_clients;
  
BEGIN
   compare_clients ('1,2 compared to 1,3', client_list_12, client_list_13);
   --
   compare_clients ('1,3 compared to 3,1', client_list_13, client_list_31);
   --
   compare_clients ('1,3,3 compared to 3,1', client_list_133, client_list_31);
   --
   compare_clients ('1,3,NULL compared to 3,NULL,1', client_list_13n, client_list_3n1);
   compare_clients ('1,3,NULL compared to 1,3,NULL', client_list_13n, client_list_13n);
END;
/


Operations with Collections
You can also apply some operators on the collections:

Operator

Description

submultiset

Tests whether a given nested table is a subset of another nested table.

multiset union

Returns a nested table that contains the values of two inputted nested tables. The all or distinct option may be used, as required, to allow for or eliminate duplicates, including NULL values.

multiset intersect

Returns a nested table that contains values that are common to the two nested tables passed in through the input operators. The all or distinct option may be used, as required, to allow for or eliminate duplicates, including NULL values.

multiset except

This operator takes the names of two nested tables as parameters. When executed it will return a nested table which contains elements listed in the first parameter of the function, that do not exist in the parameter. The all or distinct option may be used, as required, to allow for or eliminate duplicates, including NULL values.

cardinality

Returns the number of elements contained within a given nested table.

member of

Makes it possible to test a given value and determine whether it is a member of a nested table. The result is a Boolean value.

set

Used to convert a nested table into a set of elements that are distinct. This set is returned in the form of a nested table.

is a set

Used to determine whether a nested table is made up of distinct elements. If a duplicate value is contained in the nested table, then the operator returns FALSE; otherwise, TRUE is returned.

is empty

Used to determine whether a given nested table is empty.

collect

Creates a nested table from a set of elements.

powermultiset

Used on nested tables to generate multiple sets of nested table entries from a given nested table.

powermultiset_by_cardinality

Used on nested tables to generate multiple sets of nested table entries based on a specified cardinality. This restricts the depth of the resulting nested table output.

 

DECLARE
  TYPE nested_typ IS TABLE OF NUMBER;
  nt1 nested_typ := nested_typ(1,2,3);
  nt2 nested_typ := nested_typ(3,2,1);
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4);
  reponse BOOLEAN;
  combien NUMBER;
  PROCEDURE verif(test BOOLEAN DEFAULT NULL, label IN VARCHAR2 DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS
  BEGIN
     IF test IS NOT NULL THEN
        dbms_output.put_line(label || ' -> ' || CASE test WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
     END IF;
     IF quantity IS NOT NULL THEN
        dbms_output.put_line(quantity);
     END IF;
  END;
BEGIN
  reponse := nt1 IN (nt2,nt3,nt4); -- true, nt1 correspond to nt2
  verif(test => reponse, label => 'nt1 IN (nt2,nt3,nt4)');
  reponse := nt1 SUBMULTISET OF nt3; -- true, all elements correspond
  verif(test => reponse, label => 'nt1 SUBMULTISET OF nt3');
  reponse := nt1 NOT SUBMULTISET OF nt4; -- true
  verif(test => reponse, label => 'nt1 NOT SUBMULTISET OF nt4');
 
  combien := CARDINALITY(nt3); -- number of elements of nt3
  verif(quantity => combien);
  combien := CARDINALITY(SET(nt3)); -- number of distinct element, operation performed by the SET statement, is like a distinct
  verif(quantity => combien);
 
  reponse := 4 MEMBER OF nt1; -- false, no corresponding element
  verif(test => reponse, label => '4 MEMBER OF nt1');
  reponse := nt3 IS A SET; -- false, nt3 have duplicated elements
  verif(test => reponse, label => 'nt3 IS A SET' );
  reponse := nt3 IS NOT A SET; -- true, nt3 have diplicated elements
  verif(test => reponse, label => 'nt3 IS NOT A SET' );
  reponse := nt1 IS EMPTY; -- false, nt1 have elements
  verif(test => reponse, label => 'nt1 IS EMPTY' );
END;
/
 
nt1 IN (nt2,nt3,nt4) -> True
nt1 SUBMULTISET OF nt3 -> True
nt1 NOT SUBMULTISET OF nt4 -> True
4
3
4 MEMBER OF nt1 -> False
nt3 IS A SET -> False
nt3 IS NOT A SET -> True
nt1 IS EMPTY -> False



Collections and DB Tables
Nested tables and Varrays can be stored in a database column of relational or object table.
To manipulate collection from SQL, you have to create the types in the database with the CREATE TYPE statement.
 
Nested tables
CREATE [OR REPLACE] TYPE [schema. .] type_name { IS | AS } TABLE OF datatype;
 
Varrays
CREATE [OR REPLACE] TYPE [schema. .] type_name  { IS | AS } { VARRAY | VARYING ARRAY } ( limit ) OF datatype;
 
One or several collections can be stored in a database column.
Let’s see an example with a relational table. You want to make a table that store the invoices and the currents invoice lines of the company. You need to define the invoice line type as following:
-- type of invoice line --
CREATE TYPE TYP_LIG_ENV AS OBJECT (
  lig_num    Integer,
  lig_code   Varchar2(20),
  lig_Pht    Number(6,2),
  lig_Tva    Number(3,1),
  ligQty     Integer
);

 
-- nested table of invoice lines --
CREATE TYPE TYP_TAB_LIG_ENV AS TABLE OF TYP_LIG_ENV ;
 
Then create the invoice table as following:
-- table of invoices --
CREATE TABLE INVOICE (
  inv_num     Number(9),
  inv_numcli  Number(6),
  inv_date    Date,
  inv_line    TYP_TAB_LIG_ENV ) –- lines collection
  NESTED TABLE inv_line STORE AS inv_line_table ;



You can query the USER_TYPES view to get information on the types created in the database.
select type_name, typecode, attributes from user_types;
TYPE_NAME                      TYPECODE                       ATTRIBUTES
------------------------------ ------------------------------ ----------
TYP_LIG_ENV                    OBJECT                                  5
TYP_TAB_LIG_ENV                COLLECTION                              0

 
You can query the USER_COLL_TYPES view to get information on the collections created in the database.
select type_name, coll_type, elem_type_owner, elem_type_name from user_coll_types;
TYPE_NAME                 COLL_TYPE              ELEM_TYPE_OWNER           ELEM_TYPE_NAME
------------------------- ---------------------- ------------------------- -------
TYP_TAB_LIG_ENV           TABLE                  TEST                      TYP_LIG_ENV

 
You can query the USER_TYPE_ATTRS view to get information on the collection attributes.
select type_name, attr_name, attr_type_name, length, precision, scale, attr_no from user_type_attrs;
TYPE_NAME       ATTR_NAME       ATTR_TYPE_     LENGTH  PRECISION      SCALE    ATTR_NO
--------------- --------------- ---------- ---------- ---------- ---------- ----------
TYP_LIG_ENV     LIG_NUM         INTEGER                                              1
TYP_LIG_ENV     LIG_CODE        VARCHAR2           20                                2
TYP_LIG_ENV     LIG_PHT         NUMBER                         6          2          3
TYP_LIG_ENV     LIG_TVA         NUMBER                         3          1          4
TYP_LIG_ENV     LIGQTY          INTEGER                                              5

 
1 Insertion
Add a line in the INVOICE table
Use the INSERT statement with all the constructors needed for the collection
INSERT INTO INVOICE VALUES
(1 ,1000 ,SYSDATE
  , TYP_TAB_LIG_ENV  -- Table of objects constructor
    ( TYP_LIG_ENV( 1 ,'COD_01', 1000, 5.0, 1 ) -– object constructor
    )
);


Add a line to the collection
Use the INSERT INTO TABLE statement
INSERT INTO TABLE
  ( SELECT the_collection FROM the_table WHERE ... )
 
The sub query must return a single collection row.
INSERT INTO TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) VALUES( TYP_LIG_ENV( 2 ,'COD_02', 50, 5.0, 10 ) );
 
Multiple inserts
You can add more than one element in a collection by using the SELECT statement instead of the VALUES keyword.
INSERT INTO TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1)
SELECT nt.* FROM TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt;



2 Update
 
2.1 Nested table
Use the UPDATE TABLE statement
UPDATE TABLE
  ( SELECT the_collection FROM the_table WHERE ... ) alias
SET
  Alias.col_name = ...
WHERE ...

 
The sub query must return a single collection row.
Update a single row of the collection
UPDATE TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  SET    nt.ligqty = 10
  WHERE  nt.lig_num = 1;

 
Update all the rows of the collection
UPDATE TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
SET nt.lig_pht = nt.lig_pht * .1;

 
2.2 Varray
It is not possible to update one element of a VARRAY collection with SQL.
You cannot use the TABLE keyword for this purpose (because Varrays are not stored in particular table like Nested tables).

So, a single VARRAY element of a collection must be updated within a PL/SQL block:
-- varray of invoice lines --
CREATE TYPE TYP_VAR_LIG_ENV AS VARRAY(5) OF TYP_LIG_ENV ;
 
-- table of invoices with varray --
CREATE TABLE INVOICE_V (
  inv_num     Number(9),
  inv_numcli  Number(6),
  inv_date    Date,
  inv_line    TYP_VAR_LIG_ENV ) ;

 
-- insert a row --
Insert into INVOICE_V
Values ( 1, 1000, SYSDATE,
  TYP_VAR_LIG_ENV
  (
     TYP_LIG_ENV( 1, 'COD_01', 1000, 5, 1 ),
     TYP_LIG_ENV( 2, 'COD_02',  500, 5, 10 ),
     TYP_LIG_ENV( 3, 'COD_03',   10, 5, 100 )        
  )
) ;


-- Query the varray collection --
Declare
   v_table   TYP_VAR_LIG_ENV ;
   LC$Head   Varchar2(200) ;
   LC$Lig    Varchar2(200) ;
Begin
   LC$Head := 'Num Code       Pht        Tva        Qty' ;
   Select inv_line Into v_table
      From INVOICE_V
      Where inv_num = 1
      For Update of inv_line ;

   dbms_output.put_line ( LC$Head ) ;
   For i IN v_table.FIRST .. v_table.LAST Loop
     LC$Lig := Rpad(To_char( v_table(i).lig_num ),3) || ' '
         || Rpad(v_table(i).lig_code, 10) || ' '
         || Rpad(v_table(i).lig_pht,10) || ' '
         || Rpad(v_table(i).lig_tva,10) || ' '
         || v_table(i).ligqty ;       
     dbms_output.put_line( LC$Lig ) ;      
   End loop ;
End ;
/

Num Code       Pht        Tva        Qty
1   COD_01     1000       5          1
2   COD_02     500        5          10
3   COD_03     10         5          100

 
 
Update the second line of the varray to change the quantity
Declare
   v_table   TYP_VAR_LIG_ENV ;
Begin
   Select inv_line Into v_table
      From INVOICE_V
      Where inv_num = 1
      For Update of inv_line ;
   v_table(2).ligqty := 2 ; -- update the second element
   Update INVOICE_V Set inv_line = v_table Where inv_num = 1 ;
End ;
/


Display the new varray:
-- Query the varray collection --
Declare
   v_table   TYP_VAR_LIG_ENV ;
   LC$Head   Varchar2(200) ;
   LC$Lig    Varchar2(200) ;
Begin
   LC$Head := 'Num Code       Pht        Tva        Qty' ;
   Select inv_line Into v_table
      From INVOICE_V
      Where inv_num = 1
      For Update of inv_line ;

   dbms_output.put_line ( LC$Head ) ;
   For i IN v_table.FIRST .. v_table.LAST Loop
     LC$Lig := Rpad(To_char( v_table(i).lig_num ),3) || ' '
         || Rpad(v_table(i).lig_code, 10) || ' '
         || Rpad(v_table(i).lig_pht,10) || ' '
         || Rpad(v_table(i).lig_tva,10) || ' '
         || v_table(i).ligqty ;       
     dbms_output.put_line( LC$Lig ) ;      
   End loop ;
End ;
/

Num Code       Pht        Tva        Qty
1   COD_01     1000       5          1
2   COD_02     500        5          2
3   COD_03     10         5          100

 

3 Delete
 
3.1 Nested table
Use the DELETE FROM TABLE statement.
DELETE FROM TABLE
  ( SELECT the_collection FROM the_table WHERE ... ) alias
WHERE alias.col_name = ..
.
 
Delete a single collection row
DELETE FROM TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  WHERE nt.lig_num = 2;
 
Delete all the collection rows
DELETE FROM TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt;
 
Use of a PL/SQL record to handle the whole structure
Declare
  TYPE TYP_REC IS RECORD
  (
    inv_num     INVOICE.inv_num%Type,
    inv_numcli  INVOICE.inv_numcli%Type,
    inv_date    INVOICE.inv_date%Type,
    inv_line    INVOICE.inv_line%Type   –- collection line
  );
  rec_inv  TYP_REC ;
  Cursor C_INV IS Select * From INVOICE ;
Begin
   Open C_INV ;
   Loop
     Fetch C_INV into rec_inv ;
     Exit when C_INV%NOTFOUND ;
     For i IN 1 .. rec_inv.inv_line.LAST Loop   –- loop through the collection lines
       dbms_output.put_line( 'Numcli/Date ' || rec_inv.inv_numcli || '/' || rec_inv.inv_date
         || ' Line ' || rec_inv.inv_line(i).lig_num
         || ' code ' || rec_inv.inv_line(i).lig_code || ' Qty '
         || To_char(rec_inv.inv_line(i).ligqty) ) ;
     End loop ;
   End loop ;
End ;
/
Numcli/Date 1000/11/11/05 Line 1 code COD_01 Qty 1
Numcli/Date 1000/11/11/05 Line 2 code COD_02 Qty 10

 
 
3.2 Varray
Varrays are more complicated to handle. It is not possible to delete a single element in a Varray collection.
To do the job, you need a PL/SQL block and a temporary Varray that keep only the lines that are not deleted.
Declare
   v_table   TYP_VAR_LIG_ENV ;
   v_tmp     v_table%Type := TYP_VAR_LIG_ENV() ;
   ind       pls_integer  := 1 ;
Begin
   -- select the collection --
   Select inv_line
   Into   v_table
   From   INVOICE_V
   Where  inv_num = 1
   For Update of inv_line ;
   -- Extend the temporary varray --
   v_tmp.EXTEND(v_table.LIMIT) ; 
   For i IN v_table.FIRST .. v_table.LAST Loop
      If v_table(i).lig_num <> 2 Then
         v_tmp(ind) := v_table(i) ; ind := ind + 1 ;
      End if ;
   End loop ;
   Update INVOICE_V Set inv_line = v_tmp Where inv_num = 1 ;
End ;
/

 
Display the new collection:
Declare
   v_table   TYP_VAR_LIG_ENV ;
   LC$Head   Varchar2(200) ;
   LC$Lig    Varchar2(200) ;
Begin
   LC$Head := 'Num Code       Pht        Tva        Qty' ;
   Select inv_line Into v_table From INVOICE_V Where inv_num = 1 For Update of inv_line ;
   dbms_output.put_line ( LC$Head ) ;
   For i IN v_table.FIRST .. v_table.LAST Loop
     LC$Lig := Rpad(To_char( v_table(i).lig_num ),3) || ' '
         || Rpad(v_table(i).lig_code, 10) || ' '
         || Rpad(v_table(i).lig_pht,10) || ' '
         || Rpad(v_table(i).lig_tva,10) || ' '
         || v_table(i).ligqty ;       
     dbms_output.put_line( LC$Lig ) ;      
   End loop ;
End ;
/
Num Code       Pht        Tva        Qty
1   COD_01     1000       5          1
3   COD_03     10         5          100

 
 
The second line of the Varray has been deleted.

Here is a Procedure that do the job with any Varray collection
CREATE OR REPLACE PROCEDURE DEL_ELEM_VARRAY
(
  PC$Table in Varchar2, -- Main table name
  PC$Pk    in Varchar2, -- PK to identify the main table row
  PC$Type  in Varchar2, -- Varray TYPE
  PC$Coll  in Varchar2, -- Varray column name
  PC$Index in Varchar2, -- value of PK
  PC$Col   in Varchar2, -- Varray column
  PC$Value in Varchar2  -- Varray column value to delete
)
IS
  LC$Req Varchar2(2000);
Begin
LC$Req := 'Declare'
||  ' v_table ' || PC$Type || ';'
||  ' v_tmp v_table%Type := ' || PC$Type || '() ;'
||  ' ind  pls_integer := 1 ;'
||  'Begin'
||  ' Select ' || PC$Coll
||  ' Into  v_table'
||  ' From  ' || PC$Table
||  ' Where ' || PC$Pk || '=''' || PC$Index || ''''
||  ' For Update of ' || PC$Coll || ';'
||  ' v_tmp.EXTEND(v_table.LIMIT) ;'
||  ' For i IN v_table.FIRST .. v_table.LAST Loop'
||     ' If v_table(i).' || PC$Col|| '<>''' || PC$Value || ''' Then'
||     '   v_tmp(ind) := v_table(i) ; ind := ind + 1 ;'
||     ' End if ;'
||  ' End loop ;'
||  ' Update ' || PC$Table || ' Set ' || PC$Coll || ' = v_tmp Where ' || PC$Pk || '=''' || PC$Index || ''';'
||  ' End;' ;
  Execute immediate LC$Req ;
End ;
/

 
Let’s delete the third element of the Varray:
Begin
 DEL_ELEM_VARRAY ('INVOICE_V', 'inv_num', 'TYP_VAR_LIG_ENV', 'inv_line', '1', 'lig_num', '3');
End ;
/



4 Query
 Query the whole table
select * from INVOICE;
   INV_NUM INV_NUMCLI INV_DATE
---------- ---------- --------
INV_LINE(LIG_NUM, LIG_CODE, LIG_PHT, LIG_TVA, LIGQTY)
------------------------------------------------------------------------------------------
         3       1001 11/11/05
TYP_TAB_LIG_ENV()
 
         2       1002 12/11/05
TYP_TAB_LIG_ENV(TYP_LIG_ENV(1, 'COD_03', 1000, 5, 1))
 
         1       1000 11/11/05
TYP_TAB_LIG_ENV(TYP_LIG_ENV(1, 'COD_01', 1000, 5, 1), TYP_LIG_ENV(2, 'COD_02', 50, 5, 10))

 
Not easy to read !  Let’s try another syntax:
SELECT t1.inv_num, t1.inv_numcli, t1.inv_date, t2.* FROM invoice t1, TABLE(t1.inv_line) t2
  ORDER BY t1.inv_num, t2.lig_num desc;
   INV_NUM INV_NUMCLI INV_DATE    LIG_NUM LIG_CODE                LIG_PHT    LIG_TVA     LIGQTY
---------- ---------- -------- ---------- -------------------- ---------- ---------- ----------
         1       1000 11/11/05          2 COD_02                       50          5         10
         1       1000 11/11/05          1 COD_01                     1000          5          1
         2       1002 12/11/05          1 COD_03                     1000          5          1

 
We can see that the collection is treated as a table with the TABLE keyword.
The collection could be sorted on any column.
 

Query one main table row with a particular collection row
SELECT   t1.inv_num, t1.inv_numcli, t1.inv_date, t2.* FROM invoice t1, TABLE(t1.inv_line) t2
  WHERE    t1.inv_num  = 1
  AND      t2.lig_code = 'COD_01';
   INV_NUM INV_NUMCLI INV_DATE    LIG_NUM LIG_CODE                LIG_PHT    LIG_TVA     LIGQTY
---------- ---------- -------- ---------- -------------------- ---------- ---------- ----------
         1       1000 11/11/05          1 COD_01                     1000          5          1

 
Query only the collection lines
select t2.* from invoice t1,TABLE(t1.inv_line) t2;
   LIG_NUM LIG_CODE                LIG_PHT    LIG_TVA     LIGQTY
---------- -------------------- ---------- ---------- ----------
         1 COD_03                     1000          5          1
         1 COD_01                     1000          5          1
         2 COD_02                       50          5         10

 
Query the collection for a particular parent row
Use the SELECT FROM TABLE statement
 
SQL Mode
SELECT FROM TABLE
  ( SELECT the_collection FROM the_table WHERE ... )

 
select * from TABLE(SELECT inv_line FROM INVOICE WHERE inv_num = 1);
   LIG_NUM LIG_CODE                LIG_PHT    LIG_TVA     LIGQTY
---------- -------------------- ---------- ---------- ----------
         1 COD_01                     1000          5          1
         2 COD_02                       50          5         10

 
Another syntax:
Select t2.* from invoice t1,TABLE(t1.inv_line) t2
  Where  t1.inv_numcli = 1000;
   LIG_NUM LIG_CODE                LIG_PHT    LIG_TVA     LIGQTY
---------- -------------------- ---------- ---------- ----------
         1 COD_01                     1000          5          1
         2 COD_02                       50          5         10

 
PL/SQL Mode
Declare
  TYPE TYP_REC IS RECORD
  (
    num   INV_LINE_TABLE.LIG_NUM%Type,
    code  INV_LINE_TABLE.LIG_CODE%Type,
    pht   INV_LINE_TABLE.LIG_PHT%Type,
    tva   INV_LINE_TABLE.LIG_TVA%Type,
    qty   INV_LINE_TABLE.LIGQTY%Type
  );
  -- Table of records --
  TYPE TAB_REC IS TABLE OF TYP_REC ;
  t_rec  TAB_REC ;
Begin
  -- Store the lines into the table of records --
  Select * BULK COLLECT INTO t_rec
    from   TABLE(SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt ;
  -- Print the record attributes of each line--
  For i IN t_rec.FIRST .. t_rec.LAST Loop
    dbms_output.put_line( '** Line  = ' || t_rec(i).num || ' **' ) ;
    dbms_output.put_line( 'Code     = ' || t_rec(i).code ) ;
    dbms_output.put_line( 'Price    = ' || t_rec(i).pht ) ;
    dbms_output.put_line( 'Tax rate = ' || t_rec(i).tva ) ;
    dbms_output.put_line( 'Quantity = ' || t_rec(i).qty ) ;
  End loop ;
End ;  
/
** Line  = 1 **
Code     = COD_01
Price    = 1000
Tax rate = 5
Quantity = 1
** Line  = 2 **
Code     = COD_02
Price    = 50
Tax rate = 5
Quantity = 10

 
 
Query a particular column of the collection

SQL Mode
SELECT nt.lig_code, nt.lig_pht
  FROM   TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  WHERE  nt.lig_num = 1;
LIG_CODE                LIG_PHT
-------------------- ----------
COD_01                     1000

 
Another syntax:
Select t2.* from invoice t1,TABLE(t1.inv_line) t2
  Where  t1.inv_numcli = 1000
  And    t2.lig_num = 1;
   LIG_NUM LIG_CODE                LIG_PHT    LIG_TVA     LIGQTY
---------- -------------------- ---------- ---------- ----------
         1 COD_01                     1000          5          1

 
 
PL/SQL Mode
Declare
  TYPE t_rec IS RECORD
  (
    num   INV_LINE_TABLE.LIG_NUM%Type,
    code  INV_LINE_TABLE.LIG_CODE%Type,
    pht   INV_LINE_TABLE.LIG_PHT%Type,
    tva   INV_LINE_TABLE.LIG_TVA%Type,
    qty   INV_LINE_TABLE.LIGQTY%Type
  );
  rec t_rec ;
Begin
  -- Store the line into the record --
  Select *
  Into   rec
  from   TABLE(SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  Where  nt.lig_num = 1 ;
  -- Print the record attributes --
  dbms_output.put_line( 'Code     = ' || rec.code ) ;
  dbms_output.put_line( 'Price    = ' || rec.pht ) ;
  dbms_output.put_line( 'Tax rate = ' || rec.tva ) ;
  dbms_output.put_line( 'Quantity = ' || rec.qty ) ;  
End ;
/
Code     = COD_01
Price    = 1000
Tax rate = 5
Quantity = 1

 
 

Query both table and collection, All the collection’s rows

SQL Mode
SELECT v.inv_numcli, v.inv_date, nt.lig_code, nt.lig_pht
  FROM   INVOICE v, 
         TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  WHERE  v.inv_num = 1;
INV_NUMCLI INV_DATE LIG_CODE                LIG_PHT
---------- -------- -------------------- ----------
      1000 11/11/05 COD_01                     1000
      1000 11/11/05 COD_02                       50

 
A particular collection’s row
SELECT v.inv_numcli, v.inv_date, nt.lig_code, nt.lig_pht
  FROM   INVOICE v, 
         TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  WHERE  v.inv_num = 1
  AND    nt.lig_num = 1;
INV_NUMCLI INV_DATE LIG_CODE                LIG_PHT
---------- -------- -------------------- ----------
      1000 11/11/05 COD_01                     1000

 
 
PL/SQL
Mode
Declare
  invoice_rec  INVOICE%ROWTYPE ;
  LC$Print  Varchar2(512) ;
Begin
  -- Select the INVOICE line --
  Select *
  Into   invoice_rec
  From   INVOICE
  Where  inv_numcli = 1000 ;
  -- Print the parent and collection attributes--
  For i IN invoice_rec.inv_line.FIRST .. invoice_rec.inv_line.LAST Loop
    LC$Print := invoice_rec.inv_numcli
 || ' - ' || To_Char(invoice_rec.inv_date,'DD/MM/YYYY')
 || ' - ' || invoice_rec.inv_line(i).lig_num
 || ' - ' || invoice_rec.inv_line(i).lig_code
 || ' - ' || invoice_rec.inv_line(i).lig_pht
 || ' - ' || invoice_rec.inv_line(i).lig_tva
 || ' - ' || invoice_rec.inv_line(i).ligqty ;  
    dbms_output.put_line( LC$Print ) ;
  End loop ;
End ;  
/
1000 - 11/11/2005 - 1 - COD_01 - 1000 - 5 - 1
1000 - 11/11/2005 - 2 - COD_02 - 50 - 5 - 10


 
What happens when the collection is empty ?
Let’s insert a row with an empty collection:
INSERT INTO INVOICE VALUES
(3,1001 ,SYSDATE
  , TYP_TAB_LIG_ENV()  -- Empty collection
) ;

 
SELECT v.inv_numcli, v.inv_date, nt.lig_code, nt.lig_pht
  FROM   INVOICE v, 
         TABLE (SELECT inv_line FROM INVOICE WHERE inv_num = 1) nt
  WHERE  v.inv_num = 1;
INV_NUMCLI INV_DATE LIG_CODE                LIG_PHT
---------- -------- -------------------- ----------
      1000 11/11/05 COD_01                     1000
      1000 11/11/05 COD_02                       50

 
The client 1001 does not appears in the query
 
You can use NESTED CURSOR to get information on rows where collection is NULL or EMPTY
SELECT v.inv_numcli, v.inv_date,
      CURSOR( SELECT nt.lig_code, nt.lig_pht FROM TABLE (inv_line) nt)
   FROM   INVOICE v;
INV_NUMCLI INV_DATE CURSOR(SELECTNT.LIG_
---------- -------- --------------------
      1001 11/11/05 CURSOR STATEMENT : 3
CURSOR STATEMENT : 3

 

 




Full example moving from Cursor-Loops to Collections and Bulks

Let's say that we want to load one table into another one:
DECLARE
BEGIN
    FOR x IN (SELECT * FROM all_objects)
    LOOP
        INSERT INTO t1 (owner, object_name, subobject_name, object_id,
                        data_object_id, object_type, created, last_ddl_time,
                        timestamp, status, temporary, generated, secondary)
        VALUES (x.owner, x.object_name, x.subobject_name, x.object_id,
                x.data_object_id, x.object_type, x.created,
                x.last_ddl_time, x.timestamp, x.status, x.temporary, x.generated, x.secondary);
    END LOOP;
COMMIT;
END test_proc;

Elapsed: 00:00:20.02

This procedure does three things:
1. Declares a cursor that points to the resultset from SELECT * FROM ALL_OBJECTS
2. Starts at record one, and inserts into the t1 table the columns from the first row in the cursor (here is the BIG problem a lot of calls between PL/SQL and SQL)
3. Then, it loops back and gets the next row of data, until all rows from the cursor have been retrieved.
The data is then committed, and the procedure ends.

The following solution uses a nested table to hold the data from the ALL_OBJECTS table, and performs BULK COLLECT to load all of the source tables' data into the nested table.

truncate table
t1;
CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
   TYPE My_ARRAY IS TABLE OF all_objects%ROWTYPE;
   l_data My_ARRAY;
   CURSOR c IS SELECT *
                FROM all_objects;
BEGIN
   OPEN c;
   LOOP
      FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
      FORALL i IN 1..l_data.COUNT
         INSERT INTO t1 VALUES l_data(i);
   EXIT WHEN c%NOTFOUND;
   END LOOP;
   CLOSE c;
END fast_proc;
/

Elapsed: 00:00:09.06

The next example is a variation on this, that does much the same thing with slightly more compact code, I just removed the cursor.
truncate table t1;
create or replace procedure fast_proc2
is
  TYPE My_ARRAY IS TABLE OF all_objects%ROWTYPE;
  l_data My_ARRAY;

begin
   --Here I put all the rows in memory on this collection
   select * BULK COLLECT INTO l_data
      from ALL_OBJECTS;
   -- Now I work with that collection
   FORALL x in l_data.First..l_data.Last
      INSERT INTO t1 VALUES l_data(x) ;
end;
/

Elapsed: 00:00:09.27


Bulk Binding

Bulk binding improves performance by reducing the context switches between the PL/SQL and SQL engines for execution of SQL statements. Bulk Collect causes the SQL engine to bulk-bind the entire output collection before sending it to the PL/SQL engine. An ‘in-bind’ is when we pass a value from a program to the SQL engine, often either to constraint on a column or to specify a value for a DML statement

Commonly, in-binds are only of interest because they are essential for SQL statements to be sharable. When DBA’s talk of the importance of applications using ‘bind variables’ it is in the context of in-binds since, in applications that use dynamic SQL, using literals instead of bind variables causes each SQL statement to be parsed. This is a critical consideration for overall database performance

An ‘out-bind’ occurs when values are passed from the SQL engine back to the host language. 
When processing a cursor, application developers can choose to either fetch back values one-at-a-time or returned in a batch operation which will bind back many rows to the host application in a single operation.
Before Oracle 8i values being bound out into PL/SQL host variables had to be fetched one at a time.  The following CURSOR FOR-LOOP construct is a familiar one.

--Archive historical data
DECLARE
  CURSOR sales_cur (p_customer_id NUMBER) IS
    SELECT * FROM sales
      WHERE customer_id = p_customer_id;
  v_customer_id    NUMBER := 1234;
BEGIN
  FOR rec IN sales_cur(v_customer_id) LOOP
     INSERT INTO sales_hist(customer_id, detail_id, process_date)
        VALUES (v_customer_id, rec.sales_id, sysdate);
  END LOOP;
END;

--Elapsed: 00:00:44.02 for 360,000 records
--The insert was executed 36,0352 times

In a CURSOR FOR-LOOP, a record variable is implicitly declared that matches the column list of the cursor. On each iteration of the loop, the execution context is switched from the PL/SQL engine to the SQL engine, performing an out-bind of the column values into the record variable once for each loop iteration. Likewise, an in-bind for the insert statement will occur once on each iteration. Although stored PL/SQL code has the advantage over other host languages of keeping this interaction within the same process, the context switching between the SQL engine and the PL/SQL engine is relatively expensive making the above code very inefficient.In addition, the cursor is defined as SELECT * instead of just selecting from the columns to be utilized which is also inefficient. Whether the code references a column or not, Oracle will have to fetch and bind over all of the columns in the select list, slowing down code execution

A better way to perform the above task would be to utilize bulk binding, for both the fetch and the insert statements.  We have two new PL/SQL operators to accomplish this.  The BULK COLLECT (for SELECT and FETCH) statement is used to specify bulk out-binds;  while the FORALL (for INSERT, UPDATE and DELETE) statement is used to provide bulk in-binds for DML statements.

According to the documentation, FORALL is defined as:
"The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:

FORALL index IN lower_bound..upper_bound
    INSERT/UPDATE/DELETE Statements;

and BULK COLLECT is explained as;

"The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before sending them to the PL/SQL engine. You can use these keywords in the SELECT INTO,  FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

     ... BULK COLLECT INTO collection_name[, collection_name] ..."

The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range."

So the previous query could be re-defined as:

--Archive historical data
DECLARE
  -- Here I defined a type based on a field of one table
  TYPE sales_typ IS TABLE OF sales.sales_id%TYPE INDEX BY BINARY_INTEGER;
  --Define sales_ids as the sales_typ type
  sales_ids      sales_t;
  v_customer_id  NUMBER := 1234;
  max_rows       CONSTANT NUMBER := 100;

  CURSOR sales_cur (p_customer_id NUMBER) IS
             SELECT sales_id FROM sales
                WHERE customer_id = p_customer_id;
BEGIN
  OPEN sales_cur(v_customer_id);
  LOOP 
    EXIT WHEN sales_cur%NOTFOUND;
    FETCH sales_cur BULK COLLECT INTO sales_ids LIMIT max_rows;
    FORALL i IN 1..sales_ids.COUNT
      INSERT INTO sales_hist (customer_id, detail_id, process_date)
         VALUES(v_customer_id, sales_ids(i), sysdate);
   END LOOP;
 CLOSE sales_cur;
END;

--Elapsed: 00:00:08.02 for 360,000 records
--The insert was executed 72 times only

In this example, the fetch statement returns with the sales_ids array populated with all of the values fetched for the current iteration, with the maximum number of rows fetched set to 10,000.  Using this method, only a single context switch is required for the SELECT statement to populate the sales_ids array and another switch to bind all of the fetched values to the INSERT statements.  Note also that the FORALL statement is not a looping construct – the array of values is given over in batch to the SQL engine for binding and execution.  This second implementation will run at approximately 15 times the speed of the first, illustrating the importance of efficient binding in data driven code.
One potential issue with the bulk binding technique is the use of memory by the PL/SQL array variables.  When a BULK COLLECT statement returns, all of the fetched values are stored in the target array.  If the number of values returned is very large, this type of operation could lead to memory issues on the database server.  The memory consumed by PL/SQL variables is private memory, allocated dynamically from the operating system.  In dedicated server mode it would be the server process created for the current session that allocates memory.  In the case where such allocation becomes extreme, either the host will become memory bound or the dedicated server process will reach a size where it tries to allocate beyond its addressing limits, normally 2 GB on many platforms.  In either case the server processes call to malloc() will fail raising an ORA-04030 out of process memory error.
To prevent this possibility when loading anything larger than a small reference table, use the optional LIMIT ROWS operator to control the ‘batch size’ of each BULK COLLECT operation.  In the code example below the cursor will iterate though batches of 100 rows fetching in the values and inserting 100 rows.  Do not go over 500. On the final iteration, the cursor will fetch the remaining balance.  Placement of the EXIT WHEN clause should be before the FETCH statement or the last, incomplete batch will not be processed.

Oracle9i Release 2 also allows updates using record definitions by using the ROW keyword:

DECLARE
  TYPE test1_tab IS TABLE OF test1%ROWTYPE;
  t_tab  test1_tab := test1_tab();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_tab.extend;
    t_tab(t_tab.last).id          := i;
    t_tab(t_tab.last).description := 'Description: ' || To_Char(i);
  END LOOP;
 
  FOR i IN t_tab.first .. t_tab.last LOOP
    UPDATE test1
       SET   ROW = t_tab(i)
       WHERE id  = t_tab(i).id;
  END LOOP;
  COMMIT;
END;
/



SELECT with RECORD bind

Please remember that you could BULK SELECT INTO a collection of records. For example, suppose that we would like to retrieve all employees hired before June 25, 1997, and then give them all big, fat raises.
With Oracle9i Release 2, our program becomes much shorter, intuitive and maintainable. What you see below is all we need to write to take advantage of BULK COLLECT to populate a single associative array of records:
 
DECLARE
  v_emprecs  emp_util.emprec_tab_t;
  CURSOR cur IS SELECT * FROM employees
                    WHERE hire_date < '25-JUN-97';
BEGIN
   OPEN cur;
      FETCH cur BULK COLLECT INTO v_emprecs LIMIT 10;
   CLOSE cur;
   emp_util.give_raise (v_emprecs);
END;
[Note: the clause limit 10 is equivalent to where rownum <= 10.]
 
Even more wonderful, we can now combine BULK COLLECT fetches into records with NATIVE DYNAMIC SQL. Here is an example, in which we give raises to employees for a specific schema:
CREATE OR REPLACE PROCEDURE give_raise (schema_in IN VARCHAR2)
IS
   v_emprecs  emp_util.emprec_tab_t;
   cur        SYS_REFCURSOR;
BEGIN
    OPEN cur FOR 'SELECT * FROM ' || schema_in || '.employees' || 'WHERE hire_date < :date_limit' USING '25-JUN-97';
    FETCH cur BULK COLLECT INTO v_emprecs LIMIT 10;
    CLOSE cur;
    emp_util.give_raise
( schema_in, v_emprecs);

END;
SYS_REFCURSOR is a pre-defined weak REF CURSOR type.


INSERT with RECORD bind

Since Oracle9i Release 2 we can now take advance of simple, intuitive and compact syntax to bind an entire record to a row in an insert. This is shown below:

DECLARE

   v_emprec employees%rowtype := Emp_Util.Get_One_Row;
BEGIN
   INSERT INTO employees_retired
       VALUES v_emprec;
END;
 
Notice that we do not put the record inside parentheses. You are, unfortunately, not able to use this technique with Native Dynamic SQL. You can, on the other hand, insert using a record in the highly efficient FORALL statement. This technique is valuable when you are inserting a large number of rows.
Take a look at the following example. The following table explains the interesting parts of the
retire_them_now procedure

CREATE OR REPLACE PROCEDURE retire_them_now
IS
   --Declare an Exception
   bulk_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   --Declare an associative array as rowtype of the employees table.
   TYPE employees_t IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
   retirees employees_t;
BEGIN
   --Load up the array with the information for all employees who are over 40 years of age
   FOR rec IN (SELECT * FROM employees
                WHERE hire_date < ADD_MONTHS (SYSDATE, -1 * 18 * 40))
   LOOP
      retirees (SQL%ROWCOUNT) := rec;
   END LOOP;
   -- Added a clause to "catch" exceptions
   FORALL indx IN retirees.FIRST .. retirees.LAST SAVE EXCEPTIONS
      INSERT INTO employees
         VALUES retirees (indx);
EXCEPTION
   --Catch the exception
   WHEN bulk_errors THEN
     FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
     LOOP
       DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
     END LOOP;
END;

 

UPDATE SET ROW with RECORD bind
Oracle9i Release 2 now gives you an easy and powerful way to update an entire row in a table from a record: the SET ROW clause. The ROW keyword is functionally equivalent to *. It is most useful when the source of the row is one table and the target is a different table with the same column specification, for example in a scenario where rows in an application table are updated once or many times and may eventually be deleted, and where the latest state of each row (including when it has been deleted) must be reflected in an audit table. (Ideally we’d use MERGE with a RECORD bind, but this isn’t supported yet.). The new syntax for the Static SQL, single row case is obvious and compact:
DECLARE
   v_emprec employees%ROWTYPE := emp_util.get_one_row;
BEGIN
   v_emprec.salary := v_emprec.salary * 1.2;
   UPDATE employees_2 SET ROW = v_emprec
      WHERE employee_id = v_emprec.employee_id;
END;
 

DELETE and UPDATE with RETURNING with RECORD bind
You can also take advantage of rows when using the RETURNING clause in both DELETEs and UPDATEs.
The RETURNING clause allows you to retrieve and return information that is processed in the DML statement without using a separate, subsequent query. Record-based functionality for RETURNING means that you can return multiple pieces of information into a record, rather than individual variables. Example:
  RETURNING multiple rows of information from an UPDATE statement.

DECLARE
    v_emprecs emp_util.emprec_tab_t;
BEGIN
    UPDATE employees SET salary = salary * 1.1
        WHERE hire_date < = '25-JUN-97'
        RETURNING employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id
            BULK COLLECT INTO v_emprecs;
END;

Another Example returning just 1 row:
DECLARE
   TYPE EmpRec IS RECORD (last_name   employees.last_name%TYPE,
                          salary      employees.salary%TYPE);
   emp_info EmpRec;
   emp_id   NUMBER := 100;
BEGIN
   UPDATE employees SET salary = salary * 1.1
      WHERE employee_id = emp_id
      RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE('Just gave a raise to ' || emp_info.last_name ||', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/


Another Example
returning several rows:
drop table emp_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,30);
   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
   TYPE dept_t IS TABLE OF employees.department_id%TYPE;
   e_ids enum_t;
   d_ids dept_t;
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp WHERE department_id = depts(j)
       RETURNING employee_id, department_id
         BULK COLLECT INTO e_ids, d_ids;

  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
  END LOOP;
END;
/


NOTE:
You cannot use returning bulk collect into a for INSERT .... INTO ... SELECT.... statement. It can be used for update and delete.
"insert as select" is currently not supported with the returning clause, you can ONLY use insert with the "values" clause.
So the following statement will return errors:
create table emp_temp as select * from emp where 1 = 0;
declare
   type num_type is table of number;
   l_empno num_type := num_type();
begin
   insert into emp_temp(empno,ename)
     select empno,ename from emp
       returning empno
       bulk collect into l_empno;
end;
/


You currently have to two step this by
a) selecting bulk collecting the data
b) forall i bulk inserting the data.

declare
   type num_type is table of number;
   l_empno num_type;
   type var_type is table of varchar(10);
   l_empname var_type;
begin
   select empno,ename bulk collect into l_empno,l_empname from emp;
      
   forall i in l_empno.first..l_empno.last
       insert into emp_temp(empno,ename)
       values(l_empno(i),l_empname(i));
end;
/


 

Bulk BinDing in Native dynamic SQL

In-Binding
Both the EXECUTE IMMEDIATE and FORALL (for bulk DML operations) offer a USING clause to bind variable values into the SQL statement. Let's follow the progression of explicit row-from-row processing to bulk binding to bulk binding in native dynamic DML to see how the USING clause is deployed.
We start with this kind of explicit FOR loop in our Oracle7 and Oracle8 code base:
FOR indx IN employee_ids.FIRST .. employee_ids.LAST
LOOP
   UPDATE employees SET salary = salary * 1.1
      WHERE employee_id = employee_ids (indx);
END LOOP;

Then, with Oracle8i, we get rid of most of the context switches by moving to FORALL:

FORALL indx IN employee_ids.FIRST .. employee_ids.LAST
  UPDATE employees SET salary = salary * 1.1
   WHERE employee_id = employee_ids (indx);

And that handles all of our needs-unless, once again, we need or would like to perform this same operation on different tables, based on location (or for any other kind of dynamic SQL situation). In this case, we can combine FORALL with EXECUTE IMMEDIATE, with these wonderful results:
CREATE OR REPLACE PROCEDURE upd_employees (
  loc_in IN VARCHAR2,
  employees_in IN employees_t )
IS
BEGIN
   FORALL indx in employees_in.first..employees_in.last
    EXECUTE IMMEDIATE 'UPDATE ' || loc_in || ' employees SET salary = salary*1.1' || ' WHERE employee_id = :the_id'
      USING employee_in (indx);
END;

Notice that in the USING clause, we must include both the name of the collection and the subscript for a single row using the same FORALL loop index variable.


Out-Binding
Let's again follow the progression from individual row updates to bulk bind relying on BULK COLLECT INTO to retrieve information, and finally the dynamic approach possible in Oracle 9i.
Oracle8 enhanced DML capabilities by providing support for the RETURNING clause. Shown in the following FOR loop, it allows us to obtain information (in this case, the updated salary) from the DML statement itself (thereby avoiding a separate and expensive query).
BEGIN
   FOR indx IN employee_ids.FIRST .. employee_ids.LAST
   LOOP
      UPDATE employees SET salary = salary * 1.1
          WHERE employee_id = employee_ids (indx)
      RETURNING salary INTO salaries (indx);
   END LOOP;
END;

Starting with Oracle8i, we can take advantage of FORALL to improve performance dramatically:
BEGIN
   FORALL indx IN employee_ids.FIRST .. employee_ids.LAST
      UPDATE employees SET salary = salary * 1.1
          WHERE employee_id = employee_ids (indx)
      RETURNING salary BULK COLLECT INTO salaries;
END;

There's one seemingly odd aspect of this code you should remember: Inside the DML statement, any reference to the collection that drives the FORALL statement must be subscripted as in:
WHERE employee_id = employee_ids (indx)
In the RETURNING clause, however, you BULK COLLECT INTO the collection and not a single subscripted row of the collection.

That's all well and good, but what if (not to sound like a broken record) we want to execute this same update for any of the employee tables for different locations? Time to go to NDS and use the RETURNING BULK COLLECT clause:
CREATE OR REPLACE PROCEDURE upd_employees (loc_in IN VARCHAR2, employees_in IN employees_t)
IS
   my_salaries salaries_t;
BEGIN
   FORALL indx in employees_in.first.. employees_in.last
    EXECUTE IMMEDIATE 'UPDATE '|| loc_in || ' employees SET salary = salary*1.1' || ' WHERE employee_id = :the_id
      RETURNING salary INTO :salaries'
      USING employee_in (indx)
    RETURNING BULK COLLECT INTO my_salaries;
END;


Handling and Reporting Exceptions
You can now trap errors that happen in your FORALL statement by taking advantage of the SAVE EXCEPTIONS clause.
FORALL indx IN words.first..words.last SAVE EXCEPTIONS
  INSERT INTO vocabulary(text)
     VALUES ( words(indx) );

Use of SAVE EXCEPTIONS allows the FORALL to continue through all the rows indicated by the collection; it "saves up" the exceptions as it encounters them. This saving step begs the obvious question: How can you, the developer, get information about the errors that were "saved"? By taking advantage of the new SQL%BULK_COLLECTIONS pseudo-collection, as demonstrated in the code shown in the following example:

DECLARE

  bulk_errors   EXCEPTION;
  PRAGMA EXCEPTION_INIT (bulk_errors,  -24381);
BEGIN
  FORALL indx IN words.FIRST .. words.LAST SAVE EXCEPTIONS
    INSERT INTO t (text) VALUES (words (indx));
EXCEPTION
  WHEN bulk_errors THEN
     --For each error found, try to identify the cause of that error
     FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
     LOOP
                Dbms_Output.Put_Line ('Iteration Number ' || sql%bulk_exceptions(j).error_index);
        Dbms_Output.Put_Line ('Error ' || Sqlerrm(sql%bulk_exceptions(j).error_code*-1));
        --Detecting Unique Constraint Violation
        if substr(Sqlerrm(SQL%BULK_EXCEPTIONS(J).ERROR_CODE * -1),1,9) = 'ORA-00001' then
           v_RowsDuplicated := v_RowsDuplicated + 1;
        else
           Dbms_Output.Put_Line ('Other type of Error on Issuedata Import');
        end if;
     END LOOP;
     v_newerrors := SQL%BULK_EXCEPTIONS.COUNT;
     v_errors := v_errors + v_newerrors;
     dbms_output.put_line('Total Errors= ' || to_char(v_errors));
END;

Each row of this pseudo-collection is a record consisting of two fields: ERROR_INDEX and ERROR_CODE. The ERROR_INDEX shows which index in the original bulk-load collection causes the failure. ERROR_CODE is the error number encountered.
You must both use the SAVE EXCEPTIONS construct and handle the BULK_ERRORS exception to get the intended benefit (that is, that all non-erroring rows are inserted).


Implementation restriction
It is not allowed to use the FORALL statement and an UPDATE order that use the SET ROW functionality
Declare
   TYPE    TAB_EMP is table of EMP%ROWTYPE ;
   emp_tab TAB_EMP ;
   Cursor  CEMP is Select * From EMP ;
Begin 
   Open  CEMP;
   Fetch CEMP BULK COLLECT Into emp_tab ;
   Close CEMP ;
 
  Forall i in emp_tab.first..emp_tab.last
    Update EMP set row =  emp_tab(i) where EMPNO = emp_tab(i).EMPNO ; -- ILLEGAL
  
End ;
/

Update EMP set row =  emp_tab(i) where EMPNO = emp_tab(i).EMPNO ; -- ILLEGAL
ERROR at line 11:
ORA-06550: line 11, column 52:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
 
You have to use a standard FOR LOOP statement:
For i in emp_tab.first..emp_tab.last loop
   Update EMP set row =  emp_tab(i) where EMPNO = emp_tab(i).EMPNO ;
End loop ;
 
Or use simple collections:
Declare
   TYPE    TAB_EMPNO   is table of EMP.EMPNO%TYPE ;
   TYPE    TAB_EMPNAME is table of EMP.ENAME%TYPE ;
   no_tab  TAB_EMPNO ;
   na_tab  TAB_EMPNAME ; 
   Cursor  CEMP is Select EMPNO, ENAME From EMP ;
Begin 
   Open  CEMP;
   Fetch CEMP BULK COLLECT Into no_tab, na_tab ;
   Close CEMP ;
 
  Forall i in no_tab.first..no_tab.last
    Update EMP set ENAME = na_tab(i) where EMPNO = no_tab(i) ;
End ;
 
Restrictions on BULK COLLECT
The following restrictions apply to the BULK COLLECT clause:

- You cannot bulk collect into an associative array that has a string type for the key.

- You can use the BULK COLLECT clause only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.

- All targets in a BULK COLLECT INTO clause must be collections, as the following example shows:
DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   names  NameList;
   salary emp.sal%TYPE;
BEGIN
   SELECT ename, sal BULK COLLECT INTO names, salary  -- illegal target
      FROM emp WHERE ROWNUM < 50;
   ...
END;

- Composite targets (such as objects) cannot be used in the RETURNING INTO clause. Otherwise, you get the error unsupported feature withRETURNING clause.

- When implicit datatype conversions are needed, multiple composite targets cannot be used in the BULK COLLECT INTO clause.

- When an implicit datatype conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULKCOLLECT INTO clause.



Multi-Dimensional Arrays
Another new feature is the capability of multi-dimensional arrays, which Oracle has implemented as collections of collections.Technically, all collection types support only a single dimension, however by allowing a collection element to become a collection, one has the effectively the same data structure.  The following code shows the way to declare and reference a two-dimensional array of numbers.
DECLARE
  TYPE element        IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE twoDimensional IS TABLE OF element INDEX BY BINARY_INTEGER;
  twoD twoDimensional;
BEGIN
  twoD(1)(1) := 123;
  twoD(1)(2) := 456;
END;

 
At first one would think that, while an interesting capability, it has no potential impact on performance but it will be shown later in this paper how the combination of this capability along with the use of packaged variables can open up the door to dramatically speeding up PL/SQL code.


Another Example

To demonstrate this, let's use the example of students in a college or university.
In the real world the information we would want to know about students would most likely include :
- Addresses (home/holiday, term-time), more than one
- Phone numbers (home, term-time, mobile)
- Email addresses (home, college, other)
- First and Last Name
- etc

But let's keep things simple for this example and restrict ourselves to just the first and last name and 2 addresses simply to demonstrate the principles of nesting collections.
This means that we have 1 entity - student - with the following attributes : first & last name and 2 addresses. In a database, entities translate to a table, so we have our first data structure: a table (or collection in PL/SQL terms).
The attributes would normally translate to columns in the table but Oracle's PL/SQL doesn't have the concept of columns so we have to use the record structure.
Now we have a problem though. Our student record needs to contain 2 addresses, how do we handle this in PL/SQL ? We could just have two different address attributes (a home address and an term-time address) but we might need to add more addresses later so we'll use a PLSQL collection for the addresses.
The next problem is that each address has many lines, how do we handle that? Again, no problem. We simply define an address as a collection of address lines.

So let's take a look at our data structures.
We have addresses which are tables of address lines, but to declare this in PL/SQL we need to create the address lines type first and then create the address type, as follows:

TYPE address_lines IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE address_tab IS TABLE OF address_lines INDEX BY VARCHAR2(10);

Note that we are indexing address_lines by binary_integer and addresses by varchar2 so that we can look up addresses by name (e.g. 'home' or 'term-time') rather than by number.

Now we have nested our collections (associative arrays in this case) - we have a table of addresses and each address consists of a collection of address lines. Note that we can't use any of these data structures yet, as we've only defined the types, not declared any variables of those types.
The next thing to do is to define our record structure to hold the student information as follows:

TYPE student IS RECORD      (
       first_name VARCHAR2(50),
       last_name  VARCHAR2(50),
       addresses  address_tab);

So far, so good, but we only have the ability to store information about 1 student at the moment so we need to define another type - a table (collection) of students:
TYPE students_tab IS TABLE OF student INDEX BY BINARY_INTEGER;

Note that this time we're indexing by binary integer, which could be the numeric student id, as we're already storing the first and last names.
The final data structure declaration is that of the variable to store the data.
students students_tab;

Now we have PL/SQL collections nested 3 deep - we have a collection of students, each of which has a collection of addresses (home, term-time, etc), each of which in turn has a collection of address lines. We could extend the nesting by having say a collection of colleges, each of which has a collection of students and so on, but just because we can do things it doesn't necessarily mean we should do them!
Let's put this all together now and write some code to store and retrieve some data.

DECLARE
   TYPE address_lines IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
   TYPE address_tab IS TABLE OF address_lines INDEX BY VARCHAR2(10);
   TYPE student IS RECORD (
       first_name VARCHAR2(50),
       last_name  VARCHAR2(50),
       addresses  address_tab);
   TYPE students_tab IS TABLE OF student INDEX BY BINARY_INTEGER;
   students students_tab;

BEGIN
   students(1).first_name := 'Mike';
   students(1).last_name := 'Johnson';
   students(1).addresses('HOME')(2) := 'Bristol';
   students(1)Addresses('HOME')(1) := '1 The Avenue';
   students(1)Addresses('HOME')(3) := 'BS99 3SJ';

   students(2)First_name := 'Mike';
   students(2)Last_name := 'Bliss';
   students(2)Addresses('HOME')(2) := 'Leicester';
   students(2)Addresses('HOME')(1) := '11 The Street';
   students(2)Addresses('HOME')(3) := 'LE64 50Q';

   FOR student_no IN students.first..students.last LOOP
        FOR address_line_no IN students(student_no)Addresses('HOME')First ..students(student_no)Addresses('HOME')Last LOOP
            dbms_output.put_line('Student('||student_no||') home address line('||address_line_no||') '|| students(student_no)Addresses('HOME')(address_line_no));
        END LOOP;
   END LOOP;
END;
/

Student(1) home address line(1) 1 The Avenue

Student(1) home address line(2) Bristol
Student(1) home address line(3) BS99 3SJ
Student(2) home address line(1) 1 The Street
Student(2) home address line(2) Leicester
Student(2) home address line(3) LE64 50Q

Note how access to the innermost collection is achieved :
students(student_no)Addresses('HOME')(address_line_no)

The first collection is a set of records, the part of the record we are interested in, is in turn another collection each element of which is another collection. The (address_line_no)clause gives us the access to the base level collection but we can't access it by name because it doesn't have one, the type declaration is only a place holder in effect.





Returning Result Sets
There are different approaches for returning results sets:
1- Returning a single row
2- Returning cursors using REF CURSOR
3- Returning collections
4- Return collections with dynamic SQL
5- Return using table functions.

1- Returning a Single Row
PL/SQL makes it very easy to query a row of data from a table and then return that row as a record.

CREATE OR REPLACE FUNCTION or_employee (employee_id_in IN employee.employee_id%TYPE)
RETURN employee%ROWTYPE
IS
  CURSOR onerow_cur IS SELECT employee_id, last_name, first_name
                         FROM employee
                         WHERE employee_id = employee_id_in;
  retval employee%ROWTYPE;
BEGIN
  OPEN onerow_cur;
    FETCH onerow_cur INTO oretval;
  CLOSE onerow_cur;
  RETURN retval;
END or_employee;
/

The advantage of this approach is that it’s very simple code, easily understood by all PL/SQL developers.
The downsides of this approach include:
• The calling environment needs to be able to work with PL/SQL records, which aren’t universally recognized.
• This approach forces you to work with a single row at a time. If the calling program needs to retrieve lots of data, this will be a relatively slow alternative.


2- Returning Cursor Variables using REF CURSOR
One of the best ways to isolate an application from SQL dependencies is to write a package of PL/SQL functions that return the REF CURSOR type to the calling programs written in other host languages such as Java or C.  Cursor variables can be either weakly typed, which are more flexible, or strongly typed, which provide greater type safety.  Of course, the application  must know the number and data types of the returned columns as well as their semantics in order to use the data, but it can be totally isolated from the way the data is stored.   The following function returns a weakly typed cursor using the new 9i type SYS_REFCURSOR.
FUNCTION email_cur RETURN sys_refcursor IS
  rc sys_refcursor;
BEGIN
  OPEN rc FOR
    SELECT * FROM emp;
  RETURN rc;
END;

 
An application can call the function and bind the returned open cursor to a local cursor variable.  The application then iterates through the result set as if it had been defined and opened locally.
The advantages of the cursor variable approach include the following:
• The cursor variable can be passed back to a non-PL/SQL host environment such as Java, which can then fetch individual rows through the cursor variable, and close it when completed.
• Within PL/SQL, you can manipulate the cursor variable using traditional and familiar syntax.

The downsides of this approach include:
• Cursor variables require row-by-row querying from the database, so it’s relatively slow compared to BULK COLLECT.
• Relatively few PL/SQL developers are familiar with (and even fewer comfortable with) the REF CURSORs and cursor variables. The learning curve, however, is quite short.

3- Returning Collections
Another approach to returning result sets is to write a function that explicitly returns a PL/SQL collection type.
As a native PL/SQL datatype, a collection can also be passed as an argument in a parameter list and returned by a function.
Also, since there are no predefined collection types in PL/SQL, the returned type must either be declared in a shared package header or be a SQL type declared globally in the database. 
Let’s take a look at a simple example. Suppose that I need to write a function that returns a collection containing all the rows in the employee table. I’ll first create a package specification that contains two elements:
• The TYPE statement that defines the structure of the collection
• A function to return a collection of that type
CREATE OR REPLACE PACKAGE many_employees
IS
  TYPE employee_tc IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
   FUNCTION allrows RETURN employee_tc;
END;
/

The package body contains the implementation of that function by relying on the fast and concise BULK COLLECT query.
Note that with BULK COLLECT, a SELECT INTO will not raise NO_DATA_FOUND if no rows are found. Instead, you must check to see how many rows are defined in the collection to determine whether the query returned any rows.
CREATE OR REPLACE PACKAGE BODY
many_employees IS
   FUNCTION allrows RETURN employee_tc
  IS
   l_employees employee_tc;
   BEGIN
      SELECT * BULK COLLECT INTO l_employees
        FROM employee;
        RETURN l_employees;
   END allrows;
END many_employees;

/
From within PL/SQL, I can call this function and then iterate through the returned rows of the collection. In the example shown here, I display the last name of each employee found in the collection:
DECLARE
   l_employees
many_employees.employee_tc;
   l_row PLS_INTEGER;
BEGIN
   l_employees := many_employees
.allrows;
   l_row := l_employees.FIRST;
   WHILE (l_row IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (l_employees(l_row).last_name);
      l_row := l_employees.NEXT(l_row);
   END LOOP;
END;
/

The advantages of the collection approach include:
• BULK COLLECT allows you to greatly improve the performance of querying the data.
• The code is concise, highly readable, and easy to maintain.

The downsides of the collection approach include:
• The calling environment needs to be able to work with PL/SQL collections, which aren’t universally recognized.
• Relatively few PL/SQL developers are familiar with (and even fewer comfortable with) the collections data structure. Collections are fabulously useful and very efficient.
• Collections consume PGA (program global area) memory. If you’re building a multi-user application and working with large tables, you’ll need to be conscious of the size of your collections. You can apply the LIMIT clause to BULK COLLECT to manage the memory consumed by your program.

4- Return Collections with Dynamic SQL
Suppose, for example, that I need to write a function that returns the result set from the OM_EMPLOYEE table as specified by a dynamic WHERE clause.
I’ll add a new “all rows by” program to the many_employees package I created before:

CREATE OR REPLACE PACKAGE many_employees
IS
   TYPE employee_tc IS TABLE OF om_employee%ROWTYPE INDEX BY BINARY_INTEGER;
   FUNCTION allrows RETURN employee_tc;

   -- Use OPEN FOR to retrieve rows for dynamic WHERE clause
   FUNCTION allrows_by (where_in IN VARCHAR2 DEFAULT NULL)
      RETURN employee_tc;
END many_employees;
/

We could do row-by-row querying into the collection or bulk processing. Let’s start with the “oldfashioned” row-by-row processing. This version is shown here:
FUNCTION allrows_by (where_in IN VARCHAR2 DEFAULT NULL)
   RETURN employee_tc
IS
   TYPE weak_rc IS REF CURSOR;
   allrows_cur weak_rc;
   l_rows PLS_INTEGER;
   retval employee_tc;
BEGIN
   IF where_in IS NULL THEN
      RETURN allrows;
   ELSE
      OPEN allrows_cur FOR 'SELECT * FROM om_EMPLOYEE WHERE ' || where_in;
      LOOP
         FETCH allrows_cur INTO retval (retval.COUNT + 1);
      EXIT WHEN allrows_cur%NOTFOUND;
      END LOOP;
      RETURN retval;
   END IF;
END allrows_by;

Here’s an explanation of the code:
• I’ll use the OPEN FOR statement with the dynamic query, so I need to declare a REF CURSOR (weak_rc) and associated cursor variable (allrows_cur).
• If the optional WHERE clause is null, I’ll simply call the original allrows function to return all the rows.
• If I have a WHERE clause, then I’ll use the OPEN FOR statement with the dynamic query (this feature became available in Oracle8i; previously, OPEN FOR could only be used with static SQL).
• I then use a simple loop to fetch each of the rows from the cursor and deposit that data into my collection in sequentially filled rows.

Now, having done and seen all of that code, I must also caution you that whenever you face the prospect of querying data on a row-by-row basis, you should consider switching to BULK COLLECT. This construct generally will allow you to write less code (if you were already using collections) and execute your code much more efficiently. In this implementation, if my WHERE clause isn’t null, then I simply construct the query, execute within the EXECUTE IMMEDIATE statement, and then BULK COLLECT INTO my collection. With that single statement, all the data in om_employee is transferred to the collection (and the rows are populated sequentially, starting with 1).
FUNCTION allrows_by (where_in IN VARCHAR2 DEFAULT NULL)
   RETURN employee_tc
IS
   l_rows PLS_INTEGER;
   retval employee_tc;
BEGIN
   IF where_in IS NULL THEN
      RETURN allrows;
   ELSE
      EXECUTE IMMEDIATE 'SELECT * FROM om_EMPLOYEE ' ||'WHERE ' || where_in
      BULK COLLECT INTO retval;
      RETURN retval;
   END IF;
END allrows_by;

5- Table Function
Most client programs however, don’t really want to deal with trying to bind to a PL/SQL user defined type; instead, they want a cursor.  The TABLE function provides a way to take a function like the one above and return its results to the caller directly as a cursor.  Recall that the TABLE function takes a variable of a globally defined collection type as an argument, therefore a function with a return type of the same collection type, like the one above, can be used as an argument to the TABLE function as well.  Without modifying the above procedure, a program can return its output collection as a cursor using the following syntax.  Views can be wrapped around this kind of SQL statement to make life easier for a calling application.
 
SELECT * FROM TABLE( CAST( get_email_demo(45) AS email_demo_nt_t ));


Pipelined Table Functions
While that approach works, it is really only appropriate for smaller result sets of perhaps a few thousand rows.  When the function executes to populate the result set, the data is buffered in the local variable of the procedure.  Only after the procedure has finished executing, will the rows be returned to the calling application.  Memory to store the buffered data is dynamically allocated from the operating system by the server process executing the procedure If the result set was very large, operating system memory could become depleted.
Pipelined table functions are an Oracle 9i facility that address this issue by providing a mechanism to stream the values from the function back to the calling application while the function is executing.  A small amount of data remains buffered in the function’s address space so that result sets can be sent back in batches, which is faster than row-by-row processing.  This is a far more scalable design for this functionality since the operating system memory footprint is independent of the size of the result set.
To utilize this feature, the function must be declared as PIPELINED and collection objects must be returned one at a time via a new function called PIPE ROW.  The function contains a RETURN statement without arguments that is used to terminate the cursor.  The function can now be rewritten to take advantage of pipelining. 
 
FUNCTION get_email_demo  RETURN email_demo_nt_t PIPELINED IS
  CURSOR email_demo_cur IS
    SELECT email_demo_obj_t(email_id, demo_id, value)
      FROM email_demographic;
  eml_dmo_nt   email_demo_nt_t;
BEGIN
  OPEN email_demo_cur;
  LOOP
    FETCH email_demo_cur BULK COLLECT INTO eml_dmo_nt LIMIT 1000;
    EXIT WHEN email_demo_cur%NOTFOUND;
      FOR i IN 1..eml_dmo_nt.COUNT LOOP
        /*  Apply some business logic on the object here, and return a row. */
        PIPE ROW (eml_dmo_nt(i));
      END LOOP;
   END LOOP;
   RETURN;
END;

 
Note that while the return type of the function is still the collection type, the local variable being assigned is the object type.  In this example, the fetch is performed using the BULK COLLECTfeature.  The documents illustrate the much slower row-by-row fetch.  Since the signature of the procedure has not been changed, only the implementation, it can be called the same way as the previous table function using the TABLE and CAST functions.



Cursor Attributes
More information can be found HERE

Specific for BULK and FORALL:

The %BULK_ROWCOUNT attribute is a handy device, but it is also quite limited. Keep the following in mind:



Oracle10g enhancements for nested tables
The essential advance made in Oracle10g is that Oracle is now treating nested tables more like the multisets that they are. This means that it’s providing high-level set operations that can be applied to nested tables (and only, for the time being, to nested tables). More information HERE

FORALL Support for Non-Consecutive Indexes (Sparse Collections)
Oracle 10g introduces support for the FORALL syntax with non-consecutive indexes in collections.
The INDICES OF clause allows the FORALL syntax to be used with sparse collections, while the VALUE OF clause is used for collections of indexes pointing to other collections. The following are examples of their usage.
DECLARE
  TYPE t_tab1 IS TABLE OF tab1%ROWTYPE;
  TYPE t_tab2 IS TABLE OF BINARY_INTEGER;

  l_tab1  t_tab1 := t_tab1();
  l_tab2  t_tab2 := t_tab2();
BEGIN
  FOR i IN 1 .. 1000 LOOP
    l_tab1.extend;
    l_tab1(l_tab1.last).id := i;
    IF MOD(i, 100) = 0 THEN
      l_tab2.extend;
      l_tab2(l_tab2.last) := i;
    END IF;
  END LOOP;

  l_tab1.delete(301);
  l_tab1.delete(601);
  l_tab1.delete(901);

  -- This would fail due to sparse collection.
  --FORALL i IN l_tab.first .. l_tab.last
  --  INSERT INTO tab1 VALUES l_tab(i);

  -- This works fine with sparse collections.
  FORALL i IN INDICES OF l_tab1
    INSERT INTO tab1 VALUES l_tab1(i);

  -- This works fine for collections of indexes pointing to elements of another collection.
  FORALL i IN VALUES OF l_tab2
    INSERT INTO tab1 VALUES l_tab1(i);
END;
/


Nested Table Enhancements
Nested tables in PL/SQL now support more operations than before. Collections can be assigned directly to the value of another collection of the same type, or to the result of a set expression.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Yellow', 'Green');
  l_col_3 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_3 := l_col_1;
  display('Direct Assignment:', l_col_3);

  -- Expression assignments.
  l_col_3 := l_col_1 MULTISET UNION l_col_2;
  display('MULTISET UNION:', l_col_3);

  l_col_3 := l_col_1 MULTISET UNION DISTINCT l_col_2;
  display('MULTISET UNION DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT l_col_2;
  display('MULTISET INTERSECT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT DISTINCT l_col_2;
  display('MULTISET INTERSECT DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT l_col_2;
  display('MULTISET EXCEPT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT DISTINCT l_col_2;
  display('MULTISET EXCEPT DISTINCT:', l_col_3);
END;
/

Direct Assignment:
Red
Green
Blue
Green
Blue

MULTISET UNION:
Red
Green
Blue
Green
Blue
Red
Green
Yellow
Green

MULTISET UNION DISTINCT:
Red
Green
Blue
Yellow

MULTISET INTERSECT:
Red
Green
Green

MULTISET INTERSECT DISTINCT:
Red
Green

MULTISET EXCEPT:
Blue
Blue

MULTISET EXCEPT DISTINCT:
Blue


Comparissions between collections
Comparisons between collections have also improved with the addition of NULL checks, equality operators and set operations.
SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Green');
  l_col_3 t_colors;
BEGIN
  IF (l_col_3 IS NULL) AND (l_col_1 IS NOT NULL) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 IS NULL) AND (l_col_1 IS NOT NULL): TRUE');
  END IF;

  l_col_3 := l_col_1;

  IF (l_col_3 = l_col_1) AND (l_col_3 != l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 = l_col_1) AND (l_col_3 != l_col_2): TRUE');
  END IF;

  IF (SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2): TRUE');
  END IF;

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(l_col_2): ' || CARDINALITY(l_col_2));

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(SET(l_col_2)): ' || CARDINALITY(SET(l_col_2)) || ' - Duplicates removed');

  IF l_col_2 IS NOT A SET THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_2 IS NOT A SET: TRUE - Contains duplicates');
  END IF;

  IF l_col_3 IS NOT EMPTY THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_3 IS NOT EMPTY: TRUE');
  END IF;
END;
/

(l_col_3 IS NULL) AND (l_col_1 IS NOT NULL): TRUE

(l_col_3 = l_col_1) AND (l_col_3 != l_col_2): TRUE

(SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2): TRUE

CARDINALITY(l_col_2): 3

CARDINALITY(SET(l_col_2)): 2 - Duplicates removed

l_col_2 IS NOT A SET: TRUE - Contains duplicates

l_col_3 IS NOT EMPTY: TRUE


Remove Duplicates
The SET function removes duplicate entries from your nested table, in a similar way to the SQL DISTINCT aggregate function.
SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_2 := l_col_1;
  display('Direct Assignment:', l_col_2);

  -- SET assignments.
  l_col_2 := SET(l_col_1);
  display('MULTISET UNION:', l_col_2);
END;
/

Direct Assignment:
Red
Green
Blue
Green
Blue

MULTISET UNION:
Red
Green
Blue


Support for in and not in Operators
In Oracle 10g, it is possible to use in and not in operators to determine if a given nested table appears in another nested table.
 The in and not in operators return a Boolean value depending on the result of the execution of the operator.
Below is an example of using this functionality. This example re-creates the T_EMP_INFO table, adding another column.
It then creates two records to test with. Finally, it issues two queries. In the first query, using the in operator, one record matches the query, because it is looking for a promotion date that is the same as either the REVIEW_DATE date or the SALARY_LAST_INCREASE date, which is the case with EMP_ID 2. The second query, using the not in operator, matches the record for EMP_ID 1, because the not in query predicate eliminates the record that has different dates within it.
-- First, drop and recreate the t_emp_info table from the earlier example.
DROP TABLE t_emp_info;
CREATE TABLE t_emp_info
(  emp_id                 NUMBER PRIMARY KEY,
   review_date            TY_REVIEW,
   salary_last_increase   TY_REVIEW,
   promotion_date         TY_REVIEW,
   hire_date              DATE,
   last_update_date       DATE )
NESTED TABLE review_date STORE AS emp_tab
NESTED TABLE salary_last_increase STORE AS sup_tab
NESTED TABLE promotion_date STORE AS promo_tab;

-- Insert 2 records.
INSERT INTO t_emp_info values (1,
TY_REVIEW (T_REVIEW_DATES(SYSDATE-365),
T_REVIEW_DATES(SYSDATE-300),
T_REVIEW_DATES(SYSDATE-270),
T_REVIEW_DATES(SYSDATE-200) ),
TY_REVIEW (T_REVIEW_DATES(SYSDATE-365),
T_REVIEW_DATES(SYSDATE-300) ),
TY_REVIEW (T_REVIEW_DATES(SYSDATE)),
SYSDATE, SYSDATE);

INSERT INTO t_emp_info VALUES (2,
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30),
T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ),
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30),
T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ),
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30),
T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ),
SYSDATE, SYSDATE);

-- Now, query using the in operator
COLUMN review_date FORMAT A20
COLUMN salary_last_increase FORMAT A20
SELECT emp_id FROM t_emp_info WHERE promotion_date IN (review_date, salary_last_increase);
    EMP_ID
----------
         2

SELECT emp_id FROM t_emp_info WHERE promotion_date NOT IN (review_date, salary_last_increase);
EMP_ID
----------
         1


Creating Temporary Tables with VARRAY Columns
In Oracle 10g, it is possible to include VARRAY columns in a temporary table. Here is an example of such an operation:
Create type ty_cust_info as object
( first_name  varchar2(30),  last_name  varchar2(30),
  address     varchar2(30),  city       varchar2(30),
  state       varchar2(2),  zip        varchar2(5) )
/
create type nt_cust_info as varray(10) of ty_cust_info;
/
create global temporary table t_cust_info
(  customer_information   nt_cust_info,
   created_date           date,
   last_update_date       date );

Note that nested tables are still not supported in temporary tables.


Changing the VARRAY Size
Oracle 10g introduces the ability to alter the size of a VARRAY element, as shown in this example:
create or replace type test as object (test_id number);
/
create or replace type ty_test as varray(20) of test;
/
create table t_test_info
(  test_id_no             ty_test,
   created_date           date,
   last_update_date       date );

insert into t_test_info values (ty_test(test(1)), sysdate, sysdate );

Now, to modify the VARRAY so that it will be able to hold more elements, simply issue an alter type command:
alter type ty_test modify limit 1000 cascade;

There are two options available when altering types (neither of which is a default value):
Finally, the size of a VARRAY can only be increased. Attempting to make it smaller will result in an error.


Defining the Tablespace of a Nested Table
In Oracle 10g it is possible to define a different tablespace for the storage table of a nested table.
By default, the storage table will be created where the parent table has been created. The tablespace parameter can be used in the store as clause to define a different tablespace, as shown below:
create or replace type test as object (test_id number);
/
create or replace type ty_test as table of test;
/
create table t_test_info
(  test_id_no             ty_test,
   created_date           date,
   last_update_date       date )
nested table test_id_no store as test_id_tab
(tablespace users);

It is also possible to alter the existing tablespace of the table of a nested table collection with the alter table command, as shown in this example:
Alter table test_id_no move tablespace new_users;


ANSI Support for Nested Tables and VARRAY Columns

Oracle 10g introduces ANSI-related functionality (equivalent to the array and multiset ANSI functionality) to nested tables and VARRAY columns. This includes:
•    Support for equality and inequality predicates
•    Support for in and not in operators
•    New operators

Support for Equality and Inequality Predicates
Nested tables in Oracle 10g support the use of equality and inequality predicates via the use of the equal and not equal operators. The result of the comparison is a Boolean value that indicates the success or failure of the comparison. Here is an example:

CREATE OR REPLACE TYPE t_review_dates AS OBJECT
(review_date  DATE,
 MAP MEMBER FUNCTION convert RETURN DATE);
/
CREATE OR REPLACE TYPE BODY t_review_dates AS
MAP MEMBER FUNCTION convert RETURN DATE IS
BEGIN
     RETURN review_date;
END;
END;
/
CREATE OR REPLACE TYPE ty_review AS TABLE OF t_review_dates;
/
CREATE TABLE t_emp_info
(  emp_id                 NUMBER PRIMARY KEY,
   review_date            TY_REVIEW,
   salary_last_increase   TY_REVIEW,
   hire_date              DATE,
   last_update_date       DATE )
NESTED TABLE review_date STORE AS emp_tab
NESTED TABLE salary_last_increase STORE AS sup_tab;

INSERT INTO t_emp_info VALUES (1,
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30),
T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ),
TY_REVIEW (T_REVIEW_DATES(SYSDATE-30), T_REVIEW_DATES(SYSDATE-90) ),
SYSDATE, SYSDATE);

INSERT INTO t_emp_info VALUES (2,
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30),
T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ),
TY_REVIEW (T_REVIEW_DATES(SYSDATE), T_REVIEW_DATES(SYSDATE-30),
T_REVIEW_DATES(SYSDATE-60), T_REVIEW_DATES(SYSDATE-90) ),
SYSDATE, SYSDATE);

Note that it was necessary to create a map method for the object, which is required. After the objects have been created and the data has been inserted, it is possible to query the collection items in the table, as shown below:
COLUMN review_date FORMAT a20
COLUMN salary_last_increase FORMAT a20
SQL> SELECT emp_id FROM t_emp_info WHERE review_date=salary_last_increase;

    EMP_ID
----------
         2


Improvements in Oracle 11g

PLS-00436 Restriction in FORALL Statements Removed
The PLS-00436 restriction has been removed, which means you can now reference the individual elements of a collection within the SET and WHERE clauses of a DML statement in a FORALL construct. To see this in action, create and populates a test table using the following code.
CREATE TABLE forall_test (
  id          NUMBER,
  amount      NUMBER,

  description VARCHAR2(50)
);
INSERT INTO forall_test VALUES (1, 10, 'ONE');
INSERT INTO forall_test VALUES (2, 20, 'TWO');
INSERT INTO forall_test VALUES (3, 30, 'THREE');
INSERT INTO forall_test VALUES (4, 40, 'FOUR');
INSERT INTO forall_test VALUES (5, 50, 'FIVE');
COMMIT;

The PL/SQL block below populates a collection with the existing data, amends the data in the collection, then updates the table with the amended data.
The final query displays the changed data in the table.
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
  l_tab t_forall_test_tab;
BEGIN
  -- Retrieve the existing data into a collection.
  SELECT * BULK COLLECT INTO l_tab
    FROM forall_test;

  -- Alter the data in the collection.
  FOR i IN l_tab.first .. l_tab.last LOOP
    l_tab(i).description := 'Description for ' || i;
  END LOOP;

  -- Update the table using the collection.
  FORALL i IN l_tab.first .. l_tab.last
    UPDATE forall_test SET description = l_tab(i).description,
                           amount = l_tab(i).amount * 1.10

      WHERE id = l_tab(i).id;
  COMMIT;
END;
/

SELECT * FROM forall_test;
        ID     AMOUNT DESCRIPTION
---------- ---------- -------------------
         1         11 Description for 1
         2         22 Description for 2
         3         33 Description for 3
         4         44 Description for 4
         5         55 Description for 5


In versions prior to 11g, the lines with the UPDATE would raise PLS-00436 because they reference attributes within the forall_test record variable.
In Oracle Database 10g and below, you would have had to define separate collection variables for the columns and in the process may have had to update several times, based on your exact logic.
In Oracle 11g, this is not only makes the program more readable and maintainable; but performant as well, since you may not need to issue multiple UPDATE statements.
Notice both the SET and WHERE clauses contain references to individual columns in the collection. This makes using bulk-binds for DML even easier as we no longer need to maintain multiple collections if we need to reference columns in the WHERE clause. It can also improve performance of updates, as previous versions required updates of the whole row using the ROW keyword, which included potentially unnecessary updates of primary key and foreign key columns.