AWS Database Blog

Migrate Oracle collections to Amazon Aurora PostgreSQL-Compatible Edition or Amazon RDS for PostgreSQL

Migrating from Oracle to PostgreSQL can involve several steps to ensure a smooth transition. A notable example is an Oracle collection, which is a feature that Oracle database developers commonly use. PostgreSQL does not directly support Oracle collections, and there currently isn’t an extension that adds this capability. There are several decisions you have to make when migrating Oracle collections to PostgreSQL to successfully make the transition.

Oracle database developers use collections in many scenarios, such as the following:

  • Caching static values to reduce the number of database calls, consequently enhancing speed and efficiency
  • Repeatedly accessing data within a block or subprogram, often involving conditional modification and storage for future retrieval within the same transaction
  • Utilizing collections as function or procedure parameters or return types to pass multiple values to other calling subprograms
  • Passing values to an application where the application layer defines a list, array, or map to process values

In this post, we discuss Oracle simple collections, composite collections, and multi-dimensional collections, and how you can migrate these Oracle features to Amazon Aurora PostgreSQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for PostgreSQL using PostgreSQL’s native custom types and arrays while following recommended best practices.

Overview of representing Oracle collections in PostgreSQL

PostgreSQL database provides arrays, which you can use as an option for porting Oracle collections. Each array element is a data type similar to that of the collection. You can create an array for any built-in or user-defined data type.

Declaring an array variable in PostgreSQL is done using the [] (array) operator. Here’s how you can declare arrays and use them in PostgreSQL:

  • Simple array creation – You can create a simple array in PostgreSQL using the array [] operator of any predefined data type. For example, you can create an array of varchar(20) and assign values using following SQL statements:
Arr1 varchar(20)[];
Arr1[1] := 'ABC';
Arr1[2] := 'DEFGH';
SQL
  • Composite array creation – You can create a composite array in PostgreSQL by first creating a user-defined type and then using the type to create an array. The following statements create a custom type named person_type, define an array variable of the custom type created, and assign values to array elements:
-- Create a custom type
CREATE TYPE person_type AS (
    first_name text,
    last_name  text,
    age int
);

--Define composite array
arr2 person_type[];

--Assign values to composite array
arr2[1] := ROW('John', 'Smith', 30);
SQL

You can also use a domain in PostgreSQL for defining array variables. You create a domain when you intend to utilize the same existing structure. For example, you can create a domain for person_type[] and use it as is, without needing to declare arrays with the operator []:

-- Create a array type of person_type
CREATE DOMAIN person_arr AS person_type[];

--Define array variable of domain type
arr3 person_arr;

--Assign values to composite array
arr3[1] := ROW('John', 'Smith', 30);
SQL

Now that you have learned about arrays in PostgreSQL, let’s dive deep into the use cases and challenges while migrating collections from an Oracle database to PostgreSQL.

Prerequisites

To get started with the examples explained in this post, you must have the following prerequisites:

  • An active AWS account
  • A source Oracle database (on premises or using Amazon RDS for Oracle)
  • A target RDS for PostgreSQL or Aurora PostgreSQL database
  • A database user with the following privileges on the target database (provide your own schema and user):
GRANT CONNECT ON DATABASE TO YOUR_DB_USER;
GRANT USAGE ON SCHEMA YOUR_SCHEMA_NAME TO YOUR_DB_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA YOUR_SCHEMA_NAME TO YOUR_DB_USER;
SQL

Create the following table and insert data using the following script in order to try out the examples described in this post. Run the corresponding statements in Oracle and PostgreSQL databases:

For Oracle, use the following code:

CREATE TABLE Student_Marks(
    StudID         INTEGER,
    Subject        VARCHAR2(20),
    ScoredMarks    INTEGER,
    TotalMarks     INTEGER
);

INSERT ALL
INTO Student_Marks VALUES(10101, 'MATHS', 62, 75)
INTO Student_Marks VALUES(10101, 'ENGLISH', 70, 100)
INTO Student_Marks VALUES(10101, 'ZOOLOGY', 32, 50)
INTO Student_Marks VALUES(10102, 'MATHS', 35, 50)
INTO Student_Marks VALUES(10102, 'COMPUTERS', 43, 50)
INTO Student_Marks VALUES(10102, 'PHYSICS', 69, 100)
INTO Student_Marks VALUES(10102, 'ENGLISH', 81, 100)
SELECT * FROM DUAL;
SQL

For PostgreSQL, use the following code:

CREATE TABLE Student_Marks(
    StudID         INTEGER,
    Subject        VARCHAR(20),
    ScoredMarks    INTEGER,
    TotalMarks     INTEGER
);

INSERT INTO Student_Marks VALUES
(10101, 'MATHS', 62, 75),
(10101, 'ENGLISH', 70, 100),
(10101, 'ZOOLOGY', 32, 50),
(10102, 'MATHS', 35, 50),
(10102, 'COMPUTERS', 43, 50),
(10102, 'PHYSICS', 69, 100),
(10102, 'ENGLISH', 81, 100);
SQL

Migrate a simple collection using BULK COLLECT to PostgreSQL

Oracle PL/SQL developers use the BULK COLLECT feature in SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. BULK COLLECT is currently not available on PostgreSQL databases. If you convert BULK COLLECT with a traditional row-by-row processing approach, then efficiency might become an issue. Therefore, it’s better to use SQL statements rather than looping each row. Moreover, you may learn more about how to migrate BULK COLLECT in PostgreSQL in this post.

