Migrating schemas and data from Oracle DB to Postgres Pro Enterprise is a rocky road full of countless pitfalls along the way. Despite all the complexity, the process gets much smoother thanks to Postgres Pro Enterprise’s extended features that mimic Oracle DBMS mechanisms missing from the vanilla version.

There is also the open-source utility ora2pg for automatic migration, but it doesn’t support these extended Postgres Pro Enterprise features. That’s exactly why we created ora2pgpro, a tool designed specifically for migrating from Oracle to Postgres Pro Enterprise while taking its unique features into account.

In this article, we’ll go over the challenges an automated migration tool must overcome, why ora2pg can’t fully cover all our needs, and finally, what this “beast” called ora2pgpro is all about.

Roadblocks when migrating to vanilla PostgreSQL

The first question: what objects actually need to be exported?

Obviously, all DB objects underpinning the business logic must be migrated: tables, indexes, PL/SQL functions, autonomous transactions, packages, schemas, and so on. It’s also crucial to ensure that the target DB technologies fully match the functionality of Oracle DB. Otherwise, the business logic simply won’t work correctly.

At the very least, vanilla PostgreSQL has no native support for packages, autonomous transactions, and other mechanisms at the core level (see Figure 1). This alone can make migration a lot harder. By contrast, as we’ll soon see, Postgres Pro Enterprise does not suffer from these shortcomings.

Figure 1. Classification of Oracle objects and whether vanilla PostgreSQL has an equivalent
Figure 1. Classification of Oracle objects and whether vanilla PostgreSQL has an equivalent

Since DB structure often encodes complex and elaborate business logic, manual conversion is not a good option — the time sink would be colossal. That’s why building automated DB converters like ora2pg and ora2pgpro was only a matter of time.

What are ora2pg and ora2pgpro?

ora2pg is a free utility that automatically converts Oracle DB schemas to PostgreSQL. It offers broad functionality to automate most schema migration tasks. It can export a wide variety of DB objects, including tables, user rights, schemas, BLOBs, PL/SQL code, and more. It can even estimate migration costs by identifying objects that can’t be converted automatically.

Migrating to vanilla PostgreSQL is all well and good — but what about Postgres Pro Enterprise? That’s where ora2pgpro comes in: our commercial version of ora2pg, which takes into account Oracle-like features implemented in Postgres Pro Enterprise.

The main enhancements that set ora2pgpro apart are:

  1. Support for autonomous transactions in Postgres Pro Enterprise.

  2. Support for Postgres Pro Enterprise packages.

  3. Support for associative arrays via the enterprise extension pg_variables.

  4. A more advanced algorithm for translating PL/SQL into PL/pgSQL

We’ll now explore common migration pain points when using ora2pg and how ora2pgpro solves them when migrating to Postgres Pro Enterprise.

Support for autonomous transactions

ora2pg imitates autonomous transactions, though vanilla PostgreSQL has none. Postgres Pro Enterprise, however, does.

Autonomous transactions are special sub-transactions that can be committed or rolled back independently of their parent transaction. This means results (e.g., logging or auditing) become visible immediately after completion, even if the parent transaction is still running or later rolled back. Imitating them via dblink requires opening new connections — which impacts performance. Using pg_background isn’t much better: every autonomous transaction launches a background process, which is also costly in terms of performance.

These imitation issues pushed us to implement autonomous transactions directly in the Enterprise core. Our native implementation is significantly faster than the two hacks above. Of course, ora2pg cannot leverage this.

Figure 2. Autonomous transaction implementations
Figure 2. Autonomous transaction implementations

By contrast, ora2pgpro can directly export autonomous transactions in Postgres Pro format. All you need is to set POSTGRESPRO_ATX=1.

Support for packages

Unlike Oracle DB, vanilla PostgreSQL doesn’t have packages. This is a major stumbling block in migrations. Fortunately, ora2pg does provide package export, but only by workarounds: it simulates packages using schemas, and global variables are emulated with current_setting and set_config. This workaround adds significant overhead, hurts performance, and often leads to semantic mismatches.

Postgres Pro Enterprise solves this by adding true package support at the core level, complete with real package variables and scoping. However, ora2pg cannot export into this model.

ora2pgpro, on the other hand, fills this gap — it can export Oracle packages as Postgres Pro packages directly. To enable this mode, just set TYPE=PACKAGE in the configuration.

