PL/SQL Records

What are records?

  • Records are one of the data type in oracle data types.
  • It is a combination of different scalar data types like char, varchar, number etc and each scalar data types in the record holds a value.
  • A record can be visualized as a row of data.
  • It can contain all the contents of a row.
Declaring a record:

To declare a record, you must first define a composite data type; then declare a record for that type.
The General Syntax to define a composite data type is:

TYPE recordname IS RECORD
(col_name1 column_datatype,
col_name2 column_datatype, …);


  • recordname – it is the name of the composite type you want to define.
  • col_name1,col_name2, etc.,- it is the names the fields/columns within the record.
  • column_data type defines the scalar data type of the fields.

There are different ways available to declare the data type of the fields.

  1. Declaring the field in the same way as declare the fields while creating the table.
  2. If a field is based on a column from database table, you can define the field_type as follows:

col_name table_name.column_name%type;

By declaring the field data type in the above method, the data type of the column is dynamically applied to the field. This method is useful when we are altering the column specification of the table, because we do not need to change the code again. We can use also %type to declare variables and constants.

The General Syntax to declare a record of a uer-defined data type is:
record_name record_type_name;

The following code shows how to declare a record called employee_rec based on a user-defined type.
emp_id number(5),
emp_first_name varchar2(25),
emp_last_name emp.last_name%type,
emp_dept emp.dept%type

emp_salary emp.salary%type;
emp_rec emp_type;


Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License