ora2pgpro is a utility based on the ora2pg application, which can be used for porting Oracle packages and autonomous transactions as Postgres Pro packages and autonomous transactions when migrating an Oracle database to a Postgres Pro-compatible schema. ora2pgpro properly extracts type and variable declarations from the package body and specification, including the initialization section. Moreover, ora2pgpro exports autonomous transactions directly as opposed to ora2pg's translation of autonomous transactions into wrapper functions using dblink or pg_background extensions. It's worth noting that autonomous transactions are syntactically very different in Oracle and Postgres Pro. Currently, circular dependencies of packages are not supported.

To export packages using ora2pgpro, first edit the ora2pgpro.conf file.

Now you can run ora2pgpro.

ora2pgpro -t PACKAGE -c ora2pgpro.conf -o packages.sql

A short example below shows how the CUSTOMER_PKG package can be ported using ora2pgpro. This is the Oracle version:

CREATE OR REPLACE PACKAGE customer_pkg IS

  gBatchSize constant integer := 100;

  type
    TCustomerInfo is record
    (
      Id          number(9,0),
      Name        varchar2(32 char),
      Description varchar2(512 char)
    );

  type
    TBuffer is table of TCustomerInfo index by pls_integer;
  v_gBuffer TBuffer;

  v_cMainCustomer constant number(9,0) := 1;

  v_gCurrentCustomerId number(9,0);

...

  PROCEDURE dump(v_pCustomer in out nocopy TCustomerInfo);

  PROCEDURE init_package;
END;

CREATE OR REPLACE PACKAGE BODY customer_pkg IS

...

  PROCEDURE dump(v_pCustomer in out nocopy TCustomerInfo) IS
  BEGIN
    console.log('------------------  Dump of customer_pkg.TCustomerInfo  ------------------------');
    console.log('Id          => ' || v_pCustomer.Id);
    console.log('Name        => ' || v_pCustomer.Name);
    console.log('Description => ' || v_pCustomer.Description);
    console.log('--------------------------------------------------------------------------------');
  END;

  PROCEDURE init_package IS
  BEGIN
    v_gCurrentCustomerId := 0;
  END;

BEGIN
  init_package();
END;

Here is how this package would end up in Postgres Pro:

-- Oracle package 'CUSTOMER_PKG' declaration, edit to match PostgreSQL syntax

DROP SCHEMA IF EXISTS customer_pkg CASCADE;
CREATE SCHEMA IF NOT EXISTS customer_pkg;
CREATE TYPE customer_pkg.tcustomerinfo AS (
Id          integer,
      Name        varchar(32),
      Description varchar(512)

);

CREATE TYPE customer_pkg.tbuffer AS (tbuffer CUSTOMER_PKG.TCustomerInfo[]);

CREATE OR REPLACE FUNCTION customer_pkg.__init__ () RETURNS VOID AS $body$
#import CONSOLE
DECLARE

	gBatchSize constant integer := 100;
	v_gBuffer CUSTOMER_PKG.TBuffer;
	v_cMainCustomer constant integer := 1;
	v_gCurrentCustomerId integer;

BEGIN
  CALL customer_pkg.init_package();
end;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON FUNCTION customer_pkg.__init__ () FROM PUBLIC;

CREATE OR REPLACE PROCEDURE customer_pkg.init_package () AS $body$
#package
BEGIN
    v_gCurrentCustomerId := 0;
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE customer_pkg.init_package () FROM PUBLIC;

CREATE OR REPLACE PROCEDURE customer_pkg.dump (v_pCustomer INOUT CUSTOMER_PKG.TCustomerInfo) AS $body$
#package
BEGIN
    CALL console.log('------------------  Dump of customer_pkg.TCustomerInfo  ------------------------');
    CALL console.log('Id          => ' || v_pCustomer.Id);
    CALL console.log('Name        => ' || v_pCustomer.Name);
    CALL console.log('Description => ' || v_pCustomer.Description);
    CALL console.log('--------------------------------------------------------------------------------');
  end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;
-- REVOKE ALL ON PROCEDURE customer_pkg.dump (v_pCustomer INOUT CUSTOMER_PKG.TCustomerInfo) FROM PUBLIC;

Complete package examples are available at ora2pgpro.postgrespro.ru. The scripts are distributed under the PostgreSQL license.