In short, ora2pgpro automatically converts each Oracle package into its own schema, preserving modularity and encapsulation. Unlike ora2pg’s hacks, ora2pgpro supports automatic package initialization via init(), as well as public/private method separation. These features are built right into the Postgres Pro Enterprise core and are fully documented. This way, we keep the same kind of modularity and encapsulation you’d expect from Oracle packages — while still delivering native, high-performance operation in the Postgres Pro Enterprise environment. The end result is PL/pgSQL code that works natively and efficiently inside Postgres Pro Enterprise.

For a deeper dive, we recommend Igor Melnikov’s detailed article, which explains how package emulation works under the hood.

Support for associative arrays

Similarly to packages, vanilla PostgreSQL doesn’t support indexed collections, which complicates Oracle migration. That’s also why the ora2pg won’t really help here — it simply can’t interpret collections the right way.

That’s why we built the pg_variables module, which includes associative array support. ora2pgpro maps Oracle associative arrays directly onto pg_variables collections. Oracle collection methods are converted into pg_variables function calls.

For example:

  • Setting an array element in Oracle → pgv_set_elem in pg_variables

  • Removing an element → pgv_remove_elem

  • Checking element existence → pgv_exists_elem

Figure 3 illustrates these conversions (ASSOC is the name of the package where the collection is defined).

Figure 3. Mapping Oracle database operations to pg_variables collection extensions
Figure 3. Mapping Oracle database operations to pg_variables collection extensions

For more details, see the documentation.

Support for VARRAYs

In addition to associative arrays, ora2pgpro can interpret VARRAYs, which often stump open-source ora2pg. Thanks to deeper syntactic analysis, ora2pgpro handles VARRAYs correctly, mapping them to either native Postgres Pro Enterprise arrays or pg_variables when needed.

Let’s take a look at an example of translating a VARRAY array using ora2pgpro:

-- PL/SQL
 
