Collections and Records in PL/SQL

Collections and records are composite types that have internal components that can be manipulated individually, such as the elements of an array, record, or table.

PL/SQL Collections

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other data types used in classic programming algorithms. Each element is addressed by a unique subscript.
PL/SQL offers these collection types:

  • Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.
  • Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
  • Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.


array versus nested table       varray of size 10

PL/SQL Records

A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row or some columns from a table row. The fields correspond to table columns.
Records are composed of a group of fields, similar to the columns in a row. The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a database table, without listing all the columns. Your code keeps working even after columns are added to the table. If you want to represent a subset of columns in a table, or columns from different tables, you can define a view or declare a cursor to select the right columns and do any necessary joins, and then apply %ROWTYPE to the view or cursor.

Want to know more about SQL? Read this extensive SQL Tutorial and enhance your knowledge!

Defining Collection Types and Declaring Collection Variables

To create collections, you define a collection type, and then declare variables of that type. Collections follow the same scoping and instantiation rules as other types and variables. Collections are instantiated when you enter a block or subprogram, and cease to exist when you exit. In a package, collections are instantiated when you first reference the package and cease to exist when you end the database session.
You can define TABLE and VARRAY types in the declarative part of any PL/SQL block, subprogram, or package using a TYPE definition.
For nested tables and varrays declared within PL/SQL, the element type of the table or varray can be any PL/SQL datatype except REF CURSOR.
When defining a VARRAY type, you must specify its maximum size with a positive integer. In the following example, you define a type that stores up to 366 dates:




Declaring PL/SQL Collection Variables

After defining a collection type, you declare variables of that type. You use the new type name in the declaration, the same as with predefined types such as NUMBER.

Example: Declaring Nested Tables, Varrays, and Associative Arrays


TYPE nested_type IS TABLE OF VARCHAR2(30);
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
-- an arbitrary number of strings can be inserted v1
v1 := nested_type('Shipping','Sales','Finance','Payroll');
v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
v3(99) := 10; -- Just start assigning to elements
v3(7) := 100; -- Subscripts can be any integer values
v4(42) := 'Smith'; -- Just start assigning to elements
v4(54) := 'Jones'; -- Subscripts can be any integer values
v5('Canada') := 'North America'; -- Just start assigning to elements
v5('Greece') := 'Europe'; -- Subscripts can be string values


Initializing and Referencing Collections

Until you initialize it, a nested table or varray is atomically null; the collection itself is null, not its elements. To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function constructs collections from the elements passed to it.
You must explicitly call a constructor for each varray and nested table variable. Associative arrays, the third kind of collection, do not use constructors. Constructor calls are allowed wherever function calls are allowed.

Example: Constructor for a Nested Table


TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab;
dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');


Because a nested table does not have a declared size, you can put as many elements in the constructor as necessary.

Learn Oracle PL SQL

Referencing Collection Elements

Every reference to an element includes a collection name and a subscript enclosed in parentheses. The subscript determines which element is processed. To reference an element, you specify its subscript using the syntax



where subscript is an expression that yields an integer in most cases, or a VARCHAR2 for associative arrays declared with strings as keys.
The allowed subscript ranges are:

  • For nested tables, 1 .. 2147483647 (the upper limit of PLS_INTEGER).
  • For varrays, 1 .. size_limit, where you specify the limit in the declaration (not to exceed 2147483647).
  • For associative arrays with a numeric key, -2147483648 to 2147483647.
  • For associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set.

Example:  Referencing a Nested Table Element


names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE verify_name(the_name VARCHAR2) IS
FOR i IN names.FIRST .. names.LAST
IF names(i) = 'J Hamil' THEN
DBMS_OUTPUT.PUT_LINE(names(i)); -- reference to nested table element
verify_name(names(3)); -- procedure call with reference to element


Assigning Collections

One collection can be assigned to another by an INSERT, UPDATE, FETCH, or SELECT statement, an assignment statement, or a subprogram call. You can assign the value of an expression to a specific element in a collection using the syntax:


collection_name(subscript) := expression;


where expression yields a value of the type specified for elements in the collection type definition.
You can use operators such as SET, MULTISET UNION, MULTISET INTERSECT, and MULTISET EXCEPT to transform nested tables as part of an assignment statement.

Comparing Collections

You can check whether a collection is null. Comparisons such as greater than, less than, and so on are not allowed. This restriction also applies to implicit comparisons.
For example, collections cannot appear in a select DISTINCT, GROUP BY, or ORDER BY list.

Example: Checking if a Collection Is Null


TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
TYPE staff IS TABLE OF emp_name_rec;
members staff;
-- Condition yields TRUE because we have not used a constructor.


Multilevel Collections

In addition to collections of scalar or object types, you can also create collections whose elements are collections. For example, you can create a nested table of varrays, a varray of varrays, a varray of nested tables, and so on.
When creating a nested table of nested tables as a column in SQL, check the syntax of the CREATE TABLE statement to see how to define the storage table.

Visit our Community to get answers to all your queries!

Collection Methods

