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:

Declare
    type deptno_coll is table of
dept.deptno%type;
    type dname_coll is table of
dept.dname%type;
    type loc_coll is table of
dept.loc%type;
    deptno_list deptno_coll;
    dname_list dname_coll;
    loc_list loc_coll;
begin
    select * bulk collect into
deptno_list,dname_list,loc_list from dept;
end;

In Release 2, this becomes:

Declare
    type dept_coll is table of
dept%rowtype;
    dept_list dept_coll;
begin
    select * bulk collect into dept_list from
dept;
end;

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

Declare
    dept_row dept%rowtype;
begin
    /* populate dept_row . . . */
    insert into dept values
(dept_row.deptno,dept_row.dname,dept_row.loc);
end;

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

    insert into dept values
dept_row;

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.deptno,
            dname
= dept_row.dname,
              loc
= 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:

Declare
    foorow footab%rowtype;
begin
    insert into footab (foono,fooname) values
(fooseq.nextval,’Foo’)
      returning foono,fooname into
foorow.foono,foorow.fooname;
end;

Now you can simply write the insert statement
as:

    insert into footab
(foono,fooname) values (fooseq.nextval,’Foo’)
        returning
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
explicitly.

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

Library.