The PostgreSQL database offers the array function array_agg, which enables you to achieve a processing approach similar to Oracle. To illustrate, consider a basic collection scenario involving BULK COLLECT. We can then adjust the SQL query in PostgreSQL to retrieve all rows within a single fetch operation.

The following PL/SQL program fetches student marks data from the Student_Marks table and stores them in memory as an OUT parameter, which can be passed to other calling routines and applications for further processing. This following Oracle example demonstrates how BULK COLLECT is used to fetch all rows in a single database call.

/* Package spec declaration */
CREATE OR REPLACE PACKAGE stud_marks
IS
    TYPE MarksArray IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
    PROCEDURE get_stud_marks(
        i_StudentID   IN  INTEGER,
        IO_Stud_Marks OUT MarksArray
    );
END stud_marks;
/

/* Package body declaration */
CREATE OR REPLACE PACKAGE BODY stud_marks
IS
    /* Subprogram uses collections as INOUT/OUT parameter */
    PROCEDURE get_stud_marks(
        i_StudentID   IN  INTEGER,
        IO_Stud_Marks OUT MarksArray
    )
    AS
    BEGIN
        SELECT TotalMarks
        BULK COLLECT INTO IO_Stud_Marks
        FROM Student_Marks WHERE StudID = i_StudentID;
    END get_stud_marks;
END stud_marks;
/
SQL

Let’s run the package procedure through an anonymous block and check the result:

SET SERVEROUTPUT ON;
DECLARE
    v_Stud_Marks stud_marks.MarksArray;
BEGIN
    stud_marks.get_stud_marks(10101, v_Stud_Marks);   --Invoke procedure
    FOR I IN v_Stud_Marks.FIRST..v_Stud_Marks.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(i||':'|| v_Stud_Marks(i));
    END LOOP;
END;
/
SQL

We get the following output:

1:75
2:100
3:50

PL/SQL procedure successfully completed.
SQL

Let’s now convert this Oracle package function to an equivalent PL/pgSQL code. There are a few things to keep in mind when migrating such code to PostgreSQL:

  • PostgreSQL does not support Oracle’s PACKAGE syntax. For this post, we used PostgreSQL schemas to help convert Oracle packages.
  • All functions or procedures are standalone in nature that you create in a schema in PostgreSQL.
  • It’s not recommended to create types or domains in a procedure or function because they’re created as persistent objects within the database and if you run the PL/pgSQL code a second time, it reports the error message “Object already exists.”
  • You should create types and domains in PostgreSQL for collection types defined in the Oracle package specification or body.
  • You should declare an array variable as a domain and use the array_agg function to retrieve all rows as a single unit to populate the array.

With this understanding, let’s convert the Oracle code to PostgreSQL.

/* Creating a schema for package */
CREATE SCHEMA IF NOT EXISTS stud_marks;

/* Creating a Domain for the collection type in the schema */
CREATE DOMAIN stud_marks.MarkArray AS VARCHAR[];

/* Create function in the schema */
CREATE OR REPLACE FUNCTION stud_marks.get_stud_marks(
    Student_ID         IN  INTEGER,
    IO_MarkGradeArray  OUT stud_marks.MarkArray
)
AS $$
BEGIN
    SELECT array_agg(TotalMarks) /* array_agg as alternate to BULK COLLECT */
    INTO IO_MarkGradeArray
    FROM Student_Marks WHERE StudID = Student_ID;
END $$
LANGUAGE plpgsql;
SQL

Let’s run the PostgreSQL function through an anonymous block and check the result:

set client_min_messages = debug;

DO $$
DECLARE
    v_Stud_Marks stud_marks.MarkArray;
BEGIN
    v_Stud_Marks:= stud_marks.get_stud_marks(10101);

    --Accessing the collection type
    FOR i IN array_lower(v_Stud_Marks, 1) .. array_upper(v_Stud_Marks, 1)
    LOOP
        RAISE DEBUG USING message := concat_ws('',i, ':', v_Stud_Marks[i]);
    END LOOP;
    RAISE DEBUG 'Program executed successfully.';
END $$;
SQL

We get the following output:

DEBUG: 1:75
DEBUG: 2:100
DEBUG: 3:50

DEBUG: Program executed successfully.
SQL

Things to note:

  • We use the array_agg function in PostgreSQL as an alternate to BULK COLLECT in Oracle.
  • You should plan well if you need to create a domain for the type defined in Oracle. If the type is a global object, you must create a domain or type in PostgreSQL.

Let’s now explore another scenario with composite collections, and understand how to handle them in PostgreSQL.

Migrate a composite collection to PostgreSQL

Oracle Database allows collections to be composite, where the elements of the collection can contain more than one column. Because all elements of a collection always have the same data type, this type of collection always refers to a collection created from a user-defined data type.

The following PL/SQL program declares a record type named R_MarkGradeRec, comprised of four attributes. An array named MarkGradeArray of type R_MarkGradeRec is created that can store student marks. The procedure get_grades retrieves and populates an output array (IO_MarkGradeArray) with grade-related information for a specified student ID.

We use the following code in Oracle:

/* Package spec declaration */
CREATE OR REPLACE PACKAGE stud_grades
IS
TYPE R_MarkGradeRec IS RECORD(
    SubjectID   VARCHAR2(10),
    ScoredMarks INTEGER,
    TotalMarks  INTEGER,
    Grade       INTEGER
);

