Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

Part Number B10807-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF


Records are composite variables that can store data values of different types, similar to a struct type in C, C++, or Java. For more information, see "What Is a PL/SQL Record?".

In PL/SQL records are useful for holding data from table rows, or certain columns from table rows. For ease of maintenance, you can declare variables as table%ROWTYPE or cursor%ROWTYPE instead of creating new record types.


Description of record_type_definition.gif follows
Description of the illustration record_type_definition.gif

Keyword and Parameter Description


A datatype specifier. For the syntax of datatype, see "Constants and Variables".


A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions". When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.


A field in a user-defined record.


At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.


A user-defined record.


A user-defined record type that was defined using the datatype specifier RECORD.


Initializes fields to default values.

Usage Notes

You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package.

A record can be initialized in its declaration:

   TYPE TimeTyp IS RECORD ( seconds SMALLINT := 0, minutes SMALLINT := 0,
      hours SMALLINT := 0 );

You can use the %TYPE attribute to specify the datatype of a field. You can add the NOT NULL constraint to any field declaration to prevent the assigning of nulls to that field. Fields declared as NOT NULL must be initialized.

      deptno NUMBER(2) NOT NULL := 99,
      dname  departments.department_name%TYPE,
      loc    departments.location_id%TYPE,
      region regions%ROWTYPE
   dept_rec DeptRecTyp;
   dept_rec.dname := 'PURCHASING';

To reference individual fields in a record, you use dot notation. For example, you might assign a value to the field dname in the record dept_rec as follows:

dept_rec.dname := 'PURCHASING';

Instead of assigning values separately to each field in a record, you can assign values to all fields at once:

You can declare and reference nested records. That is, a record can be the component of another record:

   TYPE TimeTyp IS RECORD ( minutes SMALLINT, hours SMALLINT );
   TYPE MeetingTyp IS RECORD (
      day     DATE,
      time_of TimeTyp,             -- nested record
      dept    departments%ROWTYPE, -- nested record representing a table row
      place   VARCHAR2(20),
      purpose VARCHAR2(50) );
   meeting MeetingTyp;
   seminar MeetingTyp;
   seminar.time_of := meeting.time_of;

You can assign one nested record to another if they have the same datatype:

seminar.time_of := meeting.time_of;

Such assignments are allowed even if the containing records have different datatypes.

User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.

Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.

You can specify a RECORD type in the RETURN clause of a function spec. That allows the function to return a user-defined record of the same type. When calling a function that returns a user-defined record, use the following syntax to reference fields in the record:


To reference nested fields, use this syntax:


If the function takes no parameters, code an empty parameter list. The syntax follows:



The following example defines a RECORD type named DeptRecTyp, declares a record named dept_rec, then selects a row of values into the record:

   TYPE DeptRecTyp IS RECORD (
      deptno departments.department_id%TYPE,
      dname  departments.department_name%TYPE,
      loc    departments.location_id%TYPE );
   dept_rec DeptRecTyp;
   SELECT department_id, department_name, location_id INTO dept_rec
      FROM departments WHERE department_id = 20;

Related Topics

Collections, Functions, Packages, Procedures