PL/SQL Collections and Records

PL/SQL Collections and Records: A Comprehensive Guide

In Oracle PL/SQL, Composite Data Types allow you to group multiple values into a single unit. The two most important composite types are Collections and Records. These are essential for handling bulk data efficiently and modeling complex business objects (Feuerstein & Pribyl, 2014).

---

Part 1: Understanding Collections vs. Records

• Collection: An ordered group of elements, all of the same type. You access elements using a unique subscript (index). Examples: Lists and Arrays (Oracle, 2025a).
• Record: A group of related data items, potentially of different types. You access fields by their name. A record is perfect for holding a single row of a database table (Oracle, 2025b).

Key Rule: To use a collection or record, you must first define the type, and then declare a variable of that type (Feuerstein & Pribyl, 2014).

Characteristics of PL/SQL Collection Types

Oracle provides three types of collections. The table below summarizes their key differences (Oracle, 2025a):

Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created	Can be Object Type Attribute
Associative Array (Index-By Table)	Unbounded	String or Integer	Either	Only in PL/SQL block	No
Nested Table	Unbounded	Integer	Starts dense, can become sparse	PL/SQL block or Schema level	Yes
Varray (Variable-size array)	Bounded	Integer	Always dense	PL/SQL block or Schema level	Yes

Definitions:

• Unbounded: Theoretically no limit to elements (though memory limits apply).
• Dense: No gaps between elements. Every element between the first and last has a value.
• Sparse: Gaps exist (elements have been deleted). You must use the NEXT method to traverse (Oracle, 2025a).

---

Part 2: The Three Collection Types

1. Associative Arrays (Index-By Tables)

These are the simplest collections. They have no upper bound and are indexed using BINARY_INTEGER or VARCHAR2. They can only exist in a PL/SQL block (Oracle, 2025a).

SET SERVEROUTPUT ON
DECLARE
   TYPE table_type IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;
   v_tab table_type;
   v_idx NUMBER;
BEGIN
   -- Initialise the collection.
   FOR i IN 1 .. 5 LOOP
      v_tab(i) := i;
   END LOOP;
   
   -- Delete the third item.
   v_tab.DELETE(3);
   
   -- Traverse sparse collection using FIRST and NEXT
   v_idx := v_tab.FIRST;
   WHILE v_idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
      v_idx := v_tab.NEXT(v_idx);
   END LOOP;
END;
/

2. Nested Tables

Nested tables can be stored in a database column (unlike associative arrays). They start dense, but deleting elements makes them sparse (Oracle, 2025a).

SET SERVEROUTPUT ON
DECLARE
   TYPE table_type IS TABLE OF NUMBER(10);
   v_tab table_type;
   v_idx NUMBER;
BEGIN
   -- Initialise with two values
   v_tab := table_type(1, 2);

   -- Extend the collection
   FOR i IN 3 .. 5 LOOP
      v_tab.EXTEND;
      v_tab(v_tab.LAST) := i;
   END LOOP;
   
   -- Delete the third item
   v_tab.DELETE(3);
   
   -- Traverse
   v_idx := v_tab.FIRST;
   WHILE v_idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
      v_idx := v_tab.NEXT(v_idx);
   END LOOP;
END;
/

3. Varrays (Variable-Size Arrays)

Varrays must have a fixed maximum size defined in the declaration. They are always dense; you cannot delete individual elements (Oracle, 2025a).

SET SERVEROUTPUT ON
DECLARE
   TYPE table_type IS VARRAY(5) OF NUMBER(10);
   v_tab table_type;
   v_idx NUMBER;
BEGIN
   v_tab := table_type(1, 2);

   FOR i IN 3 .. 5 LOOP
      v_tab.EXTEND;
      v_tab(v_tab.LAST) := i;
   END LOOP;
   
   -- Cannot do v_tab.DELETE(3) on a Varray!
   
   v_idx := v_tab.FIRST;
   WHILE v_idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
      v_idx := v_tab.NEXT(v_idx);
   END LOOP;
END;
/

---

Part 3: Assignments, Equality, and Collection Methods

Assignments and Equality

You can only assign a collection to another variable if they are of the exact same type. Two separately defined types with identical structures are not compatible (Feuerstein & Pribyl, 2014).

-- This works
DECLARE
   TYPE t1 IS TABLE OF NUMBER;
   v1 t1; v2 t1;
BEGIN
   v1 := t1(1, 2);
   v2 := v1; -- Success
END;
/

-- This FAILS (PLS-00382: expression is of wrong type)
DECLARE
   TYPE t1 IS TABLE OF NUMBER;
   TYPE t2 IS TABLE OF NUMBER;
   v1 t1; v2 t2;
BEGIN
   v1 := t1(1, 2);
   v2 := v1; -- Compilation Error
END;
/

Essential Collection Methods

• EXISTS(n): Returns TRUE if element n exists.
• COUNT: Returns the number of elements.
• LIMIT: Returns the maximum size of a VARRAY (NULL for nested tables).
• FIRST / LAST: Returns the index of the first/last element.
• PRIOR(n) / NEXT(n): Returns the index of the element before/after n.
• EXTEND / EXTEND(n): Appends null elements to the end of a nested table or varray.
• TRIM / TRIM(n): Removes elements from the end.
• DELETE / DELETE(n) / DELETE(n1,n2): Removes elements (not allowed on VARRAY) (Oracle, 2025a).

