Record Types in PL/SQL – %ROWTYPE, Explicit, Compound & Object Types Explained

Introduction to Record Types in PL/SQL

Record Types in PL/SQL allow developers to group multiple data items—similar to a row in a table—into a single composite variable. Whether you’re retrieving data, passing parameters, or organizing complex logic, PL/SQL record types offer flexibility and strong structure.

In this tutorial, we’ll cover:

%ROWTYPE – Implicit record types
Explicit RECORD types
Compound record types (nested)
Object-based record types using CREATE TYPE

1. Implicit Record Types Using %ROWTYPE

The %ROWTYPE attribute creates a record type that mirrors the structure of a table, view, or cursor. It’s useful when you want a record that reflects the entire row of a table.

Syntax

record_variable table_name%ROWTYPE;

Example

DECLARE
  individual individuals%ROWTYPE;
BEGIN
  individual.individual_id := 1;
  individual.first_name := 'John';
  individual.middle_initial := 'D';
  individual.last_name := 'Rockefeller';

  INSERT INTO individuals VALUES individual;
  COMMIT;
END;
/

Notes

Automatically inherits data types and column names.
Useful for quick access to all columns of a table.
Not reusable across programs unless the table exists.

2. Explicit Record Types in PL/SQL

Explicit record types are defined manually using the TYPE keyword. You can customize field names and types.

Syntax

TYPE record_type_name IS RECORD (
  field1 datatype,
  field2 datatype,
  ...
);

Example

DECLARE
  TYPE individual_record IS RECORD (
    individual_id  INTEGER,
    first_name     VARCHAR2(30),
    middle_initial individuals.middle_initial%TYPE,
    last_name      VARCHAR2(30)
  );

  individual individual_record;
BEGIN
  individual.first_name := 'John';
  individual.last_name := 'Morgan';

  INSERT INTO individuals VALUES (
    individual.individual_id,
    individual.first_name,
    individual.middle_initial,
    individual.last_name
  );
  COMMIT;
END;
/

Notes

%TYPE ensures synchronization with table columns.
Can be used even if the table doesn’t exist at compile time.
Supports better modular and flexible programming.

3. Compound Record Types (Nested Records)

Compound records allow nesting multiple record types inside a new record type. Useful in parent-child relationships, like individuals and their addresses.

Example

DECLARE
  TYPE individual_record IS RECORD (
    individual_id   INTEGER,
    first_name      VARCHAR2(30),
    middle_initial  VARCHAR2(1),
    last_name       VARCHAR2(30)
  );

  TYPE address_record IS RECORD (
    address_id      INTEGER,
    individual_id   INTEGER,
    street_address1 VARCHAR2(30),
    city            VARCHAR2(20),
    state           VARCHAR2(20),
    postal_code     VARCHAR2(20),
    country_code    VARCHAR2(10)
  );

  TYPE individual_address_record IS RECORD (
    individual individual_record,
    address address_record
  );

  individual_address individual_address_record;
BEGIN
  individual_address.individual.first_name := 'Ulysses';
  individual_address.address.city := 'New York';
  -- Insert statements
  COMMIT;
END;
/

Notes

Use dot-dot notation to access nested fields.
Highly structured but becomes complex when over-nested.

4. Object-Based Record Types in PL/SQL

You can create advanced, reusable object types in Oracle using CREATE TYPE. These act as record types and support OOP features like constructors and composition.

a. Define Object Types:

CREATE OR REPLACE TYPE individual_record AS OBJECT (
  individual_id INTEGER,
  first_name VARCHAR2(30),
  middle_initial VARCHAR2(1),
  last_name VARCHAR2(30),
  CONSTRUCTOR FUNCTION individual_record (
    individual_id INTEGER,
    first_name VARCHAR2,
    middle_initial VARCHAR2,
    last_name VARCHAR2
  ) RETURN SELF AS RESULT
) INSTANTIABLE NOT FINAL;
/

b. Define Type Body

CREATE OR REPLACE TYPE BODY individual_record AS
  CONSTRUCTOR FUNCTION individual_record (...) RETURN SELF AS RESULT IS
  BEGIN
    SELF.individual_id := individual_id;
    SELF.first_name := first_name;
    ...
    RETURN;
  END;
END;
/

c. Use in PL/SQL Block

DECLARE
  individual individual_record;
BEGIN
  individual := individual_record(4, 'Klaes', 'M', 'Roosevelt');
  INSERT INTO individuals VALUES (
    individual.individual_id,
    individual.first_name,
    individual.middle_initial,
    individual.last_name
  );
  COMMIT;
END;
/

Notes

Enables modular, reusable record types.
Supports constructor functions, encapsulation, and nesting.
Ideal for applications with complex data models.

Summary: Choosing the Right Record Type

Record Type	Flexibility	Reusability	Ease of Use	Best For
`%ROWTYPE`	Low	No	Very Easy	Quick full-row operations
Explicit `RECORD`	High	Medium	Moderate	Custom row-like structures
Compound Record	High	Medium	Complex	Parent-child record representation
Object Type	Very High	High	Complex	OOP-based, modular applications

Conclusion

Record Types in PL/SQL offer powerful ways to manage structured data. From simple %ROWTYPE to advanced object types with constructors, each type has its purpose in Oracle PL/SQL development. Use %ROWTYPE for quick data handling, explicit records for flexible design, compound records for nested structures, and object types when you need modularity and reuse.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.