Record Types in PL/SQL as Formal Parameters

Record Types in PL/SQL play a vital role when you need to pass structured data between procedures, functions, or cursors. Using record types as formal parameters allows developers to write modular, reusable, and efficient PL/SQL code by grouping related fields into a single parameter.

This guide explains how to define, use, and pass PL/SQL record types—including compound records—as formal parameters. It also provides examples and best practices to help you implement them effectively.

What Are Record Types in PL/SQL?

In PL/SQL, a record type is a composite data structure that can group multiple fields (of different types) under a single variable. These fields can correspond to table columns, or they can be explicitly defined. You can use:

%ROWTYPE (implicit)
RECORD (explicit)
OBJECT types (object-oriented)

To pass these records as formal parameters, the record type must be defined in the declaration section (or globally via packages or object types) before being used in any subprogram.

Example: Using Record Types in Procedures

Here's a complete PL/SQL block that shows how to use record types in PL/SQL as formal parameters.

DECLARE
  -- Define individual record type
  TYPE individual_record IS RECORD (
    individual_id   INTEGER,
    first_name      VARCHAR2(30),
    middle_initial  VARCHAR2(1),
    last_name       VARCHAR2(30)
  );

  -- Define address record type
  TYPE address_record IS RECORD (
    address_id      INTEGER,
    individual_id   INTEGER,
    street_address1 VARCHAR2(30),
    street_address2 VARCHAR2(30),
    street_address3 VARCHAR2(30),
    city            VARCHAR2(20),
    state           VARCHAR2(20),
    postal_code     VARCHAR2(20),
    country_code    VARCHAR2(10)
  );

  -- Define compound record type
  TYPE individual_address_record IS RECORD (
    individual individual_record,
    address address_record
  );

  -- Declare a variable of compound record type
  individual_address individual_address_record;

  -- Procedure to insert into individuals
  PROCEDURE insert_individual(individual_in individual_record) IS
  BEGIN
    INSERT INTO individuals VALUES (
      individual_in.individual_id,
      individual_in.first_name,
      individual_in.middle_initial,
      individual_in.last_name
    );
  END;

  -- Procedure to insert into addresses
  PROCEDURE insert_address(address_in address_record) IS
  BEGIN
    INSERT INTO addresses VALUES (
      address_in.address_id,
      address_in.individual_id,
      address_in.street_address1,
      address_in.street_address2,
      address_in.street_address3,
      address_in.city,
      address_in.state,
      address_in.postal_code,
      address_in.country_code
    );
  END;

BEGIN
  -- Initialize individual values
  individual_address.individual.individual_id := 6;
  individual_address.individual.first_name := 'Rudolph';
  individual_address.individual.middle_initial := '';
  individual_address.individual.last_name := 'Guiliani';

  -- Initialize address values
  individual_address.address.address_id := 3;
  individual_address.address.individual_id := 6;
  individual_address.address.street_address1 := '89th St';
  individual_address.address.street_address2 := '';
  individual_address.address.street_address3 := '';
  individual_address.address.city := 'New York City';
  individual_address.address.state := 'NY';
  individual_address.address.postal_code := '10028';
  individual_address.address.country_code := 'USA';

  -- Savepoint for transaction control
  SAVEPOINT addressbook;

  -- Call procedures
  insert_individual(individual_address.individual);
  insert_address(individual_address.address);

  -- Commit the transaction
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO addressbook;
    DBMS_OUTPUT.PUT_LINE('Error occurred. Rolled back.');
END;
/

Working with Records

To use records efficiently:

Declare a record type or use %ROWTYPE
Declare a variable of that record type
Assign values using SQL queries or directly in code
Access fields using dot notation (record_name.field_name)

Why Use Record Types as Parameters in PL/SQL?

Improved Code Readability

You can pass multiple related values using a single variable.

Cleaner Subprogram Signatures

Avoid long parameter lists and make subprograms easier to maintain.

Easier Refactoring

When table structure changes, you only need to update the record type (if %TYPE or %ROWTYPE is used).

Best Practices for Record Types in PL/SQL

Use %TYPE to keep field types in sync with table columns.
Use compound records when handling parent-child relationships (e.g., customer and address).
Keep nesting shallow — one or two levels are manageable.
Always use transaction control with savepoints when inserting related data.
Include exception handling for rollback scenarios.

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