TYPE MarkGradeArray IS TABLE OF R_MarkGradeRec INDEX BY PLS_INTEGER;

Procedure get_grades(
    Student_ID        IN  INTEGER,
    IO_MarkGradeArray OUT MarkGradeArray
);
END stud_grades;
/

/* Package body declaration */
CREATE OR REPLACE PACKAGE BODY stud_grades
IS
/* Subprogram uses collections as INOUT/OUT parameter */
    Procedure get_grades(
     Student_ID        IN  INTEGER,
     IO_MarkGradeArray OUT MarkGradeArray
    )
    AS
        C_getMarks      SYS_REFCURSOR;
        l_sql           VARCHAR2(1000);
        l_subjID        VARCHAR2(10);
        l_ScoredMarks   INTEGER;
        l_TotalMarks    INTEGER;
        l_counter       INTEGER := 1;
    BEGIN
        l_sql := 'SELECT Subject, ScoredMarks, TotalMarks FROM Student_Marks WHERE StudID = '||Student_ID;

        OPEN C_getMarks FOR l_sql;
        LOOP
            FETCH C_getMarks INTO l_subjID, l_ScoredMarks, l_TotalMarks;
            EXIT WHEN C_getMarks%NOTFOUND;
            IO_MarkGradeArray(l_counter).SubjectID := l_subjID;
            IO_MarkGradeArray(l_counter).ScoredMarks := l_ScoredMarks;
            IO_MarkGradeArray(l_counter).TotalMarks := l_TotalMarks;
            IO_MarkGradeArray(l_counter).Grade := TO_CHAR(l_ScoredMarks/l_TotalMarks * 100, '99');
            l_counter := l_counter +1;
        END LOOP;
        CLOSE C_getMarks;
    END get_grades;
END stud_grades;
/
SQL

Let’s run the script that retrieves and displays grade information for a specific student (identified by ID 10102) using the custom array type MarkGradeArray. It prints the SubjectID and TotalMarks for each grade entry.

SET SERVEROUTPUT ON;
DECLARE
    v_MarkGradeArray stud_grades.MarkGradeArray;
BEGIN
    stud_grades.get_grades(10102, v_MarkGradeArray);
    FOR i IN v_MarkGradeArray.FIRST.. v_MarkGradeArray.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_MarkGradeArray(i).SubjectID|| ':'|| v_MarkGradeArray(i).TotalMarks);
    END LOOP;
END;
/
SQL

We get the following output:

MATHS:50
COMPUTERS:50
PHYSICS:100
ENGLISH:100

PL/SQL procedure successfully completed.
SQL

You can migrate the aforementioned Oracle code to PostgreSQL by completing the following steps:

  1. Create a user-defined type with the same structure as the Oracle user-defined type.
  2. Create a domain in PostgreSQL using the created type.
  3. Declare an array variable and modify the cursor SELECT statement using array_agg and the row operator.

We use the following code in PostgreSQL:

CREATE SCHEMA IF NOT EXISTS stud_grades;

CREATE TYPE stud_grades.R_MarkGradeArray AS(
    SubjectID     VARCHAR(10),
    ScoredMarks   INTEGER,
    TotalMarks    INTEGER,
    Grade         INTEGER
);

CREATE DOMAIN stud_grades.MarkGradeArray AS stud_grades.R_MarkGradeArray[];

CREATE OR REPLACE FUNCTION stud_grades.get_grades(
    Student_ID IN INTEGER,
    IO_MarkGradeArray OUT stud_grades.MarkGradeArray
)
AS $$
DECLARE
    c_getMarks REFCURSOR;
    l_sql VARCHAR(1000);
    l_subjID VARCHAR(10);
    l_ScoredMarks INTEGER;
    l_TotalMarks INTEGER;
    l_counter INTEGER := 1;
BEGIN
    l_sql := 'SELECT Subject, ScoredMarks, TotalMarks FROM Student_Marks WHERE StudID = $1';
    OPEN C_getMarks FOR EXECUTE l_sql USING Student_ID;
    LOOP
        FETCH C_getMarks INTO l_subjID, l_ScoredMarks, l_TotalMarks;
        EXIT WHEN NOT FOUND;
        IO_MarkGradeArray[l_counter] := row(l_subjID, l_ScoredMarks, l_TotalMarks, (l_ScoredMarks/l_TotalMarks)::INTEGER * 100);
        l_counter := l_counter + 1;
    END LOOP;
END $$
LANGUAGE plpgsql;
SQL

Let’s run the script:

set client_min_messages = debug;

DO $$
DECLARE
    V_MarkGradeArray stud_grades.MarkGradeArray ;
BEGIN
    V_MarkGradeArray:= stud_grades.get_grades(10102);

--Accessing the collection type
    FOR i IN array_lower(V_MarkGradeArray, 1) .. array_upper(V_MarkGradeArray, 1)
    LOOP
        RAISE DEBUG USING message := concat_ws('',V_MarkGradeArray[i].SubjectID, ':', V_MarkGradeArray[i].TotalMarks);
    END LOOP;
    RAISE DEBUG 'Program executed successfully.';
END $$;
SQL

We get the following output:

DEBUG: MATHS:50
DEBUG: COMPUTERS:50
DEBUG: PHYSICS:100
DEBUG: ENGLISH:100

