Ispirer Website
Ispirer Capabilities: PostgreSQL Migration
Free Trial
Table Types Conversion from Oracle to PostgreSQL Using Arrays
Oracle provides a very convenient mechanism for working with table data types. Unfortunately, this can be problematic in other databases. PostgreSQL, for example, does not support table types, so the question of how exactly to convert such types is very important.
There are several ways to convert table data types in PostgreSQL. The first one is conversion using temporary tables. This solution is quite straightforward, but it has one big drawback - it clutters up the code a lot and makes it very difficult to read. After all, simple operations will be replaced by querying from the temporary table.
The second option is to convert table types using arrays in PostgreSQL.
This article describes the main features and implementation of table type conversion using arrays in PG. To better understand the principles of conversion, let's convert the following example:
CREATE TYPE employee AS OBJECT ( id NUMBER, Name VARCHAR(300) ); CREATE TYPE employees_tab IS TABLE OF employee; CREATE OR REPLACE PROCEDURE hire(EMPLOYEES in out employees_tab, id NUMBER, Name VARCHAR) AS NEW_EMPLOYEES employees_tab := employees_tab(); BEGIN EMPLOYEES.Extend(1); EMPLOYEES(EMPLOYEES.count) := employee(id, Name); FOR i IN EMPLOYEES.first..EMPLOYEES.last LOOP INSERT INTO emp_tab values (EMPLOYEES(i).id, EMPLOYEES(i).Name); END LOOP; INSERT INTO EMP_TAB SELECT * FROM TABLE(EMPLOYEES); NEW_EMPLOYEES.Extend(EMPLOYEES.count); NEW_EMPLOYEES.Delete; END;
An example of converting this procedure using arrays in PG is given below:
CREATE TYPE EMPLOYEE AS(id DOUBLE PRECISION,Name VARCHAR(300)); -- CREATE TYPE EMPLOYEES_TAB IS TABLE OF employee; CREATE OR REPLACE PROCEDURE HIRE(INOUT EMPLOYEES EMPLOYEE[] , ID DOUBLE PRECISION, NAME VARCHAR(4000)) LANGUAGE plpgsql AS $$ DECLARE EMPLOYEES_REC EMPLOYEE; NEW_EMPLOYEES EMPLOYEE[] default array[]::EMPLOYEE[] ; BEGIN EMPLOYEES[swf_array_length(EMPLOYEES)+1] := null; EMPLOYEES[swf_array_length(EMPLOYEES)] := row(ID,NAME); FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1) LOOP EMPLOYEES_REC := EMPLOYEES[i]; INSERT INTO EMP_TAB values(EMPLOYEES_REC.ID, EMPLOYEES_REC.NAME); EMPLOYEES[i] := EMPLOYEES_REC; END LOOP; INSERT INTO EMP_TAB SELECT * FROM unnest(EMPLOYEES); for i in 1 .. swf_array_length(EMPLOYEES) loop NEW_EMPLOYEES[swf_array_length(NEW_EMPLOYEES)+1] := null; end loop; NEW_EMPLOYEES := array[]:: EMPLOYEE[]; END; $$;
To better understand the conversion mechanism, let's go through the conversion of each method from the example separately:
Table data types conversion
Table data types themselves are not converted. Instead of using them in the code, arrays of the type from which the given table type is composed will be used everywhere:
Oracle | PostgreSQL |
---|---|
employees_tab | EMPLOYEE[] |
Declaration and initialization of table type variables
Instead of initializing a variable in Oracle, in PostgreSQL we fill this variable with an empty array of the given type:
Oracle | PostgreSQL |
---|---|
new_employees employees_tab := employees_tab(); | NEW_EMPLOYEES EMPLOYEE[] default array[]::EMPLOYEE[]; |
Count method conversion
To convert the Count method, an additional swf_array_length function was developed, which will be automatically generated and created in the PostgreSQL database during the conversion process.
Oracle | PostgreSQL |
---|---|
EMPLOYEES.count | swf_array_length(EMPLOYEES) |
Extend method conversion
Instead of the Extend method, we just add an empty element to the end of the array.
Oracle | PostgreSQL |
---|---|
EMPLOYEES.Extend(1); EMPLOYEES.Extend (n); | EMPLOYEES[swf_array_length(EMPLOYEES)+1] := null; for i in 1 .. n loop EMPLOYEES[swf_array_length(EMPLOYEES) + 1] := null; end loop; |
Last and First methods conversion
To convert a.first and a.last use array_lower(a ,1) and array_upper(a,1) respectively.
Oracle | PostgreSQL |
---|---|
FOR i IN EMPLOYEES.first..EMPLOYEES.last | FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1) |
Initialization of the TYPE IS TABLE element
To initialize an array element, we use the row() constructor.
Oracle | PostgreSQL |
---|---|
EMPLOYEES(EMPLOYEES.count) := employee(id, Name); | EMPLOYEES[swf_array_length(EMPLOYEES)] := row(ID,NAME); |
Delete method conversion
Instead of completely removing all rows from a variable, we simply fill the variable with an empty array in PostgreSQL.
Oracle | PostgreSQL |
---|---|
NEW_EMPLOYEES.Delete; | NEW_EMPLOYEES := array[]:: EMPLOYEE[]; |
Table() function conversion
Instead of the TABLE function, which allows us to refer to a table variable as a table, we use the UNNEST function in PostgreSQL. Instead of pseudo-column column_value - unnest in PostgreSQL.
Oracle | PostgreSQL |
---|---|
SELECT * FROM TABLE(EMPLOYEES) | SELECT * FROM unnest(EMPLOYEES) |
Field Access to a TYPE IS TABLE Composite Element
Oracle allows you to refer to a field of an array's composite element by its element number(EMPLOYEES( i ).id).
This is not possible for PostgreSQL version 13 and below. In this case, to access the field of array you must first put this element in a new variable, and then access its field:
Oracle | PostgreSQL |
---|---|
EMPLOYEES(i).id | DECLARE EMPLOYEES_REC EMPLOYEE; EMPLOYEES_REC := EMPLOYEES[i]; EMPLOYEES_REC.ID |
For version of PostgreSQL 14 and higher the access to element by its index is allowed:
Oracle | PostgreSQL |
---|---|
EMPLOYEES(i).id | EMPLOYEES[i].id |
This solution not only allows you to efficiently convert table types, but also makes the code much more accurate compared to converting using temporary tables. Also, this conversion mechanism can be used when converting VARRAYS types.
Developers who are familiar with how table types work in Oracle and array types in PG may have noticed that there is one significant difference between them - Table type variables can have empty elements in the middle (empty strings), while in PG all array elements must go in order. This problem can be avoided by a small change in the logic in the sql code.
Using ths solution it is possible to convert not only such simple cases, but also more complex cases when an element is accessed in an array of arrays, etc.
This article describes the basic principles of converting table data types and their use from Oracle to PostgreSQL using arrays.
Our tool supports converting table types both with temporary tables (by default) and with arrays.
In order to use solution with arrays, please use the option (Guide on how to set options in SQLWays Wizard):
[Postgre]
Table_type_conversion=Arrays
In this case the arrays will be used instead of types, and the types will not be created in PostgreSQL. The “Arrays” is default value for this option. The empty values is equal to “Arrays” value.
If the option is set to “Tables”, then the temporary tables will be used.
If you have any questions or face any difficulties, please contact our support team: support@ispirer.com