Discuss Composite Datatypes in Oracle PL/SQL

In PL/SQL, a composite datatype is any datatype whose value is made of internal components. Oracle groups PL/SQL composite datatypes into two kinds: records (row-shaped structures) and collections (multi-value lists such as arrays, sets, or maps). In this module, you will learn how records and collections are declared, populated, and used safely and efficiently in modern Oracle workloads—especially when PL/SQL code must move data between SQL queries, procedural logic, and application-facing APIs.

Module objectives

PL/SQL datatype categories

Every constant, variable, and parameter in a PL/SQL block has a datatype. PL/SQL includes predefined datatypes and subtypes, and it also lets you define your own types. The categories below are useful because they tell you whether a value is a single atomic value, a multi-part structure, or a pointer-like reference.

Predefined PL/SQL datatype categories

Collections: three options, three behaviors

PL/SQL collections are the modern replacement for older “PL/SQL table” terminology. Think of a collection as “one variable, many values.” Oracle provides three collection types, and choosing the right one is mostly about bounds, density, and whether SQL can see it.

Characteristics of PL/SQL collection types

Records: row-shaped structures for clean APIs

A record groups related fields into a single variable. Records are ideal when you want “a row” to move through your code as one unit: fetch once, validate once, pass once. In practice, records show up everywhere: wrapping query results, structuring procedure parameters, and staging data before a DML operation.

%TYPE and %ROWTYPE keep your code aligned to schema changes

Two attributes dramatically reduce maintenance:

• %TYPE anchors a variable to a specific column’s datatype (great for single fields).
• %ROWTYPE anchors a record to an entire table or cursor row shape (great for row-level work).

These attributes help your PL/SQL stay resilient when a column precision changes, a VARCHAR2 length is adjusted, or a query’s select list is updated.

Composite types and performance: bulk processing patterns

Composite datatypes are not just about code organization—they are also a major performance tool. When you need to process many rows, PL/SQL collections allow you to reduce context switching between SQL and PL/SQL using BULK COLLECT (fetch many rows at once) and FORALL (apply DML for many elements at once). This is one of the most reliable ways to speed up row-by-row loops.

Oracle 23ai-era context: composites still matter

Oracle Database 23ai expands what you can do with SQL and modern application patterns (for example, JSON-relational duality views), but PL/SQL remains the place where you enforce business rules, build database APIs, and orchestrate multi-step logic. Composite datatypes are the “data containers” that make this clean: records for row-shaped values, and collections for batching, caching, and passing sets of values across program boundaries.

Also, as Oracle continues improving the SQL/PLSQL boundary (including features such as automatically transpiling certain PL/SQL functions into SQL expressions for faster execution inside SQL statements), well-structured PL/SQL code becomes even more valuable—because it can be reused safely in more places without rewriting everything as pure SQL.

Read-only examples

The examples below are designed for reading and pattern recognition. They illustrate how records and collections “feel” in real code, and how %TYPE/%ROWTYPE reduce fragility.

/* Example 1: %TYPE and %ROWTYPE */

DECLARE
  v_empno   emp.empno%TYPE;
  v_emp_rec emp%ROWTYPE;
BEGIN
  SELECT empno INTO v_empno
  FROM emp
  WHERE ename = 'SCOTT';

  SELECT * INTO v_emp_rec
  FROM emp
  WHERE empno = v_empno;

  -- v_emp_rec now holds an entire row (row-shaped record)
END;
/

/* Example 2: Collections (three flavors) */

DECLARE
  -- Associative array (map-like; PL/SQL only)
  TYPE t_bonus_by_key IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  l_bonus t_bonus_by_key;

  -- Nested table (SQL-visible when defined at schema level)
  TYPE t_dept_list IS TABLE OF NUMBER;
  l_depts t_dept_list := t_dept_list(10, 20, 30);

  -- Varray (bounded, always dense)
  TYPE t_top3 IS VARRAY(3) OF VARCHAR2(30);
  l_tags t_top3 := t_top3('fast', 'safe', 'reusable');
BEGIN
  l_bonus(1001) := 500;
  l_bonus(1002) := 750;
END;
/

/* Example 3: Bulk processing (conceptual pattern) */

DECLARE
  TYPE t_empno_list IS TABLE OF emp.empno%TYPE;
  l_empnos t_empno_list;

BEGIN
  SELECT empno
  BULK COLLECT INTO l_empnos
  FROM emp
  WHERE deptno = 10;

  FORALL i IN 1 .. l_empnos.COUNT
    UPDATE emp
    SET sal = sal * 1.05
    WHERE empno = l_empnos(i);
END;
/