Introduction to Object Types and Records

Very quick Summary

Index-by tables or Associative array

Syntax
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;
table_name type_name;

Example
TYPE l_tab_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
l_tab  l_tab_type;


Variable-size array or Varray

TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);


Nested table

Syntax
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;

Example
TYPE l_tab_type IS TABLE OF NUMBER;
l_tab  l_tab_type;
TYPE l_rec_type is RECORD  (
ID NUMBER,
NAME VARCHAR2(200)
);

TYPE l_tab_type IS TABLE OF l_rec_type;

l_tab l_tab_type;

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:

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:

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:

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;
/

• Associative Arrays (Index-by Tables), let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
• Nested Tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• 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.

• Repeated access to the same, static database information. If, during execution of your program (or during a session, since your collection can be declared as package data and thereby persist with all its rows for the entire session), you need to read the same data more than once, load it into a collection. Multiple scannings of the collection will be much more efficient than multiple executions of a SQL query.
• Management of program-only lists. You may build and manipulate lists of data that exist only within your program, never touching a database table. In this case, collections-and, specifically, associative arrays-will be the way to go.

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

• Nested tables are unbounded, whereas varrays have a maximum size.
• Individual elements can be deleted from a nested table, but not from a varray (on varrays you can delete just elements from the end). Therefore, nested tables can be sparse, whereas varrays are always dense.
• Varrays are stored by Oracle in-line (in the same tablespace), whereas nested table data is stored out-of-line in a store table, which is a system-generated database table associated with the nested table.
• When stored in the database, nested tables do not retain their ordering and subscripts, whereas varrays do.
• Nested tables support indexes while varrays do not.

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;
/

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.

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

• If you need a sparsely indexed list (for "data-smart" storage, for example), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable, but it is inefficient to do so for anything but the smallest collections.
• If your PL/SQL application requires negative subscripts, you have to use associative arrays.
• If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays.
• If you want to enforce a limit on the number of rows stored in a collection, use VARRAYs.
• If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle Database will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth.
• If you want to preserve the order of elements stored in the collection column and if your data set will be small, use a VARRAY. What is "small"? I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.
• Here are some other indications that a VARRAY would be appropriate: You don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.

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

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

Varray

• Use to preserve ordered list
• Use when working with a fixed set, with a known number of entries
• Use when you need to store in the database and operate on the Collection as a whole
• Access the collection inside SQL (table functions, columns in tables).
  
  

Nested Table

• Use when working with an unbounded list that needs to increase dynamically
• Use when you need to store in the database and operate on elements individually
• Access the collection inside SQL (table functions, columns in tables)
• Want to perform set operations
  

Associative Array

• Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.
• Work within PL/SQL code only
• Sparsely fill and manipulate the collection
• Take advantage of negative index values
  

• EXISTS
• COUNT
• LIMIT
• FIRST and LAST
• PRIOR and NEXT
• EXTEND
• TRIM
• DELETE

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:

• EXTEND appends one null element to a collection.
• EXTEND(x) appends 'x' null elements to a collection.
• EXTEND(n,i) appends n copies of the ith element to a collection.

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

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;

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:

--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;

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:

INSERT with RECORD bind

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;

Another Example

Specific for BULK and FORALL:

• For FORALL, %FOUND and %NOTFOUND reflect the overall results, not the results of an individual statement, including the last. In other words, if any one of the statements executed in the FORALL modified at least one row, %FOUND returns TRUE and %NOTFOUND returns FALSE.
• For FORALL, %ISOPEN always returns FALSE because the cursor is closed when the FORALL statement terminates.
• For FORALL, %ROWCOUNT returns the total number of rows affected by all the FORALL statements executed, not simply the last statement.
• For BULK COLLECT, %FOUND and %NOTFOUND always return NULL and %ISOPEN returns FALSE because the BULK COLLECT has completed the fetching and closed the cursor. %ROWCOUNT always returns NULL, since this attribute is only relevant for DML statements.
• The nth row in this pseudo index-by table stores the number of rows processed by the n th execution of the DML operation in the FORALL statement. If no rows are processed, then the value in %BULK_ROWCOUNT is set to 0.

• Even though it looks like an index-by table, you cannot apply any methods to it.
• %BULK_ROWCOUNT cannot be assigned to other collections. Also, it cannot be passed as a parameter to subprograms.
• The only rows defined for this pseudo index-by table are the same rows defined in the collection referenced in the FORALL statement.

• invalidate - Invalidates all dependent objects when the operation takes place and does so without any checks. This can be dangerous, as it is possible to inadvertently drop an attribute that is critical, so use invalidate carefully.
• cascade - Propagates the change to all dependent types and tables, and an error will occur if any errors are found.

Associative Array Example

This example uses an associative array based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

Nested Table Example

This example uses a nested table based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp%ROWTYPE;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

Varray Example

This example uses a varray based on a %ROWTYPE defined in the package specification.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS VARRAY(10) OF emp%ROWTYPE;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

Supported Types

The previous examples all use a %ROWTYPE of a schema table for the collection type. It is also possible to use %TYPE, a scalar and a local record type.

The following example uses an associated array based on a %TYPE. Notice the column is referenced as COLUMN_VALUE.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF emp.empno%TYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.column_value);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

The following example uses an associated array based on a scalar. Notice the column is referenced as COLUMN_VALUE.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_tab IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.column_value);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782
7839
7934

PL/SQL procedure successfully completed.

SQL>

The following example uses an associated array based on a local record type.

CREATE OR REPLACE PACKAGE test_api AS
  TYPE t_row IS RECORD (
    empno NUMBER(4),
    ename VARCHAR2(10)
  );
  
  TYPE t_tab IS TABLE OF t_row
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT empno, ename
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/

SET SERVEROUTPUT ON
EXEC test_api.test1;
Loop Through Collection
7782 : CLARK
7839 : KING
7934 : MILLER

PL/SQL procedure successfully completed.

SQL>

Location of Type Definition

For this new functionality to work, the type must be defined in package specification. The examples below show how alternate locations fail.

The following example moves the type definition to the package body, which results in a compilation failure.

CREATE OR REPLACE PACKAGE test_api AS
  PROCEDURE test1;
END;
/

CREATE OR REPLACE PACKAGE BODY test_api AS

  TYPE t_tab IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  PROCEDURE test1 IS
    l_tab1 t_tab;
  BEGIN
    SELECT *
    BULK COLLECT INTO l_tab1
    FROM   emp
    WHERE  deptno = 10;

    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
                    FROM   TABLE(l_tab1))
    LOOP
      DBMS_OUTPUT.put_line(cur_rec.empno || ' : ' || cur_rec.ename);
    END LOOP;
  END;

END;
/
Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY TEST_API:

LINE/COL ERROR
-------- -----------------------------------------------------------------
15/21    PL/SQL: SQL Statement ignored
16/28    PL/SQL: ORA-22905: cannot access rows from a non-nested table
         item

16/34    PLS-00382: expression is of wrong type
18/7     PL/SQL: Statement ignored
18/28    PLS-00364: loop index variable 'CUR_REC' use is invalid