DEBUG: Program executed successfully.
SQL

Migrate a nested composite collection to PostgreSQL

Both the Oracle and PostgreSQL database support nested types, but let’s see the operational differences with respect to collections or arrays in both databases.

You can include a record as a field within another record. This is called a nested record. The record that contains the nested record as a field is called the enclosing record. The following code illustrates how you can port nested composite collections to PostgreSQL and carry out the following actions:

  • Declare two custom types, school_info and student_info:
    • school_info is a record type that represents an address and all attributes are of built-in types.
    • student_info is a nested record type because it contains one attribute of the school_info record type for student’s school details.
  • The students_address_info procedure populates the IO_student_info_array with student information, including IDs, names, and school details.

We use the following code in Oracle:

/* Package spec declaration */
CREATE OR REPLACE PACKAGE stud_info
IS
    TYPE school_info IS RECORD (
        house_addr VARCHAR2(50),
        street     VARCHAR2(50),
        city       VARCHAR2(50)
    );

    TYPE student_info IS RECORD (
        id         INTEGER,
        name       VARCHAR2(50),
        school_det school_info
    );

    TYPE student_info_tbl IS TABLE OF student_info INDEX BY BINARY_INTEGER;

    Procedure students_address_info(
        IO_student_info_array OUT student_info_tbl
    );
END stud_info;
/

/* Package body declaration */
CREATE OR REPLACE PACKAGE BODY stud_info
IS
/* Subprogram uses collections as INOUT/OUT parameter */
Procedure students_address_info(
    IO_student_info_array OUT student_info_tbl
)
AS
    i INTEGER := 1;
BEGIN
    IO_student_info_array(1).id := 101010 ;
    IO_student_info_array(1).name := 'Peter';
    IO_student_info_array(1).school_det.house_addr := 'F 30, ABC Apartments';
    IO_student_info_array(1).school_det.street := 'XYZ Street';
    IO_student_info_array(1).school_det.city := 'HYD CITY, DC';
    IO_student_info_array(2).id := 202020;
    IO_student_info_array(2).name := 'Willard';
    IO_student_info_array(2).school_det.house_addr := 'Lane 4, ABC Center';
    IO_student_info_array(2).school_det.street := NULL;
    IO_student_info_array(2).school_det.city := 'XYZ City, Washington';
    IO_student_info_array(3).id := 303030;
    IO_student_info_array(3).name := 'Andrew';
    IO_student_info_array(3).school_det:= NULL;
END;
end stud_info;
/
SQL

Let’s run the code:

SET SERVEROUTPUT ON;

DECLARE
    IO_STUDENT_INFO_ARRAY STUD_INFO.STUDENT_INFO_TBL;
    i INTEGER := 1;
BEGIN
    STUD_INFO.STUDENTS_ADDRESS_INFO(IO_STUDENT_INFO_ARRAY);
    FOR rec IN IO_STUDENT_INFO_ARRAY.FIRST .. IO_STUDENT_INFO_ARRAY.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE('Element- '||i||': ID='||IO_STUDENT_INFO_ARRAY(i).id||', Name='||IO_STUDENT_INFO_ARRAY(i).name||' is staying at '||IO_STUDENT_INFO_ARRAY(i).school_det.house_addr||','||IO_STUDENT_INFO_ARRAY(i).school_det.street||','||IO_STUDENT_INFO_ARRAY(i).school_det.city);
i:=i+1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Program executed successfully.');
END;
/
SQL

We get the following output:

Element- 1: ID=101010, Name=Peter is staying at F 30, ABC Apartments,XYZ Street,HYD CITY, DC
Element- 2: ID=202020, Name=Willard is staying at Lane 4, ABC Center,,XYZ City, Washington
Element- 3: ID=303030, Name=Andrew is staying at ,,
Program executed successfully.

The PL/SQL procedure successfully completed.
SQL

The following PostgreSQL code snippet is the equivalent code for the preceding nested composite collection:

CREATE SCHEMA IF NOT EXISTS stud_info;

CREATE TYPE stud_info.school_info AS (
    house_addr VARCHAR(50),
    street     VARCHAR(50),
    city       VARCHAR(50)
);

CREATE TYPE stud_info.student_info AS(
    id         INTEGER,
    name       VARCHAR(50),
    school_det stud_info.school_info
);

/* Create Domain from above type */
CREATE DOMAIN stud_info.student_info_tbl AS stud_info.student_info[];

CREATE OR REPLACE FUNCTION stud_info.students_address_info(
    IO_student_info_tbl OUT stud_info.student_info_tbl
)
AS $$
DECLARE
    i INTEGER := 1;
    v_rec stud_info.student_info;
BEGIN
    v_rec := IO_student_info_tbl[1];
    v_rec.id := 101010;
    v_rec.name := 'Peter';
    v_rec.school_det:= ROW('F 30, ABC Apartments', 'XYZ Street', 'HYD CITY, DC');
    IO_student_info_tbl[1] := v_rec;

    /* ROW constructor is optional. PostgreSQL treats values enclosed in parenthesis as RECORD type and can implicitly type cast to proper type. */
    v_rec := IO_student_info_tbl[2];
    v_rec.id := 202020;
    v_rec.name := 'Willard';
    v_rec.school_det:= ('Lane 4, ABC Center', NULL, 'XYZ City, Washington');
    IO_student_info_tbl[2] := v_rec;
    v_rec := IO_student_info_tbl[3];
    v_rec.id := 303030;
    v_rec.name := 'Andrew';
    v_rec.school_det:= NULL;
    IO_student_info_tbl[3] := v_rec;
