Using RECORD binds for SQL in PL/SQL programs

Backround

A PL/SQL RECORD is the datatype that corresponds to a row in a schema-level table. It is the natural construct to use when manipulating table rows programatically, especially when a row is read (via SELECT, UPDATE...RETURNING or DELETE...RETURNING), manipulated programatically, and then recorded (via INSERT or UPDATE) in an another table with the same shape. It has these advantages over representing the column values in individual variables...

• the declaration is compact, using mytable%rowtype
   
• by using %rowtype, 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
   
• the record variable can be used as a formal and actual parameter to a procedure or function giving compact and guaranteed correct notation and allowing optimizations in the implementation of parameter passing.
   
• and of particular relevance in this discussion the SQL-PL/SQL interface allows a syntax which does not list the columns of the source/target table explicitly, again allowing for robust code which has a greater degree of schema-independence.

Status at Oracle9i Version 9.0.1

As the matrix below shows, the use of RECORDs in the SQL-PL/SQL interface was greatly restricted. The syntax was supported only for the the single row SELECT case (but for that in both static SQL and native dynamic SQL). Thus the advantages listed above were not yet capable of being realized.

Restrictions Removed in Version 9.2.0

A large number of restrictions have been lifted at Version 9.2.0. Full support is now provided for all flavors of SELECT.

And support is provided (with some minor restrictions -see below) for all static SQL flavors of INSERT, DELETE and UPDATE.

Restrictions Remaining in Version 9.2.0

• No use is supported of EXECUTE IMMEDIATE in connection with INSERT, UPDATE or DELETE. (It is supported for SELECT, as stated above.) In other words, RECORDs are not yet supported for DML using native dynamic SQL. 
• With DELETE and UPDATE...RETURNING the column-list must be written explicitly in the SQL statement. 
• In the bulk syntax case, you cannot reference fields of the in-bind table of records elsewhere in the SQL statement (esp eg in the where clause).

Guide to the Samples

These samples rely on the employees table. This is in the hr schema which is installed in the seed database. The script to create this schema is demo/schema/human_resources/hr_cre.sql under the Oracle Home directory.

This file, start.htm, and all the files it refers to, rec*.htm, are on the plsql/demo directory under the Oracle Home directory. First run rectab.sql and then recpkg.sql to set up the environment. Then you can copy the code samples straight from the browser to SQL*PLus.

There's a corresponding set of plain text files, rec*.sql, (only on the plsql/demo directory) which can be run with start or @ at the SQL*Plus prompt. The file rec_all.sql runs them all and cleans up after itself.

Navigation Matrix for Samples

What	static SQL	native dynamic SQL
SELECT
- single row	ok	ok
- many rows, single row syntax	ok	ok
- many rows, bulk syntax	PLS-00597 @9.0.1 illustrates LIMIT clause	PLS-00597 @9.0.1 illustrates LIMIT clause
INSERT
- single row	ORA-00906 @9.0.1	PLS-00457 @9.2.0
- many rows, bulk syntax	ORA-00906 @9.0.1 illustrates SAVE EXCEPTIONS	PLS-00457 @9.2.0
DELETE with RETURNING
- single row	ORA-00600 @9.0.1	PLS-00429 @9.2.0
- many rows, bulk syntax	PLS-00597 @9.0.1	PLS-00429 @9.2.0
UPDATE with RETURNING
- single row	ORA-00600 @9.0.1	PLS-00429 @9.2.0
- many rows, bulk syntax	PLS-00597 @9.0.1	PLS-00429 @9.2.0
UPDATE ... SET ROW = no need for RETURNING since you already have the RECORD before you do the SQL
- single row	ORA-00904 @9.0.1	PLS-00457 @9.2.0
- many rows, bulk syntax	PLS-00436 @9.2.0	PLS-00457 @9.2.0

Key
supported in 9.0.1
new in 9.2.0
partial support in in 9.2.0
not supported, 9.2.0 or earlier

Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 7-May-2002