Working with PL/SQL collections is an important part of the Oracle Database 11g: Advanced PL/SQL exam for which I’m currently studying. This post serves as a refresher on the different types of collections that exist in the wonderful PL/SQL language.

Associative arrays

  • Previously known as index-by tables or PL/SQL tables.
  • Single-dimensional set of key-value pairs. Each key is unique, and is used to locate the corresponding value.
  • Arbitrary key values, which means that the keys don’t need to be consecutive.
  • Indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2 or one of VARCHAR2 subtypes VARCHAR, STRING, or LONG.
  • BINARY_INTEGER and PLS_INTEGER are identical and can be used interchangeably since Oracle Database 10g.
  • BULK COLLECT INTO can be used to populate the collection from a query result. The key type cannot be VARCHAR2 in this case.
  • An associative array cannot be used as column type in a database table.
set serveroutput on
declare
  type laat_dept_names
    is table of departments.department_name%type
    index by pls_integer;

  laa_dept_names laat_dept_names;
  l_index number;
begin
  -- bulk collect into example
  -- select department_name
  -- bulk collect into laa_dept_names
  -- from departments;

  -- add new elements
  laa_dept_names(1) := 'Administration';
  laa_dept_names(2) := 'Marketing';
  laa_dept_names(3) := 'Purchasing';
  laa_dept_names(4) := 'Human Resources';

  -- change existing value associated with key value '4'
  laa_dept_names(4) := 'HR';

  -- remove element
  laa_dept_names.delete(3);

  -- print elements
  l_index := laa_dept_names.first;
  while l_index is not null loop
    dbms_output.put_line(laa_dept_names(l_index) || ' (' || l_index || ')');
    l_index := laa_dept_names.next(l_index);
  end loop;
end;

-- output:
-- Administration (1)
-- Marketing (2)
-- HR (4)

Nested tables

  • Single-dimensional array with an unbounded number of elements.
  • Initially dense, but can become sparse, because you can delete elements from the collection.
    • Dense = collection has no gaps between its elements.
    • Sparse = collection can have gaps between its elements.
  • Always indexed by an integer.
  • A nested table can be used as column type in a database table.
set serveroutput on
declare
  type lntt_dept_names
    is table of departments.department_name%type;

  lnt_dept_names lntt_dept_names;
  l_index number;
begin
  -- initialise the collection with two values
  -- key values start with 1 and are consecutive
  lnt_dept_names := lntt_dept_names('Administration', 'Marketing');

  -- extend the collection with two extra values
  lnt_dept_names.extend(2);

  -- add new elements
  lnt_dept_names(3) := 'Purchasing';
  lnt_dept_names(4) := 'Human Resources';

  -- change existing value associated with key value '4'
  lnt_dept_names(4) := 'HR';

  -- remove element
  -- a nested table can become sparse
  lnt_dept_names.delete(3);

  -- print elements
  l_index := lnt_dept_names.first;
  while l_index is not null loop
    dbms_output.put_line(lnt_dept_names(l_index) || ' (' || l_index || ')');
    l_index := lnt_dept_names.next(l_index);
  end loop;
end;

-- output:
-- Administration (1)
-- Marketing (2)
-- HR (4)

The EXTEND method can accept different parameters:

  • 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.

Varrays

  • Varrays are very similar to nested tables, except for the fact that you must specify an upper bound.
  • Varrays cannot become sparse, which means that you can’t use the DELETE method on varrays.
  • A varray can be used as column type in a database table.
set serveroutput on
declare
  type lvat_dept_names
    is varray(4) of departments.department_name%type;

  lva_dept_names lvat_dept_names;
  l_index number;
begin
  -- initialise the collection with two values
  -- key values start with 1 and are consecutive
  lva_dept_names := lvat_dept_names('Administration', 'Marketing');

  -- extend the collection with two extra values
  lva_dept_names.extend(2);

  -- add new elements
  lva_dept_names(3) := 'Purchasing';
  lva_dept_names(4) := 'Human Resources';

  -- change existing value associated with key value '4'
  lva_dept_names(4) := 'HR';

  -- a varray can't become sparse
  -- lva_dept_names.delete(3);

  -- print elements
  l_index := lva_dept_names.first;
  while l_index is not null loop
    dbms_output.put_line(lva_dept_names(l_index) || ' (' || l_index || ')');
    l_index := lva_dept_names.next(l_index);
  end loop;
end;

-- output:
-- Administration (1)
-- Marketing (2)
-- Purchasing (3)
-- HR (4)

Resources