Define and use collection types in a package - PolarDB

PolarDB for Oracle supports defining variable-length arrays (VARRAYs), nested tables, and associative arrays inside a package specification, making those types available to any code that references the package.

Collection type scopes

Collection types can be defined at three scopes:

Scope	How to define	Accessible from	Persistence
Local	`TYPE ... IS TABLE OF ...` in a `DECLARE` block	Only the current block	Dropped when the block exits
Package	`TYPE ... IS TABLE OF ...` in a package specification	Any code that references the package	Stored in the system table; dropped when the package is dropped
Schema-level (global)	`CREATE TYPE ... IS TABLE OF ...` at schema level	Any code in the database	Persists until explicitly dropped

Define a collection type in a package when you need to share the type across multiple procedures or anonymous blocks.

Examples

Both examples use the pkg package defined below, which declares a nested table type NumberList and a procedure print that accepts a NumberList parameter.

CREATE OR REPLACE PACKAGE pkg AS
  TYPE NumberList IS TABLE OF NUMBER;
  PROCEDURE print (nums NumberList);
END pkg;

CREATE OR REPLACE PACKAGE BODY pkg AS
  PROCEDURE print (nums NumberList) IS
  BEGIN
    FOR i IN nums.FIRST..nums.LAST LOOP
      RAISE NOTICE '%', nums(i);
    END LOOP;
  END;
END;

Pass a local collection variable to a package procedure

This example declares a local collection type CharList (element type VARCHAR(10)) and passes a variable of that type to pkg.print, which expects a NumberList (element type NUMBER). PolarDB for Oracle performs implicit type conversion based on the element type.

DECLARE
  TYPE CharList IS TABLE OF VARCHAR(10);
  n1 pkg.NumberList := pkg.NumberList(1,2); -- Package collection type variable.
  n2 CharList := CharList('3','4');         -- Local collection type variable.
BEGIN
  pkg.print(n1); -- Pass the package collection type variable directly.
  pkg.print(n2); -- Pass the local collection type variable; PolarDB for Oracle converts based on element type.
END;

Expected output:

NOTICE:  1
NOTICE:  2
NOTICE:  3
NOTICE:  4
DO

Pass a global collection variable to a package procedure

This example declares a global collection type NumList and passes it to pkg.print. PolarDB for Oracle converts the value based on the element type, the same way it does for the local type in the previous example.

-- Declare the global collection type.
CREATE OR REPLACE TYPE NumList IS TABLE OF NUMBER;

-- Use the package procedure from the previous example.
DECLARE
  n NumList := NumList(5,6);
BEGIN
  pkg.print(n);
END;

Expected output:

NOTICE:  5
NOTICE:  6
DO