1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. Migrating Oracle Collections
  5. Convert Nested table

Convert 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 butassociative 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 anuninitialized 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.

OraclePostgreSQL
SET SERVEROUTPUT ON;
DECLARE
TYPE dcm_test IS TABLE OF NUMBER; — Nested table type

testArray dcm_test := dcm_test(); — Nested table variable declaration and initilization
BEGIN
— Add elements (key-value pairs) to Nested table:
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;
/

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 $$;

DEBUG: Array (1) = 1000
DEBUG: Array (2) = 88880
DEBUG: Array (3) = 1750000
DO

Was this article helpful to you? Yes No 1

How can we help?