Records (%ROWTYPE & Custom)

A record in PL/SQL is a composite variable that groups related fields under one name — similar to a struct in C or a row in a table. Records eliminate the need to declare individual variables for every column in a row.

%ROWTYPE Records

%ROWTYPE creates a record matching the entire column structure of a table or view:

DECLARE
    v_emp   employees%ROWTYPE;
BEGIN
    SELECT *
    INTO   v_emp
    FROM   employees
    WHERE  employee_id = 100;

    -- Access individual fields with dot notation
    DBMS_OUTPUT.PUT_LINE('Name:   ' || v_emp.first_name || ' ' || v_emp.last_name);
    DBMS_OUTPUT.PUT_LINE('Salary: $' || v_emp.salary);
    DBMS_OUTPUT.PUT_LINE('Dept:   ' || v_emp.department_id);
    DBMS_OUTPUT.PUT_LINE('Hired:  ' || TO_CHAR(v_emp.hire_date, 'DD-MON-YYYY'));
END;
/

%ROWTYPE with Cursors

DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, last_name, salary
        FROM   employees
        WHERE  department_id = 90;

    -- Record matched to cursor structure (only selected columns)
    v_emp  c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': $' || v_emp.salary);
    END LOOP;
    CLOSE c_emp;
END;
/

User-Defined Record Types

When you need a record that does not match any single table — perhaps combining columns from multiple tables — define a custom record type:

DECLARE
    -- Define the type
    TYPE t_emp_summary IS RECORD (
        employee_id  employees.employee_id%TYPE,
        full_name    VARCHAR2(100),
        salary       employees.salary%TYPE,
        dept_name    departments.department_name%TYPE,
        job_title    jobs.job_title%TYPE,
        years_served NUMBER(5,1)
    );

    v_summary  t_emp_summary;
BEGIN
    SELECT e.employee_id,
           e.first_name || ' ' || e.last_name,
           e.salary,
           d.department_name,
           j.job_title,
           ROUND(MONTHS_BETWEEN(SYSDATE, e.hire_date) / 12, 1)
    INTO   v_summary
    FROM   employees   e
    JOIN   departments d ON d.department_id = e.department_id
    JOIN   jobs        j ON j.job_id        = e.job_id
    WHERE  e.employee_id = 101;

    DBMS_OUTPUT.PUT_LINE(v_summary.full_name);
    DBMS_OUTPUT.PUT_LINE('  Job:      ' || v_summary.job_title);
    DBMS_OUTPUT.PUT_LINE('  Dept:     ' || v_summary.dept_name);
    DBMS_OUTPUT.PUT_LINE('  Salary:   $' || v_summary.salary);
    DBMS_OUTPUT.PUT_LINE('  Seniority: ' || v_summary.years_served || ' yrs');
END;
/

Record Assignment

Records of the same type can be assigned wholesale:

DECLARE
    TYPE t_contact IS RECORD (
        name   VARCHAR2(100),
        email  VARCHAR2(100),
        phone  VARCHAR2(20)
    );

    v_original  t_contact;
    v_copy      t_contact;
BEGIN
    v_original.name  := 'Steven King';
    v_original.email := 'SKING@example.com';
    v_original.phone := '515.123.4567';

    -- Assign the entire record at once
    v_copy := v_original;
    v_copy.name := 'Copy of King';  -- only modifies v_copy

    DBMS_OUTPUT.PUT_LINE(v_original.name);  -- Steven King
    DBMS_OUTPUT.PUT_LINE(v_copy.name);       -- Copy of King
END;
/

Nested Records

Records can contain other records:

DECLARE
    TYPE t_address IS RECORD (
        street  VARCHAR2(100),
        city    VARCHAR2(50),
        country VARCHAR2(30) DEFAULT 'USA'
    );

    TYPE t_person IS RECORD (
        full_name  VARCHAR2(100),
        salary     NUMBER,
        address    t_address   -- nested record
    );

    v_person  t_person;
BEGIN
    v_person.full_name          := 'Jane Doe';
    v_person.salary             := 9500;
    v_person.address.street     := '100 Oracle Way';
    v_person.address.city       := 'Redwood Shores';
    v_person.address.country    := 'USA';

    DBMS_OUTPUT.PUT_LINE(
        v_person.full_name || ' lives in ' ||
        v_person.address.city || ', ' ||
        v_person.address.country
    );
END;
/

Records in Collections

Records are the typical element type for associative arrays and nested tables:

DECLARE
    TYPE t_emp_rec IS RECORD (
        emp_id  employees.employee_id%TYPE,
        name    VARCHAR2(100),
        salary  employees.salary%TYPE
    );

    TYPE t_emp_list IS TABLE OF t_emp_rec INDEX BY PLS_INTEGER;

    v_emps  t_emp_list;
    v_idx   PLS_INTEGER := 1;
BEGIN
    FOR r IN (SELECT employee_id,
                     first_name || ' ' || last_name AS full_name,
                     salary
              FROM   employees
              WHERE  department_id = 90) LOOP
        v_emps(v_idx).emp_id := r.employee_id;
        v_emps(v_idx).name   := r.full_name;
        v_emps(v_idx).salary := r.salary;
        v_idx := v_idx + 1;
    END LOOP;

    -- Print the collected records
    FOR i IN 1..v_emps.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            v_emps(i).name || ': $' || v_emps(i).salary
        );
    END LOOP;
END;
/

Returning Records from Functions

CREATE OR REPLACE FUNCTION get_emp_summary(p_emp_id IN NUMBER)
RETURN employees%ROWTYPE IS
    v_emp  employees%ROWTYPE;
BEGIN
    SELECT * INTO v_emp
    FROM   employees
    WHERE  employee_id = p_emp_id;

    RETURN v_emp;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee ' || p_emp_id || ' not found');
END get_emp_summary;
/

-- Usage
DECLARE
    v_emp  employees%ROWTYPE;
BEGIN
    v_emp := get_emp_summary(100);
    DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ', $' || v_emp.salary);
END;
/

Summary

• %ROWTYPE creates a record matching a table/view/cursor structure — adapts to schema changes automatically.
• User-defined record types (TYPE t IS RECORD (...)) combine columns from multiple sources.
• Records of the same type can be assigned wholesale with :=.
• Nested records are supported — access with chained dot notation (v_person.address.city).
• Records are the natural element type for collections of structured data.
• Functions can return record types, enabling clean single-call lookups.