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’;
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);
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);
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;
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;
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);
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;
/
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;
/
We get the following output:
1:75
2:100
3:50
PL/SQL procedure successfully completed.
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;
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 $$;
We get the following output:
DEBUG: 1:75
DEBUG: 2:100
DEBUG: 3:50
DEBUG: Program executed successfully.
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;
/
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;
/
We get the following output:
MATHS:50
COMPUTERS:50
PHYSICS:100
ENGLISH:100
PL/SQL procedure successfully completed.
You can migrate the aforementioned Oracle code to PostgreSQL by completing the following steps:
Create a user-defined type with the same structure as the Oracle user-defined type.
Create a domain in PostgreSQL using the created type.
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;
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 $$;
We get the following output:
DEBUG: MATHS:50
DEBUG: COMPUTERS:50
DEBUG: PHYSICS:100
DEBUG: ENGLISH:100
DEBUG: Program executed successfully.
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;
/
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;
/
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.
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;
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 $$;
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.
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;
/
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.
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 $$;
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.
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;
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
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;
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;
/
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.
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[];
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 $$;
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.
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;
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.
AWS Database Blog