END $$
LANGUAGE plpgsql;
SQL

Let’s run the code:

set client_min_messages = debug;

DO $$
DECLARE
    i integer=1;
    V_student_info_Array stud_info.student_info_tbl ;
BEGIN
    V_student_info_Array:= stud_info.students_address_info();
    /* Accessing the collection type */
    FOR rec IN array_lower(V_student_info_Array, 1) .. array_upper(V_student_info_Array, 1)
    LOOP
        RAISE DEBUG USING message := concat_ws('', 'Element- ',i,': ID=',V_student_info_Array[i].id,', Name=',V_student_info_Array[i].name,' is staying at ',V_student_info_Array[i].school_det.house_addr,',',V_student_info_Array[i].school_det.street,',',V_student_info_Array[i].school_det.city);
        i:=i+1;
    END LOOP;
    RAISE DEBUG 'Program executed successfully.';
END $$;
SQL

We get the following output:

DEBUG: Element- 1: ID=101010, Name=Peter is staying at F 30, ABC Apartments,XYZ Street,HYD CITY, DC
DEBUG: Element- 2: ID=202020, Name=Willard is staying at Lane 4, ABC Center,,XYZ City, Washington
DEBUG: Element- 3: ID=303030, Name=Andrew is staying at ,,

DEBUG: Program executed successfully.
SQL

Things to note:

  • When values are enclosed in parentheses, PostgreSQL understands them as a single unit and treats them as a record type and implicitly type casts to the proper type. In this case, the ROW constructor is optional.
  • The current version of PostgreSQL doesn’t allow multi-level reference even with the record type, which means v_rec.school_det.city := 'HYD CITY, DC' is not allowed. Therefore, the correct method is modifying the record type, for example v_rec.school_det := ROW('F 30, ABC Apartments', 'XYZ Street', 'HYD CITY, DC');.

Migrate a multi-dimensional collection

You can create a collection whose elements are also collections. For example, you can create a varray of varrays, a nested table of varrays, and so on. PostgreSQL also allows you to create arrays of arrays. This is referred as a multi-dimensional collection or multi-dimensional array.

Let’s take an example of a two-dimensional array, which is the simplest form of a multi-dimensional array. The following anonymous block declares a two-dimensional array CLASS_STUD_JOINDT_ARRAY, populates the arrays, and provides the array access and modification mechanism.

We use the following code in Oracle:

SET SERVEROUTPUT ON;

DECLARE
    TYPE STUDENT_JOINDT_ARRAY IS TABLE OF DATE INDEX BY PLS_INTEGER;
    TYPE CLASS_STUD_JOINDT_ARRAY IS TABLE OF STUDENT_JOINDT_ARRAY INDEX BY PLS_INTEGER;
    V_CLASS_STUD_JOINDT_ARRAY  CLASS_STUD_JOINDT_ARRAY;
    i INTEGER := 1;
BEGIN
    /* Populate the collection. */
    FOR i IN 1..5 LOOP
        FOR j IN 1..2 LOOP
            V_CLASS_STUD_JOINDT_ARRAY(i)(j) := sysdate-i-j;
        END LOOP;
    END LOOP;

    /* Accessing the collection type - Before Modify */
    FOR i IN V_CLASS_STUD_JOINDT_ARRAY.FIRST .. V_CLASS_STUD_JOINDT_ARRAY.LAST
    LOOP
        FOR j IN V_CLASS_STUD_JOINDT_ARRAY(i).FIRST .. V_CLASS_STUD_JOINDT_ARRAY(i).LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE('Before modify- Element- ('||i||')('||j||'): ID='|| V_CLASS_STUD_JOINDT_ARRAY(i)(j));
        END LOOP;
    END LOOP;

    /* Modifying collection element values */
    V_CLASS_STUD_JOINDT_ARRAY(3)(2) := '22-JAN-2022';

    /* Accessing the collection type – After Modify */
    FOR i IN V_CLASS_STUD_JOINDT_ARRAY.FIRST .. V_CLASS_STUD_JOINDT_ARRAY.LAST
    LOOP
        FOR j IN V_CLASS_STUD_JOINDT_ARRAY(i).FIRST .. V_CLASS_STUD_JOINDT_ARRAY(i).LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE('After modify- Element- ('||i||')('||j||'): ID='|| V_CLASS_STUD_JOINDT_ARRAY(i)(j));
        END LOOP;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Program executed successfully.');
END;
/
SQL

We get the following output:

Before modify- Element- (1)(1): ID=17-10-23
Before modify- Element- (1)(2): ID=16-10-23
Before modify- Element- (2)(1): ID=16-10-23
Before modify- Element- (2)(2): ID=15-10-23
Before modify- Element- (3)(1): ID=15-10-23
Before modify- Element- (3)(2): ID=14-10-23
Before modify- Element- (4)(1): ID=14-10-23
Before modify- Element- (4)(2): ID=13-10-23
Before modify- Element- (5)(1): ID=13-10-23
Before modify- Element- (5)(2): ID=12-10-23
After modify- Element- (1)(1): ID=17-10-23
After modify- Element- (1)(2): ID=16-10-23
After modify- Element- (2)(1): ID=16-10-23
After modify- Element- (2)(2): ID=15-10-23
After modify- Element- (3)(1): ID=15-10-23
After modify- Element- (3)(2): ID=22-01-22
After modify- Element- (4)(1): ID=14-10-23
After modify- Element- (4)(2): ID=13-10-23
After modify- Element- (5)(1): ID=13-10-23
After modify- Element- (5)(2): ID=12-10-23
Program executed successfully.