Collection methods make collections easier to use and make your applications easier to maintain. These methods include COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, and TRIM.
A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The following applies to collection methods:
■ Collection methods cannot be called from SQL statements.
■ EXTEND and TRIM cannot be used with associative arrays.
■ EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures.
■ EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts, which are usually integers but can also string for associative arrays.
■ Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.

1. EXISTS Method

EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE.

2. COUNT Method

COUNT returns the number of elements that a collection currently contains.

3. LIMIT Method

For nested tables and associative arrays, which have no declared size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain.

4. FIRST and LAST Methods

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.

5. PRIOR and NEXT Methods

PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n)returns the index number that succeeds index n. If n has no predecessor, PRIOR(n)returns NULL. If n has no successor, NEXT(n)returns NULL.

6. EXTEND Method

To increase the size of a nested table or varray, use EXTEND.
This procedure has three forms:

  • EXTEND appends one null element to a collection.
  • EXTEND(n) appends n null elements to a collection.
  • EXTEND(n, i) appends n copies of the ith element to a collection.

 7. TRIM Method

This procedure has two forms:
■ TRIM removes one element from the end of a collection.
■ TRIM(n) removes n elements from the end of a collection.
If you want to remove all elements, use DELETE without parameters.

8. DELETE Method

This procedure has various forms:

  • DELETE with no parameters removes all elements from a collection, setting COUNT to 0.
  • DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
  • DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n)does

Go for this in-depth job-oriented PL/SQL Training Course now!

Avoiding Collection Exceptions

 avoiding collection exceptions

Defining and Declaring Records

 To create records, you define a RECORD type, then declare records of that type. You can also create or find a table, view, or PL/SQL cursor with the values you want, and use the %ROWTYPE attribute to create a matching record.
You can define RECORD types in the declarative part of any PL/SQL block, Pl/SQL subprogram, or PL/SQL package. When you define your own RECORD type, you can specify a NOT NULL constraint on fields, or give them default values.

Example: Declaring and Initializing a Simple Record Type


deptid NUMBER(4) NOT NULL := 99,
dname departments.department_name%TYPE,
loc departments.location_id%TYPE,
region regions%ROWTYPE );
dept_rec DeptRecTyp;
dept_rec.dname := 'PURCHASING';

Assigning Values to Records

To set all the fields in a record to default values, assign to it an uninitialized record of the same type.

Example: Assigning Default Values to a Record


TYPE RecordTyp IS RECORD (field1 NUMBER,
field2 VARCHAR2(32) DEFAULT 'something');
rec1 RecordTyp;
rec2 RecordTyp;
-- At first, rec1 has the values we assign.
rec1.field1 := 100; rec1.field2 := 'something else';
-- Assigning an empty record to rec1 resets fields to their default values.
-- Field1 is NULL and field2 is 'something' due to the DEFAULT clause
rec1 := rec2;
DBMS_OUTPUT.PUT_LINE('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',
field2 = ' || rec1.field2);


You can assign a value to a field in a record using an assignment statement with dot notation:

emp_info.last_name := 'Fields';


Comparing Records 

Records cannot be tested for nullity or compared for equality, or inequality. If you want to make such comparisons, write your own function that accepts two records as parameters and does the appropriate checks or comparisons on the corresponding fields.

Check out the top SQL Interview Questions to learn what is expected from SQL professionals!

Inserting PL/SQL Records into the Database

A PL/SQL-only extension of the INSERT statement lets you insert records into database rows, using a single variable of type RECORD or %ROWTYPE in the VALUES clause instead of a list of fields. That makes your code more readable and maintainable.
If you issue the INSERT through the FORALL statement, you can insert values from an entire collection of records. The number of fields in the record must equal the number of columns listed in the INTO clause, and corresponding fields and columns must have compatible data types. To make sure the record is compatible with the table, you might find it most convenient to declare the variable as the type table_name%ROWTYPE.

Example: Inserting a PL/SQL Record Using %ROWTYPE


dept_info departments%ROWTYPE;
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
-- Using the %ROWTYPE means we can leave out the column list
-- (department_id, department_name, and location_id) from the INSERT statement
INSERT INTO departments VALUES dept_info;


Updating the Database with PL/SQL Record Values

A PL/SQL-only extension of the UPDATE statement lets you update database rows using a single variable of type RECORD or %ROWTYPE on the right side of the SET clause, instead of a list of fields.

Example: Updating a Row Using a Record


dept_info departments%ROWTYPE;
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE.
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
-- The fields of a %ROWTYPE can completely replace the table columns
-- The row will have values for the filled-in columns, and null
-- for any other columns
UPDATE departments SET ROW = dept_info WHERE department_id = 300;


Querying Data into Collections of Records

You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection of records.

Course Schedule

Name Date
Database Architect 2021-04-24 2021-04-25
(Sat-Sun) Weekend batch
View Details
Database Architect 2021-05-01 2021-05-02
(Sat-Sun) Weekend batch
View Details
Database Architect 2021-05-08 2021-05-09
(Sat-Sun) Weekend batch
View Details

1 thought on “PL/SQL Collections and Records”

  1. I’m really enjoying the content, design and layout of your site. It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a developer to create your theme? Exceptional work!

Leave a Reply

Your email address will not be published. Required fields are marked *