What Is Record Data Type in Oracle?


Angela Bailey

The Record data type in Oracle is a composite data type that allows you to store multiple related values as a single unit. It is similar to a structure in programming languages like C or C++. A record is a collection of fields, each with its own name and data type.

To define a record data type in Oracle, you use the %ROWTYPE attribute. It allows you to create a record type that matches the structure of a table or cursor. This means that you can easily fetch rows from a table or query into a record variable without explicitly specifying each column.

Defining Record Data Type

To define a record data type, you can use the following syntax:

TYPE record_type_name IS RECORD
  field1 data_type1,
  field2 data_type2,

Here, record_type_name is the name you give to the record type, and field1, field2, . are the names of the fields within the record. Each field has its own data type.


TYPE employee_rec IS RECORD
  emp_id NUMBER,
  emp_name VARCHAR2(100),
  emp_salary NUMBER

In this example, we define a record type called employee_rec, which has three fields: emp_id (NUMBER), emp_name (VARCHAR2), and emp_salary (NUMBER).

Using Record Data Type

Once you have defined a record data type, you can use it to declare variables and fetch rows from tables or cursors. Here’s how you can do it:

  variable_name record_type_name;
  SELECT column1, column2, .
  INTO variable_name
  FROM table_name;

  -- You can now access the fields of the record using dot notation
  dbms_output.put_line('Employee ID: ' || variable_name.emp_id);
  dbms_output.put_line('Employee Name: ' || variable_name.emp_name);
  dbms_output.put_line('Employee Salary: ' || variable_name.emp_salary);

In this code snippet, we declare a variable of type record_type_name. Then we fetch the values from a table or cursor into that variable using the SELECT INTO statement. Finally, we can access the individual fields of the record using dot notation.

  emp employee_rec;
  SELECT emp_id, emp_name, emp_salary
  INTO emp
  FROM employees
  WHERE emp_id = 100;

  dbms_output.put_line('Employee ID: ' || emp.put_line('Employee Name: ' || emp.put_line('Employee Salary: ' || emp.emp_salary);

In this example, we declare a variable emp (employee_rec) and fetch the values of an employee with ID 100 from the employees table into that variable. We then print the values of the individual fields using dot notation.


The record data type in Oracle provides a way to store and manipulate related values as a single unit. It allows you to define record types that match the structure of tables or cursors, making it convenient to fetch rows into record variables.

By using dot notation, you can access the individual fields of a record and perform operations on them. Understanding how to use record data types can greatly simplify your code and improve its readability.

Discord Server - Web Server - Private Server - DNS Server - Object-Oriented Programming - Scripting - Data Types - Data Structures

Privacy Policy