PL/SQL procedure successfully completed.
SQL

To migrate the Oracle multi-dimensional collection code to PostgreSQL’s PL/pgSQL, we create equivalent arrays as domain types. PostgreSQL uses a similar loop structure, but the syntax for accessing and modifying arrays is slightly different.

We use the following code in PostgreSQL:

/* Create a DATE array */
CREATE DOMAIN STUNDENT_JOINDT_ARRAY AS DATE[];

/* Create 2D array */
CREATE DOMAIN CLASS_STUD_JOINDT_ARRAY AS STUNDENT_JOINDT_ARRAY[];

set client_min_messages = debug;

DO $$
DECLARE
    V_STUDENT_JOINDT_ARRAY STUNDENT_JOINDT_ARRAY;
    V_CLASS_STUD_JOINDT_ARRAY CLASS_STUD_JOINDT_ARRAY;
    i INTEGER := 1;
BEGIN
    /* Populate the collection */
    FOR I IN 1..5 LOOP
        FOR J IN 1..2 LOOP
            V_STUDENT_JOINDT_ARRAY[J] := CURRENT_DATE - I - J;
        END LOOP;
        V_CLASS_STUD_JOINDT_ARRAY[I] := V_STUDENT_JOINDT_ARRAY;
    END LOOP;

    /* Accessing the collection type - before modify */
    FOR I IN ARRAY_LOWER(V_CLASS_STUD_JOINDT_ARRAY, 1).. ARRAY_UPPER(V_CLASS_STUD_JOINDT_ARRAY, 1)
    LOOP
        FOR J IN ARRAY_LOWER(V_CLASS_STUD_JOINDT_ARRAY[I], 1) .. ARRAY_UPPER(V_CLASS_STUD_JOINDT_ARRAY[I], 1)
        LOOP
            RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Before modify- Element- (', I, ')(', J, '): ID=', (V_CLASS_STUD_JOINDT_ARRAY[I])[J]);
        END LOOP;
    END LOOP;

    /* Modifying collection element values */
    V_STUDENT_JOINDT_ARRAY := V_CLASS_STUD_JOINDT_ARRAY[3];
    V_STUDENT_JOINDT_ARRAY[2] := '2022-01-22';
    V_CLASS_STUD_JOINDT_ARRAY[3] := V_STUDENT_JOINDT_ARRAY;

    /* Accessing the collection type – after modify */
    FOR I IN ARRAY_LOWER(V_CLASS_STUD_JOINDT_ARRAY, 1).. ARRAY_UPPER(V_CLASS_STUD_JOINDT_ARRAY, 1)
    LOOP
        FOR J IN ARRAY_LOWER(V_CLASS_STUD_JOINDT_ARRAY[I], 1) .. ARRAY_UPPER(V_CLASS_STUD_JOINDT_ARRAY[I], 1)
        LOOP
            RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'After modify- Element- (', I, ')(', J, '): ID=', (V_CLASS_STUD_JOINDT_ARRAY[I])[J]);
        END LOOP;
    END LOOP;
    RAISE DEBUG 'Program executed successfully.';
END $$;
SQL

We get the following output:

DEBUG: Before modify- Element- (1)(1): ID=2023-10-17
DEBUG: Before modify- Element- (1)(2): ID=2023-10-16
DEBUG: Before modify- Element- (2)(1): ID=2023-10-16
DEBUG: Before modify- Element- (2)(2): ID=2023-10-15
DEBUG: Before modify- Element- (3)(1): ID=2023-10-15
DEBUG: Before modify- Element- (3)(2): ID=2023-10-14
DEBUG: Before modify- Element- (4)(1): ID=2023-10-14
DEBUG: Before modify- Element- (4)(2): ID=2023-10-13
DEBUG: Before modify- Element- (5)(1): ID=2023-10-13
DEBUG: Before modify- Element- (5)(2): ID=2023-10-12
DEBUG: After modify- Element- (1)(1): ID=2023-10-17
DEBUG: After modify- Element- (1)(2): ID=2023-10-16
DEBUG: After modify- Element- (2)(1): ID=2023-10-16
DEBUG: After modify- Element- (2)(2): ID=2023-10-15
DEBUG: After modify- Element- (3)(1): ID=2023-10-15
DEBUG: After modify- Element- (3)(2): ID=2022-01-22
DEBUG: After modify- Element- (4)(1): ID=2023-10-14
DEBUG: After modify- Element- (4)(2): ID=2023-10-13
DEBUG: After modify- Element- (5)(1): ID=2023-10-13
DEBUG: After modify- Element- (5)(2): ID=2023-10-12

DEBUG: Program executed successfully.
SQL

Things to note:

  • You can fetch multi-dimensional element values in PostgreSQL similar to Oracle. For example, V_CLASS_STUD_JOINDT_ARRAY(i)(j) can be converted to V_CLASS_STUD_JOINDT_ARRAY[i][j] in PostgreSQL when fetching or accessing values.
  • However, you can’t assign values directly to elements of a multi-dimensional array in PostgreSQL as you do in Oracle. In PostgreSQL, you need another temporary record variable to hold values and assign back the array element.

