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.

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.

3-Varrays
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
   OF element_type [NOT NULL];
Like nested tables, varrays can be both PL/SQL types and SQL types and therefore can take advantage of the many of the features listed above. The main differences with varrays in PL/SQL is that their maximum size must be specified when the type is declared.  It should be noted that both varray types as well as nested table types can define the column type of a SQL table.  In the former case, if the size of the varray type is 4000 bytes or less, it can be stored in-line in the data block along with other column values.  In contrast,  the column data for a nested table is stored in a system managed child table making it very similar to a normal parent/child table relationship. Because they have a shared type, PL/SQL nested table or varray variables can be used to atomically insert values into tables that use them. Apart from this capability, varrays are of less interest than nested tables to the PL/SQL developer because they have the restriction of an upper bound and most anything one can do in code with a varray, one can do with a nested table. Example:

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

Examples for nested tables and varrays

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

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

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

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

  i := someNumbers.first();
  while i is not null loop
    dbms_output.put_line (i || ': ' || someNumbers(i));
    i := someNumbers.next(i);
  end loop;
end;
/

• 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. Also, EXTEND and TRIM cannot be used with associative arrays. EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts, which are usually integers but can also be strings for associative arrays.
Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.
Some Examples:

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