TYPE r_customer_type IS RECORD (
customer_name VARCHAR2(50),
credit_limit NUMBER(10,2)
…
 
t_customers t_customer_type := t_customer_type();
…
 
t_customers.EXTEND;
 
t_customers(t_customers.LAST).customer_name := 'ABC Corp';
 
t_customers(t_customers.LAST).credit_limit := 10000;
 
tmp_string := 'The number of customers is ' || t_customers.COUNT;
-- PL/pgSQL
 
CREATE TYPE PKGC.r_customer_type AS (
customer_name varchar(50),
credit_limit numeric(10,2)
);
…
 
/*WARNING: collection constructors are not supported.*/
 
t_customers PKGC.t_customer_type /*:=*/ /*t_customer_type() /*WARNING: varray constructors are not supported.*/*/;
…
 
t_customers = array_cat(t_customers, array_fill(NULL::PKGC.R_CUSTOMER_TYPE, ARRAY[1]));
 
t_customers[array_upper(T_CUSTOMERS, 1)].customer_name = 'ABC Corp';
 
t_customers[array_upper(T_CUSTOMERS, 1)].credit_limit = 10000;
 
tmp_string = 'The number of customers is ' || array_length(T_CUSTOMERS, 1);

In this example, we see the declaration of a VARRAY called t_customer_type, its initialization, the use of the EXTEND method to add elements, assigning values to record fields inside the VARRAY, as well as the use of LAST and COUNT.

The ora2pgpro tool can correctly translate these operations into equivalent PL/pgSQL constructs, leveraging the native features of Postgres Pro Enterprise for working with arrays and records — or, when needed, the pg_variables functions for more complex scenarios.

You probably noticed the warning comment that ora2pgpro inserted. The tool doesn’t just blindly replace old constructs with new ones by template; it actually analyzes the source code. Yes, ora2pgpro really does analyze the code, working on a completely different principle compared to the open-source tool ora2pg.

Advanced PL/SQL → PL/pgSQL translation

Migration issues often pop up when converting PL/SQL code into PL/pgSQL. The ora2pg tool can technically handle this translation, but its implementation leaves much to be desired: the conversion relies on primitive Perl regular expressions. Because of this rather "naive" export algorithm, ora2pg frequently produces incorrect translations of PL/SQL code.

The ora2pgpro tool, however, doesn’t suffer from this flaw — it actually parses the syntax and semantics of PL/SQL code, which allows it to translate much more complex constructs.

The pipeline is:

  1. Extract PL/SQL code from Oracle DB.

  2. Parse the code into an AST.

  3. Convert AST into PL/pgSQL code.

  4. Leave warnings for any unconvertible fragments. The developer must then manually review and handle these cases.

Figure 4. Algorithm for PL/SQL → PL/pgSQL translation
Figure 4. Algorithm for PL/SQL → PL/pgSQL translation

This AST-based approach — grounded in real syntax and semantic analysis — ensures a much higher accuracy of conversion. It also gives ora2pgpro developers the flexibility to extend support for new constructs and improve existing transformations in future releases. It’s worth noting that ora2pgpro is a commercial product, and any unauthorized modifications of its code by end users are not allowed and may violate the licensing terms.

Note: ora2pgpro supports Oracle DB versions up to 11.2 (most common in Russia). It doesn’t handle newer Oracle features — but it still works fine with Oracle 23 where applicable.

Handling complex package specifications

One of the key strengths of ora2pgpro is its ability to analyze the syntax and semantics of PL/SQL code, which makes it possible to accurately translate even very complex and diverse declarations in package specifications. Unlike the primitive regular expressions used in ora2pg, our AST-based approach delivers a much higher level of precision in conversion.

Let’s look at a few examples of how package specification fragments are translated to showcase the wide range of supported data types and constructs.

For instance, ora2pgpro can correctly interpret temporal data types with varying precision, time zones, and intervals:

-- PL/SQL
 
    my_date1 DATE := TO_DATE('2023-08-31', 'YYYY-MM-DD');
 
    my_date2 DATE := my_date + 34;

 
    my_timestamp1 TIMESTAMP(1);
 
    my_timestamp2 TIMESTAMP WITH TIME ZONE;
 
    my_timestamp3 TIMESTAMP WITH LOCAL TIME ZONE;
 
    my_timestamp4 TIMESTAMP(9) WITH TIME ZONE;

    my_interval1 INTERVAL DAY TO SECOND(4);
 
    my_interval2 INTERVAL DAY(3) TO SECOND(2);
-- PL/pgSQL
 
    my_date1 timestamp := TO_DATE('2023-08-31', 'YYYY-MM-DD');
 
    my_date2 timestamp := DATETIME_PKG.my_date + 34;
 
 
    my_timestamp1 timestamp;
 
    my_timestamp2 timestamp with time zone;
 
    my_timestamp3 timestamp with time zone;
 
    my_timestamp4 timestamp with time zone;

 
    my_interval1 interval DAY TO SECOND;
 
    my_interval2 interval DAY/*(3)*/ TO SECOND;

Our tool can also handle the conversion of complex Boolean expressions:

-- PL/SQL
 
varchar_var VARCHAR2(100) := 'test';
 
char_var CHAR(10) := 'char2';
 
bool_var1 BOOLEAN := varchar_var LIKE 'ssdsd' || char_var;
 
bool_var2 BOOLEAN;
 
bool_var3 BOOLEAN := TRUE;
 
var1 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1,2,3,4,5,6,7,8);
 
bool_var3 BOOLEAN := bool_var2 AND TRUE OR
 
    (UPDATING('ID_TABLE') AND NOT var1.EXISTS(3)) OR
 
    var1.EXISTS(8);
-- PL/pgSQL
 
varchar_var varchar(100) := 'test';
 
char_var char(10) := 'char2';
 
bool_var1 BOOLEAN := BOOL_PKG.varchar_var LIKE 'ssdsd' || BOOL_PKG.char_var;
 
bool_var2 BOOLEAN;
 
bool_var3 BOOLEAN := TRUE;
 
var1 SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(1,2,3,4,5,6,7,8);
 
bool_var3 BOOLEAN := BOOL_PKG.bool_var2 AND TRUE OR
 
    (UPDATING('ID_TABLE') AND NOT var1.EXISTS(3)) OR
 
    var1.EXISTS(8);

In addition to temporal types and complex Boolean expressions, ora2pgpro can accurately recognize and translate user-defined data types such as VARRAY and RECORD, complex variable initializations, constant declarations, and many other constructs.

The tool ensures that all these elements are properly mapped, preserving the semantic consistency of Oracle data types in Postgres Pro Enterprise, and converting intricate expressions and method calls into their PL/pgSQL equivalents. This greatly reduces the amount of manual cleanup required after automated migration.

Conclusion

Our experience with migrations from Oracle to Postgres Pro Enterprise highlights just how crucial automation and tool adaptation are for the target database system. 

ora2pgpro effectively handles the export of complex objects, delivering support for functionality that vanilla PostgreSQL simply doesn’t provide. The result is a migration process that’s both more reliable and performant, opening the door to more robust database infrastructure and significantly lowering operational risks.