The following SQL for multi-dimensional collection assignment works fine in Oracle PL/SQL:

V_CLASS_STUD_JOINDT_ARRAY(i)(j) := sysdate-i-j;
SQL

The following multi-dimensional array assignment statement throws an error in PL/pgSQL because it’s not possible to write to a multi-dimensional array element directly in PostgreSQL:

V_CLASS_STUD_JOINDT_ARRAY[i][j] := current_date-i-j; --Incorrect
SQL

In PostgreSQL, you have to break the multi-dimensional array to a single dimensional array and assign values:

--Correct
V_STUDENT_JOINDT_ARRAY := V_CLASS_STUD_JOINDT_ARRAY[i];
V_STUDENT_JOINDT_ARRAY[j] := current_date-i-j;
V_CLASS_STUD_JOINDT_ARRAY[i] := V_STUDENT_JOINDT_ARRAY;
SQL

Migrate a composite multi-dimensional collection

Multi-dimensional structures can also be extended when table/type columns become arrays. In this case, each record can have multiple values of each with different data types, which are grouped as a single unit and make the multi-dimensional elements.

Developers often come across such scenarios when migrating Oracle PL/SQL code. Operating with a correct reasonable data structure in PostgreSQL becomes troublesome because the syntax, Oracle functions, and operators don’t work similarly in PostgreSQL.

The following code is an extension of our previous example. The collection discussed in this example is multi-dimensional as well as composite consisting of more than one attribute.

We use the following code in Oracle:

SET SERVEROUTPUT ON;

DECLARE
    TYPE SCHOOL_INFO IS RECORD (
        ADDR_TYPE  VARCHAR2(100),
        ADDR_VAL   VARCHAR2(100),
        CITY       VARCHAR2(50)
    );

    TYPE SCHOOL_INFO_ARRAY IS TABLE OF SCHOOL_INFO INDEX BY BINARY_INTEGER;
    TYPE STUDENT_SCHOOL_ADDR IS RECORD(
        ID         NUMBER,
        NAME       VARCHAR2(50),
        SCHOOL_DET SCHOOL_INFO_ARRAY
    );

    TYPE STUDENT_SCHOOL_ADDR_ARRAY IS TABLE OF STUDENT_SCHOOL_ADDR INDEX BY BINARY_INTEGER;
    V_STUDENT_SCHOOL_ADDR_ARRAY STUDENT_SCHOOL_ADDR_ARRAY;
BEGIN
    /* Populate the collection */
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).ID := 10101;
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).NAME := 'WILLIAMS';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(1).ADDR_TYPE := 'PERMANENT';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(1).ADDR_VAL := '3044 ABC LANE';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(1).CITY := 'NEW CITY';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(2).ADDR_TYPE := 'CORRESPONDENCE';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(2).ADDR_VAL := '4390 XYZ LANE';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(2).CITY := 'LOS ANGELES';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(3).ADDR_TYPE := 'OFFICE';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(3).ADDR_VAL := '2970 SEH ROAD';
    V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(3).CITY := 'ILLINOIS';
    V_STUDENT_SCHOOL_ADDR_ARRAY(2).ID := 20202;
V_STUDENT_SCHOOL_ADDR_ARRAY(2).NAME := 'JACKSON';

    /* Accessing the collection type - before modify */
    FOR J IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE('ELEMENT- '|| V_STUDENT_SCHOOL_ADDR_ARRAY(1).ID|| ', NAME:'|| V_STUDENT_SCHOOL_ADDR_ARRAY(1).NAME||', COMPLETE ADDRESS: '|| V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(J).ADDR_TYPE||'- '|| V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(J).ADDR_VAL||', '|| V_STUDENT_SCHOOL_ADDR_ARRAY(1).SCHOOL_DET(J).CITY);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('PROGRAM EXECUTED SUCCESSFULLY.');
END;
/
SQL

We get the following output:

ELEMENT- 10101, NAME:WILLIAMS, COMPLETE ADDRESS: PERMANENT- 3044 ABC LANE, NEW CITY
ELEMENT- 10101, NAME:WILLIAMS, COMPLETE ADDRESS: CORRESPONDENCE- 4390 XYZ LANE, LOS ANGELES
ELEMENT- 10101, NAME:WILLIAMS, COMPLETE ADDRESS: OFFICE- 2970 SEH ROAD, ILLINOIS
PROGRAM EXECUTED SUCCESSFULLY.

PL/SQL procedure successfully completed.
SQL

The following code block in PostgreSQL creates the types and domains required to implement the multi-dimensional composite array structure:

CREATE TYPE SCHOOL_INFO AS (
    ADDR_TYPE VARCHAR(100),
    ADDR_VAL  VARCHAR(100),
    CITY      VARCHAR(50)
);

CREATE DOMAIN SCHOOL_INFO_ARRAY AS SCHOOL_INFO[];

CREATE TYPE STUDENT_SCHOOL_ADDR AS(
    ID         INTEGER,
    NAME       VARCHAR(50),
    SCHOOL_DET SCHOOL_INFO_ARRAY
);

CREATE DOMAIN STUDENT_SCHOOL_ADDR_ARRAY AS STUDENT_SCHOOL_ADDR[];
SQL

The following code block is equivalent to an Oracle multi-dimensional composite collection and describes how to operate on a multi-dimensional composite array in a PostgreSQL database:

