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


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. In contrast to a scalar datatype like NUMBER, a record 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. Records are very handy constructs for PL/SQL developers. The easiest way to define a record is by using the %ROWTYPE syntax in your declaration. For example, the following statement:

DECLARE
    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 should also be aware that while you can assign one record to another, you couldn’t perform comparisons or computations on records. Neither of these statements will compile:

BEGIN
   IF bestseller = required_reading THEN ...
BEGIN
    left_to_read := bestseller - required_reading;

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)

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. There were several drawbacks to this scheme. First, it required reliance on an outmoded datatype, since BINARY_INTEGER has since been superseded by PLS_INTEGER as a more efficient integer datatype. Second, it meant that if the list being manipulated had a non-integer key, the developer had to write some very complex and/or compute-intensive logic (namely, to perform full collection scans or create alternative indexes via hashing) to take advantage of collections.
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;
BEGIN
...
END;

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;

--Another Example
create or replace TYPE array_t IS TABLE OF number INDEX BY varchar2(64);  --New in 9i

A welcome addition in 9i Release 2 is the ability to use the VARCHAR2 data type as an index key.  Similar to associative arrays in Perl and Awk, or Hashtables in Java, this data type enables the look-up of a value using a VARCHAR2.  Many existing PL/SQL routines can benefit from this capability.  Here are some examples
of such declarations:
DECLARE
   TYPE array_t1 IS TABLE OF NUMBER
      INDEX BY VARCHAR2(64);
   TYPE array_t3 IS TABLE OF NUMBER
      INDEX BY VARCHAR2(32767);
   TYPE array_t4 IS TABLE OF NUMBER
      INDEX BY employee.last_name%TYPE;
BEGIN
.....


Example:

DECLARE
 idx VARCHAR2(64);
 TYPE population_type IS TABLE OF NUMBER INDEX BY idx%TYPE;

 country_population population_type;
 continent_population population_type;

 howmany PLS_INTEGER;
BEGIN
  country_population('Norway') := 4000000;
  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 = ' || howmany);

  idx := continent_population.FIRST;
  DBMS_OUTPUT.PUT_LINE ('FIRST row = ' || idx);
  DBMS_OUTPUT.PUT_LINE ('FIRST value = ' || continent_population(idx));

  idx := continent_population.LAST;
  DBMS_OUTPUT.PUT_LINE ('LAST row = ' || idx);
  DBMS_OUTPUT.PUT_LINE ('LAST value = ' || continent_population(idx));

  idx := country_population.FIRST;
  WHILE idx IS NOT NULL
  LOOP
     DBMS_OUTPUT.PUT_LINE ( idx || ' = ' || country_population(idx) );
     idx := country_population.NEXT(idx);
  END LOOP;
 END;

Example: Populate an index by varchar2 table with 500,000 tuples and a database table with the same data.

drop table table1;
create table table1 (
idx varchar2(4000),
data varchar2(4000) );

create or replace procedure P is
--This is Using the new feature of Oracle 9i index by varchar2(4000)
type text_tab_t is table of varchar2(4000) index by varchar2(4000);
v_text_tab text_tab_t;
v_index table1.idx%type;
v_data table1.data%type;
t_start integer;
t_end integer;
c_nof_rows constant pls_integer := 500000;

procedure Show_Elapsed_Time ( p_start in number, p_end in number ) is
begin
Dbms_Output.Put_Line ( Lpad ( To_Char ( ((p_end-p_start)/100), 9999.9 ), 15 ) || ' seconds' );
end Show_Elapsed_Time;

begin
-- Database table Method --------------------------
t_start := Dbms_Utility.Get_Time;
for j in 1..c_nof_rows
loop
v_index := To_Char(j) || ' suffix';
insert into table1( idx, data )
values (v_index, 'data for ' || v_index );
end loop;
execute immediate ( 'create unique index t_idx on table1( idx )' );

for j in ( select idx, data from t )
loop
v_index := j.idx; v_data := j.data;
-- Dbms_Output.Put_Line ( Rpad(v_index,20) || Rpad(v_data,20) );
end loop;

t_end := Dbms_Utility.Get_Time;
Dbms_Output.Put_Line ('For Database Table Method');
Show_Elapsed_Time ( t_start, t_end );

-- PL/SQL index by varchar2 table Method -------------
t_start := Dbms_Utility.Get_Time;
for j in 1..c_nof_rows
loop
v_index := To_Char(j) || ' suffix';
v_text_tab( v_index ) := 'data for ' || v_index;
end loop;

