Oracle PL/SQL offers three kinds of collections: Associative Arrays, Nested Tables, and Varrays (short for variable-size arrays). Nested tables extend the functionality of associative arrays (formerly called PL/SQL tables or index-by tables).
Migrate Associative Array
The Associative arrays were known as PL/SQL tables in Oracle 7, and index-by tables in Oracle 8 and 8i. The name was changed to Associative arrays in Oracle 9i.
This article demonstrates different usages of an associative array in Oracle and how to migrate the features in PostgreSQL.
SET SERVEROUTPUT ON;
DECLARE
TYPE dcm_test IS TABLE OF NUMBER -- Associative array type
INDEX BY PLS_INTEGER; -- indexed by integer
testArray dcm_test; -- Associative array variable
BEGIN
-- Add elements (key-value pairs) to associative array:
testArray(1) := 1000;
testArray(2) := 50000;
testArray(3) := 1750000;
-- Change value for index 2
testArray(2) := 88880;
-- Print associative array:
FOR i IN testArray.FIRST .. testArray.LAST LOOP
DBMS_Output.PUT_LINE('Array (' || i || ') = ' || testArray(i));
END LOOP;
END;
/
Output:
Array (1) = 1000 Array (2) = 88880 Array (3) = 1750000 PL/SQL procedure successfully completed.
SET client_min_messages = debug;
DO $$
DECLARE
-- Declare array
testArray INTEGER[]; -- Array variable declaration
BEGIN
-- Add elements (key-value pairs) to associative array:
testArray[1] := 1000;
testArray[2] := 50000;
testArray[3] := 1750000;
-- Change value associated with key 'Smallville':
testArray[2] := 88880;
-- Print associative array:
FOR i IN array_lower(testArray, 1) .. array_upper(testArray, 1) LOOP
RAISE DEBUG USING message := concat_ws('', 'Array (', i, ') = ', testArray[i]);
END LOOP;
END $$;
Output:
DEBUG: Array (1) = 1000 DEBUG: Array (2) = 88880 DEBUG: Array (3) = 1750000 DO
Migrate Nested Table
Nested tables are an extension of the associative arrays. The main difference between the two is that nested tables can be stored in a database column but associative arrays cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation nested tables are dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.
In order to use nested tables in PL/SQL block for rows processing, these steps must be followed.
- When you declare a nested table variable, it must be initialized. You must initialize it, either by making it empty or by assigning a non-NULL value to it. An uninitialized nested table variable is a null collection. Any operation (except EXISTS operation) on an uninitialized nested table wil throw “ORA-06531: Reference to uninitialized collection” error.
- To add an element to a nested table, you first use the EXTEND method to allocate memory to nested table.
This article demonstrates different usages of nested tables in Oracle and how to migrate the features in PostgreSQL.
Below program declares a varray of size 3, is initialized and extended to store elements.
DECLARE
TYPE dcm_test IS VARRAY(3) OF NUMBER; -- Varray type
testArray dcm_test := dcm_test(); -- Varray variable
BEGIN
-- Add elements (key-value pairs) to VARRAY:
testArray.EXTEND(3);
testArray(1) := 1000;
testArray(2) := 50000;
testArray(3) := 1750000;
-- Change value for index 2
testArray(2) := 88880;
-- Print associative array:
FOR i IN testArray.FIRST .. testArray.LAST LOOP
DBMS_Output.PUT_LINE('Array (' || i || ') = ' || testArray(i));
END LOOP;
END;
/
Output:
Array (1) = 1000 Array (2) = 88880 Array (3) = 1750000 PL/SQL procedure successfully completed.
Postgres provides arrays which are powerful feature and conceptually resemble Oracle varrays in all aspects.
SET client_min_messages = debug;
DO $$
DECLARE
-- Declare array
testArray INTEGER[]; -- Array variable declaration
BEGIN
-- Add elements (key-value pairs) to associative array:
testArray[1] := 1000;
testArray[2] := 50000;
testArray[3] := 1750000;
-- Change value associated with key 'Smallville':
testArray[2] := 88880;
-- Print associative array:
FOR i IN array_lower(testArray, 1) .. array_upper(testArray, 1) LOOP
RAISE DEBUG USING message := concat_ws('', 'Array (', i, ') = ', testArray[i]);
END LOOP;
END $$;
Output:
DEBUG: Array (1) = 1000 DEBUG: Array (2) = 88880 DEBUG: Array (3) = 1750000 DO