Collections and Bulk Binds
Introduction to Object Types and Records
Collections
Associative Arrays
Nested Tables
Varrays
Table Functions
Moving from Cursor-Loops to Collections
Using Collection Methods (Count, First, Last, etc)
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
When to use what
Returning Result Sets
Cursor Attributes
Improvements to Bulk Bind and Collections in 10g


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 TYPE food_t 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 food_t  := food_t ('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';
   IF INSTR (my_favorite_vegetable_rec.grown_in, 'yard') > 0 THEN
      --Pass an object as a parameter
      order_seeds (my_favorite_vegetable_rec);
   END IF;
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 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;
   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 Example:

DECLARE
-- Declare a basic table type type
TYPE a_char_data IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

-- Declare a complex record type
TYPE r_data IS RECORD (
ssn VARCHAR2(9) NOT NULL := -1,
name a_char_data, -- Notice the table_type used here
dob DATE );

-- Declare a index by table using the complex record type
TYPE a_multi IS TABLE OF r_data INDEX BY BINARY_INTEGER;

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

BEGIN
-- Set some values
v_data(1).ssn := '123456789';
v_data(1).dob := '01-JAN-1900';

-- Notice the second subscript
v_data(1).name(1) := 'Lewis';
v_data(1).name(2) := 'Joe';

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

Let me walk you through exactly what this example is doing:


Collections
There are three flavors of collection types, one, which is only available in PL/SQL (associative arrays), and two others (nested tables and varrays) that are shared between both languages.
The following scenarios generally indicate a need for collections:

1-Associative Arrays (ALSO CALLED PL/SQL Tables (oRACLE 7) OR INDEX_BY_TABLES (ORACLE 8)


TYPE type_name IS TABLE OF element_type [NOT NULL]

   INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;

Probably the most familiar collection type is the PL/SQL index-by table (called associative arrays since 9i Release 2).  The code block below is a typical use of an associative array.
DECLARE
  --This was the ONLY option before 9.2.0
  TYPE num_array IS TABLE OF NUMBER
                     INDEX BY BINARY_INTEGER;
  powers  num_array;
BEGIN
  FOR i IN 1..100 LOOP
    powers(i) := power(2, i);
  END LOOP;
END;
This 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 index by table does NOT have to be initialized.
In previous versions of Oracle, the only wat to declare an associative arrays was using the "index by binary_integer", that meant that the only index allowed on an associative array was the row number. These restrictions have now been lifted. You can now declare associative arrays to be indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2 and even anchored declarations of those types using %TYPE. All of the following statements are valid declarations of associative array types with integer indexes:
DECLARE
  TYPE array_t1 IS TABLE OF NUMBER
     INDEX BY BINARY_INTEGER;
  TYPE array_t2 IS TABLE OF NUMBER
     INDEX BY PLS_INTEGER;
  TYPE array_t3 IS TABLE OF NUMBER
     INDEX BY POSITIVE;
  TYPE array_t4 IS TABLE OF NUMBER
     INDEX BY NATURAL;
   TYPE array_t5 IS TABLE OF NUMBER
      INDEX BY VARCHAR2(64);

You can even use a user-defined subtype, thus:
DECLARE
   SUBTYPE my_integer IS PLS_INTEGER NOT NULL;
   TYPE array_t4 IS TABLE OF NUMBER
      INDEX BY my_integer;

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 schemalevel table:
SELECT * FROM translations;
ENGLISH       FRENCH
------------- ----------
computer      ordinateur
tree          arbre
book          livre
cabbage       chou
country       pays
vehicle       voiture
garlic        ail
apple         pomme
desk          éscritoire
furniture     meubles

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.


2-Nested Tables

Unlike associative arrays, the nested table data type is also a SQL data type.  A nested table is similar to an associative array in that there is no maximum size to the array however prior to assigning 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.  For many PL/SQL programs, these two added requirements make associative arrays a better choice for basic array functionality in code, however we will see that with nested tables a whole new set of options will open up that would not be possible with associative arrays.

DECLARE
  TYPE nest_tab_t IS TABLE OF NUMBER;
  --initialization of this type

  nt   nest_tab_t := nest_tab_t();  
BEGIN
  FOR i IN 1..100 LOOP
    nt.EXTEND;
    nt(i) := i;
  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.


3-Varrays
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
   OF element_type [NOT NULL];

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.  It should be noted that both varray types as well as nested table types can define the column type of a SQL table.  In the former case, if the size of the varray type is 4000 bytes or less, it can be stored in-line in the data block along with other column values.  In contrast,  the column data for a nested table is stored in a system managed child table making it very similar to a normal parent/child table relationship. Because they have a shared type, 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. Example:

declare
type v is varray(50) of varchar2(30);


Examples for nested tables and varrays

set serveroutput on
declare
  type nestab is table of number;
  type varr is varray(50) of varchar2(30);
  someNumbers nestab;
  someNames   varr;
  i           binary_integer;
begin
  someNumbers := tn(10,4,6,9,2,5);
  someNames   := v('Fred','Joe','Caesar');
  i:=3;
  if someNumbers(i) = 6 then
    dbms_output.put_line ('someNumbers(' || i || ')  = 6');
  else
    dbms_output.put_line ('someNumbers(' || i || ') <> 6');
  end if;

  someNumbers(i) := 7;
  if someNumbers(i) = 6 then
    dbms_output.put_line ('someNumbers(' || i || ')  = 6');
  else
    dbms_output.put_line ('someNumbers(' || i || ') <> 6');
  end if;

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

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

  i := someNumbers.first();
  while i is not null loop
    dbms_output.put_line (i || ': ' || someNumbers(i));
    i := someNumbers.next(i);
  end loop;
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;
 

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. Also, EXTEND and TRIM cannot be used with associative arrays. EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts, which are usually integers but can also be strings for associative arrays.
Only EXISTS can be applied to atomically null collections. 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. This method does not raise the SUBSCRIPT_OUTSIDE_LIMIT exception if used on an element that does not exists in 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
Returns the maximum number of elements that a varray can contain. Return NULL for Nested tables and Index-by tables
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
Returns the first or last subscript of a collection. If the collection is empty, FIRST and LAST return NULL
Declare
   TYPE    TYP_TAB IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(1);
   my_tab  TYP_TAB;
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 ) ;
End ;
/
First= A  Last= E


PRIOR(index) and NEXT(index)
Returns the previous or next subscript of the index element. If the index element has no predecessor, PRIOR(index) returns NULL. Likewise, if index has no successor, NEXT(index) returns NULL.
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

 
EXTEND[(n[,i])]
Used to extend a collection (add new elements)
·         EXTEND appends one null element to a collection.
·         EXTEND(n) appends n null elements to a collection.
·         EXTEND(n,i) appends n copies of the ith element to a collection.

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.


Main collection exceptions
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   nums NumList;  -- atomically null
BEGIN
   /* Assume execution continues despite the raised exceptions. */
   nums(1) := 1;            -- raises COLLECTION_IS_NULL       (1)
   nums := NumList(1,2);    -- initialize table
   nums(NULL) := 3          -- raises VALUE_ERROR              (2)
   nums(0) := 3;            -- raises SUBSCRIPT_OUTSIDE_LIMIT  (3)
   nums(3) := 3;            -- raises SUBSCRIPT_BEYOND_COUNT   (4)
   nums.DELETE(1);          -- delete element 1
   IF nums(1) = 1 THEN ...  -- raises NO_DATA_FOUND            (5)




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.  Oracle makes the distinction between values that are passed back via a RETURNING clause in SQL as opposed to when values are passed back by during a fetch operation but for the purpose of this paper I will refer to both of these operations as out-binds.
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 360352 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 returning 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

As we noted earlier, while it was possible before 9.2.0 to SELECT INTO a record, you could not BULK SELECT INTO a collection of records. The resulting code was often very tedious to write and not as efficient as would be desired. Suppose, for example, 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 that was added to the PL/SQL language in Oracle9i Release 1.


INSERT with RECORD bind

For years, one of our favorite "wish-we-had’s" was the ability to insert a row into a table using a record. Prior to Oracle9i Release 2, if we had put our data into a record, it would then be necessary to "explode" the record into its individual fields when performing the insert, as in:
DECLARE
   v_emprec employees%ROWTYPE := emp_util.get_one_row;
BEGIN
   INSERT INTO employees_retired ( employee_id, last_name, ...)
       VALUES ( v_emprec.employee_id, v_emprec.last_name, ...);
END;

This is very difficult coding. In Oracle9
i 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 .
 
Line(s)   Description
3-4        Declare an exception, enabling us to trap by name an error that occurs during the bulk insert
5-7        Declare an associative array, each row of which contains a record having the same structure as the employees table.
9 – 14     Load up the array with the information for all employees who are over 40 years of age
15-18
    The turbo-charged insert mechanism, FORALL, that includes a clause to allow FORALL to continue past errors and references a record (the specified row in the array)
20-26
    Typical code you would write to trap any error that was raised during the bulk insert and display or deal with each error individually.
 
Bulk INSERTing with a record.
--------------------------------
CREATE OR REPLACE PROCEDURE retire_them_now
IS
   bulk_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
   TYPE employees_t IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   retirees employees_t;
BEGIN
   FOR rec IN (SELECT *
                FROM employees
                WHERE hire_date < ADD_MONTHS (SYSDATE, -1 * 18 * 40))
   LOOP
      retirees (SQL%ROWCOUNT) := rec;
   END LOOP;
   FORALL indx IN retirees.FIRST .. retirees.LAST
      SAVE EXCEPTIONS
      INSERT INTO employees
         VALUES retirees (indx);
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;
 
Prior to Oracle9i Release 2, this same functionality would require listing the columns explicitly.


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 into a record from a DELETE statement.

-----------------------------------------------------------
DECLARE
   v_emprec employees%ROWTYPE;
BEGIN
   DELETE FROM employees
      WHERE employee_id = 100
      RETURNING employee_id, first_name, last_name, email, phone_number,
                hire_date, job_id, salary, commission_pct, manager_id, department_id
      INTO v_emprec;
   emp_util.show_one (v_emprec);
END;
 
You can also retrieve less than a full row of information by relying on programmer-defined record types, as this next example shows:
 
DECLARE
   TYPE key_info_rt IS RECORD (
           id NUMBER,
           nm VARCHAR2 (100) );
         v_emprec key_info_rt;
BEGIN
   DELETE FROM employees
         WHERE employee_id = 100
         RETURNING employee_id, first_name INTO v_emprec;
   ...
END;
 
Next, suppose that we execute a DELETE or UPDATE that modifies more than one row. In this case, we can use the RETURNING clause to obtain information from each of the individual rows modified by using BULK COLLECT to populate a collection of records! 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,