1. Introduction

The %ROWTYPE attribute enables you declare a record that represents either a partial or full row of a database table or view. For every column of the full or partial row, the record has a field with the same data type and name. If the structure of the row changes, then the structure of the record changes accordingly.

The record fields do not inherit the initial values or constraints of the corresponding columns.

2. Declaring a record variable that represents a full row

To declare a record variable that represents a database table or view, use this following syntax:

variable_name table_or_view_name%ROWTYPE;

For every column of the table or view, the record has a field with the same name and data type. In the following example, we have declared a record variable emp_rec which represents a row of table employee.

DECLARE
	emp_rec employee%ROWTYPE;
BEGIN
	emp_rec.id := 1;
	emp_rec.name := 'John';
	emp_rec.joining_date := '01-Jan-2021';
	-- Print fields:
	DBMS_OUTPUT.PUT_LINE('id: ' || emp_rec.id);
	DBMS_OUTPUT.PUT_LINE('name: ' || emp_rec.name);
	DBMS_OUTPUT.PUT_LINE('joining_date: ' || emp_rec.joining_date);
END;

Output

id: 1
name: John
joining_date: 01-01-21

3. %ROWTYPE variable represents partial database table row

The syntax to declare a record variable that can represent a partial row of a database table or view is:

variable_name cursor%ROWTYPE;

A cursor is a pointer that points to a result of a query. Cursor must be either an explicit cursor or a strong cursor variable. For every column that the query selects, the record variable has a corresponding field. If the query selects partial columns of the table, then the variable represents only partial row. If the query selects all the columns of the table, then the variable represents the entire row.

In the following example, we have a variable emp_rec which represents the partial row of employee table.

DECLARE
    CURSOR cur IS
        SELECT name, joining_date
        FROM employee;
    
    emp_rec cur%ROWTYPE;
BEGIN
    emp_rec.name := 'John';
    emp_rec.joining_date := '01-Jan-2021';
    DBMS_OUTPUT.PUT_LINE('name: ' || emp_rec.name);
    DBMS_OUTPUT.PUT_LINE('joining date: ' || emp_rec.joining_date);
END;

4. %ROWTYPE variable does not inherit initial values or constraints

In the following example, we are assigning null to emp_rec.id. As you can observe from the table structure of employee table, id has not null constraint. So we can conclude that %ROWTYPE variable does not inherit initial values or constraints.

DECLARE
	emp_rec employee%ROWTYPE;
BEGIN
	emp_rec.id := null;
	emp_rec.name := 'John';
	emp_rec.joining_date := '01-Jan-2021';
	-- Print fields:
	DBMS_OUTPUT.PUT_LINE('id: ' || emp_rec.id);
	DBMS_OUTPUT.PUT_LINE('name: ' || emp_rec.name);
	DBMS_OUTPUT.PUT_LINE('joining_date: ' || emp_rec.joining_date);
END;

5. %ROWTYPE attribute and virtual columns

If you use the %ROWTYPE attribute to define a record variable which represents a full row of a table that has a virtual column, then you can not insert that record in the table. To insert such a record in the table, you must insert the individual record fields into the table, excluding the column.

For this example, we’ll use the following table structure:

CREATE TABLE employee(
id  NUMBER(5) NOT NULL,    
first_name  VARCHAR2(50),
last_name VARCHAR2(50), 
full_name GENERATED ALWAYS AS (first_name || ' ' || last_name),
joining_date DATE); 

Lets now try to insert record in the table.

DECLARE
    emp_rec employee%ROWTYPE;
BEGIN
    emp_rec.id := 1;
    emp_rec.first_name := 'John';
    emp_rec.last_name := 'Bond';
    emp_rec.joining_date := '01-Jan-2021';
    INSERT INTO employee VALUES emp_rec;
END;

This will throw error similar to the following:

ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at line 8
54013. 0000 -  "INSERT operation disallowed on virtual columns"
*Cause:    Attempted to insert values into a virtual column
*Action:   Re-issue the statment without providing values for a virtual column

To fix the problem, you must insert the individual record fields into the table.

DECLARE
    emp_rec employee%ROWTYPE;
BEGIN
    emp_rec.id := 1;
    emp_rec.first_name := 'John';
    emp_rec.last_name := 'Bond';
    emp_rec.joining_date := '01-Jan-2021';
    INSERT INTO employee (id, first_name, last_name, joining_date)
    VALUES (emp_rec.id, emp_rec.first_name, emp_rec.last_name, emp_rec.joining_date);
END;

6. %ROWTYPE attribute and invisible columns

If you use the %ROWTYPE attribute to define a record variable that represents a row of a table that has an invisible column.

• Case 1: If you define the record variable with a cursor, making the invisible column visible will not change the structure of the record variable.
• Case 2: If you define the record variable with a cursor and use a SELECT * INTO statement to assign values to the record, then making the invisible column visible does change the structure of the record.