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:

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

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.

The following scenarios generally indicate a need for collections:

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

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

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

• Use collections of records as the target in a BULK COLLECT INTO statement. You no longer need to fetch into a series of individual variables.
• Insert a row into a table using a record. You no longer need to list the individual fields in the record separately, matching them up with the columns in the table.
• Update a row in a table using a record. You can now take advantage of the special SET ROW syntax to update the entire row with the contents of a record with a minimum of typing.
• Use a record to retrieve information from the RETURNING clause of an UPDATE, DELETE or INSERT.

• 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)]

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

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

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

INSERT with RECORD bind

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;

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.

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.