v_index := v_text_tab.First;
while v_index is not null
loop
-- Dbms_Output.Put_Line ( Rpad(v_index,20) || Rpad(v_text_tab(v_index),20) );
v_index := v_text_tab.Next(v_index);
end loop;
t_end := Dbms_Utility.Get_Time;
Dbms_Output.Put_Line ('For index by varchar2 Table Method');
Show_Elapsed_Time ( t_start, t_end );
end P;
/
exec p
For Database Table Method          = 297.2 seconds
For index by varchar2 Table Method = 17.55 seconds

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
The last collection type to be discussed is the varray. 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 varying array(50) of varchar2(30);


Examples for nested tables and varrays

set serveroutput on
declare
  type nestab is table of number;
  type varr is varying array(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;
 


Oracle9i Release 2 Record Improvements

Oracle now let you do any of the following with static SQL (i.e. SQL statements that are fully specified at the time your code is compiled):
Some restrictions do remain at Version 9.2.0 for records in SQL, including:
You cannot use the EXECUTE IMMEDIATE statement (Native Dynamic SQL) in connection with record-based INSERT, UPDATE or DELETE statements. (It is supported for SELECT, as stated above)
With DELETE and UPDATE...RETURNING, the column-list must be written explicitly in the SQL statement.
In the bulk syntax case, you cannot reference fields of the in-bind table of records elsewhere in the SQL statement (especially in the where clause).
 
Let's explore this great new functionality with a series of examples, all of which will rely on the employees table, defined in the hr schema that is installed in the seed database. The script to create this schema is demo/schema/human_resources/hr_cre.sql under the Oracle Home directory.
 
The samples also rely on common features such as an index-by-*_integer table, records of employees%rowtype and a procedure to show the rows of such a table. These are implemented in the Emp_Utl package.


Full example moving from Cursor-Loops to Collections
The original method for loading one table into another table was:

CREATE TABLE t1 AS SELECT *
   FROM all_objects
  WHERE 1=0;

CREATE OR REPLACE PROCEDURE test_proc IS
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

Ignoring the initial table creation script, the test_proc 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 (her eis 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
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


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.


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 constrain 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, introduced in Oracle 8i, 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.
The above example is only for the purpose of demonstrating coding techniques.  The same logic could be accomplished totally in SQL using an INSERT AS SELECT statement.   Doing this in pure SQL would be faster yet since no host binds or procedural execution would be required at all.



Example Using the BULK COLLECT clause to collect few rows into a collection

DECLARE  
   TYPE NumTab IS TABLE OF emp.empno%TYPE;  
   TYPE NameTab IS TABLE OF emp.ename%TYPE;  
   enums NumTab;   -- no need to initialize  
   names NameTab;
BEGIN  
   SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;  
   FOR i in enums.FIRST..enums.LAST LOOP     
      DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' || names(i));  
   END LOOP;
END;
/

--The following is an example for the FETCH INTO clause

DECLARE
   TYPE NameTab IS TABLE OF emp.ename%TYPE;
   TYPE SalTab IS TABLE OF emp.sal%TYPE;  
   names NameTab;  
   sals SalTab;  
   CURSOR c1 IS SELECT ename, sal FROM emp;
BEGIN 
  OPEN c1;  
  FETCH c1 BULK COLLECT INTO names, sals;  
     FOR i IN names.FIRST..names.LAST LOOP     
        DBMS_OUTPUT.PUT_LINE(names(i) || ' ' || sals(i));  
     END LOOP;  
  CLOSE c1;
END;
/  


 
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,
                  job_id, salary, commission_pct, manager_id, department_id
            BULK COLLECT INTO v_emprecs;
    emp_util.show_all (v_emprecs);
END;
 


Bulk Binding in Native Dynamic SQL
Let's look at the following example:
BEGIN
   SELECT employee_id
     BULK COLLECT INTO employee_ids
     FROM employees
    WHERE salary < 3000;
   ...
END;

What if you want to execute this same query (and then process the rows) for one of any number of different employee tables, segregated by location?
In Oracle 9i, we can take the preceding query and transform it into a dynamic SQL query that will handle this additional complexity. There are many application implementation situations that require dynamic SQL.
CREATE OR REPLACE PROCEDURE
   process_employees (loc_in IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
     'SELECT employee_id FROM ' || loc_in || '_employees'
     BULK COLLECT INTO employee_ids;
   ...
END;


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, with Oracle 9i only, also employ a 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
In Oracle8i, FORALL was wonderful for rapid processing of bulk DML statements. One problem with it, however, is that you lose some of the granularity in exception handling that you help with row-by-row processing. Suppose, for example, that we want to load a whole bunch of words into a vocabulary table. We can do it very efficiently as follows:
BEGIN
   FORALL indx IN words.FIRST .. words.LAST
    INSERT INTO vocabulary text)
        VALUES (words (indx));
END;

If, however, an error occurred in any single INSERT, the entire FORALL statement would fail. With Oracle 9i, you can now do this with both static and dynamic FORALL SQL statements, 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:
Example Using the SQL%BULK_COLLECTIONS pseudo-collection.

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).


Multi-Dimensional Arrays
Another new feature that has been provided in Oracle 9i, Release 1 is the long awaited capability of multi-dimensional arrays, which Oracle has implemented as collections of collections.  Multi-dimensional arrays have been around in most programming languages for a long time and are now available in PL/SQL.  Technically, all collection types support only a single dimension, however by now 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.


Package Variables
PL/SQL packages offer a number of advantages over stand-alone code or anonymous blocks.   While some of the advantages come from the familiar ability to organize code into logical collections of related procedures and functions, an often ignored aspect is the use of package-level variables and package initialization sections. 
A package variable is essentially a variable that is declared globally, typically at the top of the package body outside of any procedure definition.  Once set, a package variable will maintain its state for the life of the session, as opposed to variables local to procedure definitions that only exist for the duration of the procedure call.  Every package body implementation can optionally include a block of code at the end of the specification referred to as the initialization section.  This code will run only once – when the package is first referenced and is normally used to initialize any package variables that are used.
The use of package variables is a powerful technique to speed up SQL statements that has been used for many years.  Consider a procedure that is called repeatedly in a busy OLTP database that inserts a row into a table.  One of the values passed to the procedure is a key that is used to first lookup another value, which in turn, is used in the insert.  Most PL/SQL code will first execute a select statement for the row of interest, binding the value to a local variable that will then be used on the subsequent insert statement.  A unique indexed table look-up is relatively quick but if an application is being driven hard, the cost can be more than one would expect.   Referencing a pre-initialized array value is approximately 20 times faster than an index based table look-up, making this technique a real time saver for intensive operations.
Using package variables, the lookup can be avoided entirely by first initializing an array with the desired values.  For example, in a marketing application a procedure is passed a zip code as an argument and must perform an insert into a fact table that requires the city name as a denormalized value in the table.  Assume also that there is a lookup table, with a numeric zip code as the primary key as well as the city and state information that the zip code maps to.  Code to avoid all of the look-ups would look like this.
CREATE OR REPLACE PACKAGE BODY direct_mkt  AS
  TYPE zip_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  zip_deref    zip_array;
PROCEDURE do_insert(f_name VARCHAR2, l_name VARCHAR2, zip NUMBER)IS
  BEGIN
    INSERT INTO user_data (f_nm, l_nm, city_nm)
    VALUES (f_name, l_name, zip_deref(zip));
    COMMIT;
  END;
 
--  Package initialization section.
BEGIN
  FOR rec IN (SELECT zip_code, city FROM dma) LOOP
    zip_deref(rec.zip_code) := rec.city;
  END LOOP;
END;

 
Until Oracle 9i Release 2, this technique couldn’t be used if the look-up key was non-numeric or composite, but now with the combination of VARCHAR2 associative arrays and multi-dimensional arrays, it can be extended to almost any look-up table of a reasonable size.  For example consider a table of individuals mapped to email addresses that has as it’s primary key, a composite index of numeric user id and an email address that they are associated with.  The following code shows how to implement this.
 
CREATE OR REPLACE PACKAGE BODY email_push  AS
  TYPE email_array    IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
  TYPE usermail_array IS TABLE OF email_array  INDEX BY BINARY_INTEGER;
  user_emails    usermail_array;
 
  FUNCTION lookup(p_userid  NUMBER, p_email  VARCHAR2) RETURN VARCHAR2  IS
  BEGIN
    RETURN user_emails(p_userid)(p_email);
  END;
 
BEGIN
  FOR rec IN (SELECT user_id, email, l_name FROM user_emails) LOOP
    user_emails(rec.user_id)(rec.email) := rec.l_name;
  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
--

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

Varray

Nested Table

Associative Array


Returning Result Sets
There are three general approaches for returning results sets: returning cursors, returning collections, or using table functions.

1-Cursors Variables

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

2-Returning Collections

Another approach to returning result sets is to write a function that explicitly returns a PL/SQL collection type.  The method has been around for years but it is really best suited for use by calling PL/SQL programs.  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.  A function that returns a shared collection type is shown below.  The type, email_demo_nt_t, was defined earlier in this paper.
 
FUNCTION get_email_demo(p_email_id NUMBER)  RETURN email_demo_nt_t  IS
  eml_dmo       email_demo_nt_t;
BEGIN
  SELECT email_demo_obj_t(email_id, demo_id, value)
    BULK COLLECT INTO eml_dmo
    FROM email_demographic
    WHERE email_id = p_email_id;
 
  /*  Apply some business logic and return the result set. */
  RETURN eml_dmo;
END;

 
Note that when the BULK COLLECT feature is used, it is not necessary to initialize or extend a nested table because Oracle does it automatically.  But it is necessary to call the constructor function for the object type in the query itself to be able to fetch scalar column values into the nested table of objects.  This would not be necessary if fetching from a column of that object type. 

3-Table Functions

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: