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 dept.deptno%type;
type dname_coll is table of dept.dname%type;
type loc_coll is table of dept.loc%type;
select * bulk collect into deptno_list,dname_list,loc_list from dept;
In Release 2, this becomes:
type dept_coll is table of dept%rowtype;
select * bulk collect into dept_list from dept;
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:
/* populate dept_row . . . */
insert into dept values (dept_row.deptno,dept_row.dname,dept_row.loc);
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.
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:
insert into footab (foono,fooname) values (fooseq.nextval,'Foo')
returning foono,fooname into foorow.foono,foorow.fooname;
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.