This article originally appeared in the Oracle e-newsletter. Click

here to subscribe automatically.

Oracle9i Release 2 offers powerful
enhancements to PL/SQL collections, which can make code maintenance
across releases easier. For instance, you can use PL/SQL
collections in Release 2 as single arguments rather than using long
lists of individual fields.

Another benefit is that collections of records
can now be targets of the BULK COLLECT INTO clause. Before Release
2, you had to create a collection for each column being returned.
For instance:

    type deptno_coll is table of
    type dname_coll is table of
    type loc_coll is table of
    deptno_list deptno_coll;
    dname_list dname_coll;
    loc_list loc_coll;
    select * bulk collect into
deptno_list,dname_list,loc_list from dept;

In Release 2, this becomes:

    type dept_coll is table of
    dept_list dept_coll;
    select * bulk collect into dept_list from

Notice that there’s no longer a reference to
column names. If the DEPT table columns were modified in number or
name, you wouldn’t need to make any changes to the second code
example. This code is also easier to maintain than the older
version. However, the application requires a Release 2 database to
even compile.

You can now use records to collectively insert
a row into a table. Before Release 2, you had to insert a PL/SQL
record into a table and identify each column individually, like

    dept_row dept%rowtype;
    /* populate dept_row . . . */
    insert into dept values

In Release 2, you can simplify the insert
statement to this:

    insert into dept values

Records can be used to collectively update rows
in a table. Like the insert statement, you previously had to update
each column.

    update dept
       set deptno =
= dept_row.dname,
= dept_row.loc
     where deptno = dept_row.deptno;

With Release 2, you can update an entire row by
position within the record:

    update dept set row =
dept_row where deptno = dept_row.deptno;

(Notice that “SET ROW” is a new clause.)

You can use records to retrieve information
from the RETURNING clause of a DML statement. It’s often useful to
update some data and keep a local copy for further PL/SQL
manipulation. Before Release 2, you could use the RETURNING clause
to store the data somewhere after calculations and calls were
completed. In Release 2, you can store data in a record. For
example, here’s what you had to write prior to Release 2:

    foorow footab%rowtype;
    insert into footab (foono,fooname) values
      returning foono,fooname into

Now you can simply write the insert statement

    insert into footab
(foono,fooname) values (fooseq.nextval,’Foo’)
foono,fooname into foorow;

These enhancements make the syntax easier to
read and help you avoid bugs caused by mismatched columns in the
returning clause. But remember that these enhancements depend on
the physical position of each column in the table and the record.
If the position of a table’s columns changes, then the columns’
order changes. Therefore, it’s important to use ROWTYPE to match
columns with a database table rather than listing table columns

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and

software development. For more of his Oracle tips visit our Oracle Dev Tips