Developer

Oracle Tip: Use collection enhancements of PL/SQL in Oracle9i Release 2

Oracle9i Release 2 offers powerful enhancements to PL/SQL collections. These enhancements make the syntax easier to read and help you avoid bugs caused by mismatched columns in the returning clause.

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.

Editor's Picks