set client_min_messages = debug;

DO $$
DECLARE
    V_STUDENT_SCHOOL_ADDR STUDENT_SCHOOL_ADDR;
    V_STUDENT_SCHOOL_ADDR_ARRAY STUDENT_SCHOOL_ADDR_ARRAY;
    V_STUDENT_ADDR_ARRAY SCHOOL_INFO_ARRAY;
    V_STUDENT_ADDR SCHOOL_INFO;
BEGIN
    /* Populate the collection */
    V_STUDENT_SCHOOL_ADDR := V_STUDENT_SCHOOL_ADDR_ARRAY[1];
    V_STUDENT_SCHOOL_ADDR.ID := 10101;
    V_STUDENT_SCHOOL_ADDR.NAME := 'WILLIAMS';
    V_STUDENT_SCHOOL_ADDR.SCHOOL_DET[1] := ROW('PERMANENT', '3044 ABC LANE', 'NEW CITY');
    V_STUDENT_SCHOOL_ADDR.SCHOOL_DET[2] := ROW('CORRESPONDENCE', '4390 XYZ LANE', 'LOS ANGELES');
    V_STUDENT_SCHOOL_ADDR.SCHOOL_DET[3] := ROW(' OFFICE', '2970 SEH ROAD', 'ILLINOIS');
    V_STUDENT_SCHOOL_ADDR_ARRAY[1] := V_STUDENT_SCHOOL_ADDR;
    V_STUDENT_SCHOOL_ADDR := V_STUDENT_SCHOOL_ADDR_ARRAY[2];
    V_STUDENT_SCHOOL_ADDR.ID := 20202;
    V_STUDENT_SCHOOL_ADDR.NAME := 'JACKSON';
    V_STUDENT_SCHOOL_ADDR_ARRAY[2] := V_STUDENT_SCHOOL_ADDR;

    /* Accessing the collection type */
    FOR J IN 1..3 LOOP
        RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'ELEMENT- ', V_STUDENT_SCHOOL_ADDR_ARRAY[1].ID, ', NAME:', V_STUDENT_SCHOOL_ADDR_ARRAY[1].NAME, ', COMPLETE ADDRESS: ', V_STUDENT_SCHOOL_ADDR_ARRAY[1].SCHOOL_DET[J].ADDR_TYPE, '- ', V_STUDENT_SCHOOL_ADDR_ARRAY[1].SCHOOL_DET[J].ADDR_VAL, ', ', V_STUDENT_SCHOOL_ADDR_ARRAY[1].SCHOOL_DET[J].CITY);
    END LOOP;
    RAISE DEBUG 'PROGRAM EXECUTED SUCCESSFULLY.';
END $$;
SQL

We get the following output:

DEBUG: ELEMENT- 10101, NAME:WILLIAMS, COMPLETE ADDRESS: PERMANENT- 3044 ABC LANE, NEW CITY
DEBUG: ELEMENT- 10101, NAME:WILLIAMS, COMPLETE ADDRESS: CORRESPONDENCE- 4390 XYZ LANE, LOS ANGELES
DEBUG: ELEMENT- 10101, NAME:WILLIAMS, COMPLETE ADDRESS: OFFICE- 2970 SEH ROAD, ILLINOIS
DEBUG: PROGRAM EXECUTED SUCCESSFULLY.
SQL

Clean up

When you’re finished experimenting with the scenarios described in this post, you can clean up your PostgreSQL database by using following code to delete all the objects created in this example.

DROP SCHEMA stud_marks CASCADE;
DROP SCHEMA stud_grades CASCADE;
DROP SCHEMA stud_info CASCADE;
DROP DOMAIN CLASS_STUD_JOINDT_ARRAY;
DROP DOMAIN STUNDENT_JOINDT_ARRAY;
DROP DOMAIN STUDENT_SCHOOL_ADDR_ARRAY;
DROP TYPE STUDENT_SCHOOL_ADDR;
DROP DOMAIN SCHOOL_INFO_ARRAY;
DROP TYPE SCHOOL_INFO;
DROP TABLE Student_Marks;
SQL

Conclusion

In this post, we showed how to use PostgreSQL-native features to efficiently migrate Oracle simple collections, composite collections, and multi-dimensional collections to PostgreSQL. It’s important to note that the specifics of the migration process will depend on the complexity of your Oracle collections, the size of your dataset, and the intricacies of your application. We shared instructions for migrating Oracle collections to Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL and also provided code examples, SQL scripts, and best practices that you can use while migrating Oracle collections.

If you have any feedback, comments, or questions, share them in the comments section.

About the Authors

Sashikanta Pattanayak works as a Lead Consultant with the Professional Services team at AWS, specializing in database modernization, optimization, and migration strategies. His role involves collaborating with clients to architect scalable, resilient, and secure solutions within the AWS cloud environment. He specializes in homogeneous and heterogeneous database migrations.

Vinay Paladi is a Lead Database Consultant with the Professional Services team at Amazon Web Services. He works as a database migration specialist, helping and enabling customers to build highly available, cost-effective database solutions and migrate their commercial engines to the AWS Cloud. He is passionate about building innovative solutions to accelerate the database journey to cloud.

Anuradha Chintha is a Lead Consultant with Amazon Web Services. She works with customers to build scalable, highly available, and secure solutions in the AWS Cloud. Her focus area is homogeneous and heterogeneous database migrations.