---

Part 4: Multiset Conditions

Oracle allows you to perform set operations directly on collections using the MULTISET keyword (Oracle, 2025a).

MULTISET UNION

Combines two collections (like UNION ALL). Add DISTINCT to remove duplicates.

SET SERVEROUTPUT ON
DECLARE
   TYPE t_tab IS TABLE OF NUMBER;
   l_tab1 t_tab := t_tab(1,2,3,4,5,6);
   l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
   l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;
   FOR i IN l_tab1.FIRST .. l_tab1.LAST LOOP
      DBMS_OUTPUT.put_line(l_tab1(i));
   END LOOP;
END;
/

MULTISET EXCEPT & INTERSECT

EXCEPT: Returns elements in the first set that are not in the second.
INTERSECT: Returns elements present in both sets (Oracle, 2025a).

-- INTERSECT Example (Returns 6,7,8,9,10)
l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;

-- EXCEPT Example (Returns 1,2,3,4,5)
l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;

SUBMULTISET Condition

Checks if the first collection is a subset of the second.

SET SERVEROUTPUT ON
DECLARE
   TYPE t_tab IS TABLE OF NUMBER;
   l_tab1 t_tab := t_tab(1,2,3,4,5);
   l_tab2 t_tab := t_tab(1,2,3);
BEGIN
   IF l_tab2 SUBMULTISET OF l_tab1 THEN
      DBMS_OUTPUT.put_line('l_tab2 is a subset of l_tab1: TRUE');
   END IF;
END;
/

Note: An empty subset will always return TRUE (Oracle, 2025a).

MEMBER Condition

Tests if a specific element exists inside a collection.

SET SERVEROUTPUT ON
DECLARE
   TYPE t_tab IS TABLE OF NUMBER;
   l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
   IF 3 MEMBER OF l_tab1 THEN
      DBMS_OUTPUT.put_line('3 is a member: TRUE');
   END IF;
END;
/

---

Part 5: Records and Multidimensional Collections

PL/SQL Records

A record groups related data items. They are often based on database tables using the %ROWTYPE attribute (Oracle, 2025b).

DECLARE
   -- Define a record type manually
   TYPE t_row IS RECORD (
      id NUMBER,
      description VARCHAR2(50)
   );
   
   -- Or base it on a table
   v_emp_rec employees%ROWTYPE;
BEGIN
   -- Manually defined record
   t_row.id := 1;
   t_row.description := 'Test Record';
END;
/

Multidimensional Collections (Collections of Records)

You can create a collection whose elements are records. This allows you to hold a whole table in memory (Feuerstein & Pribyl, 2014).

-- Collection of Records (using a table's ROWTYPE)
CREATE TABLE t1 (
  id NUMBER,
  description VARCHAR2(50)
);

SET SERVEROUTPUT ON
DECLARE
   TYPE t_tab IS TABLE OF t1%ROWTYPE;
   l_tab t_tab := t_tab();
BEGIN
   FOR i IN 1 .. 10 LOOP
      l_tab.EXTEND();
      l_tab(l_tab.LAST).id := i;
      l_tab(l_tab.LAST).description := 'Description for ' || i;
   END LOOP;
END;
/

Collections of Collections

You can also build two-dimensional collections by making a collection of another collection type (Feuerstein & Pribyl, 2014).

DECLARE
   TYPE t_tab1 IS TABLE OF NUMBER;
   TYPE t_tab2 IS TABLE OF t_tab1;
   
   l_tab1 t_tab1 := t_tab1(1,2,3,4,5);
   l_tab2 t_tab2 := t_tab2();
BEGIN
   FOR i IN 1 .. 10 LOOP
      l_tab2.EXTEND();
      l_tab2(l_tab2.LAST) := l_tab1;
   END LOOP;
END;
/

Conclusion: Collections and Records are powerful tools in PL/SQL. They allow you to process data in bulk (using BULK COLLECT and FORALL), store temporary data in memory, and model complex business logic. Mastering the differences between Associative Arrays, Nested Tables, and Varrays is key to writing efficient PL/SQL code (Feuerstein & Pribyl, 2014).

---

References

Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL programming (6th ed.). O'Reilly Media.

Oracle. (2025a). Database PL/SQL language reference: PL/SQL collections and records (19c). Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-collections-and-records.html

Oracle. (2025b). Database PL/SQL language reference: PL/SQL records (19c). Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-record.html

My Personal Experience

Throughout my 17 years as a Senior Oracle Developer, I have used PL/SQL collections in almost every enterprise project I have delivered. For example, in my Capstone Project (the Change Management System), I used associative arrays to cache user role information in memory. This prevented the application from querying the database repeatedly for every page load. In my real-world projects, understanding the difference between Nested Tables and VARRAYs has helped me design more efficient ETL processes and bulk data loads. Mastering these structures is essential for any developer who wants to move beyond basic SQL and write high-performance PL/SQL code.