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 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:

Instead of initializing a variable in Oracle, in PostgreSQL we fill this variable with an empty array of the given type:

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.

Instead of the Extend method, we just add an empty element to the end of the array.

To convert a.first and a.last use array_lower(a ,1) and array_upper(a,1) respectively.

To initialize an array element, we use the row() constructor.

Instead of completely removing all rows from a variable, we simply fill the variable with an empty array in PostgreSQL.

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 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:

For version of PostgreSQL 14 and higher the access to element by its index is allowed:

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