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:

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

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

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

Another 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:

• In the declare section:
  • First we declare our very basic array of VARCHAR2(10).
  • Next we declare a record type with an embedded index by table.
  • So now we have a ragged record type. A single record with a dimensional name column.
  • Then we declare a new table based on the complex record type. This creates an array of ragged records.
  • And finally, we create the complex variable.
• Executeable section:
  • We populated the ssn and dob columns of the first record of the v_data variable.
  • Next we populated the first and second rows of the name table in the first row of the v_data variable.
  • Finally, we displayed the ssn of the first row of the v_data variable and then looped through the name table of the first row of the v_data variable.

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

3-Varrays
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 number;

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

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

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

Bulk Binding

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

An ‘out-bind’ occurs when values are passed from the SQL engine back to the host language.  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;

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

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

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

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

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

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

INSERT with RECORD bind

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

Then, with Oracle8i, we get rid of most of the context switches by moving to FORALL:
FORALL indx IN employee_ids.FIRST .. employee_ids.LAST
  UPDATE employees SET salary = salary * 1.1
   WHERE employee_id = employee_ids (indx);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Varray

• Use to preserve ordered list
• Use when working with a fixed set, with a known number of entries
• Use when you need to store in the database and operate on the Collection as a whole

Nested Table

• Use when working with an unbounded list that needs to increase dynamically
• Use when you need to store in the database and operate on elements individually

Associative Array

• Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.

Specific for BULK and FORALL:

• For FORALL, %FOUND and %NOTFOUND reflect the overall results, not the results of any individual statement, including the last (this contradicts Oracle documentation). In other words, if any one of the statements executed in the FORALL modified at least one row, %FOUND returns TRUE and %NOTFOUND returns FALSE.

• For FORALL, %ISOPEN always returns FALSE because the cursor is closed when the FORALL statement terminates.

• For FORALL, %ROWCOUNT returns the total number of rows affected by all the FORALL statements executed, not simply the last statement.

• For BULK COLLECT, %FOUND and %NOTFOUND always return NULL and %ISOPEN returns FALSE because the BULK COLLECT has completed the fetching and closed the cursor. %ROWCOUNT always returns NULL, since this attribute is only relevant for DML statements.

• The nth row in this pseudo index-by table stores the number of rows processed by the n th execution of the DML operation in the FORALL statement. If no rows are processed, then the value in %BULK_ROWCOUNT is set to 0.

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

• Even though it looks like an index-by table, you cannot apply any methods to it.

• %BULK_ROWCOUNT cannot be assigned to other collections. Also, it cannot be passed as a parameter to subprograms.

• The only rows defined for this pseudo index-by table are the same rows defined in the collection referenced in the FORALL statement.

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

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