Blog: Comparing Ora2Pg with АWS SCT for Oracle to PostgreSQL migration

I’m seeing an increasing trend with our customers running Oracle databases wanting to reduce costs by moving to PostgreSQL. The open-source community has made innovations over the years to increase compatibility with Oracle database through with new features and extension libraries to make the migration easier. I felt that by comparing Ora2Pg with AWS SCT, people wanting to migrate Oracle to PostgreSQL for running on AWS would benefit anyone attempting this effort.

As a company recognized for our database migration competence, we are often asked by the customers for recommendations on the best products to use. We monitor new products on the market, and when we can’t find the right tool, we build our own. Our team had been working with the AWS Schema Conversion Tool (SCT) since it is launch. We’ve shared our experience and feedback over the years to AWS to improve SCT. When we saw issues pop-up on various forums regarding SCT, we created a series of blogs to help others use SCT more effectively for their migration efforts. We have successfully used SCT on numerous large scale projects for migrating Oracle database applications to AWS RDS/Aurora PostgreSQL. I’m especially proud of the achievements of our team to be recognized by AWS as one of only three partners for the AWS Database Freedom For ISVs program.

Recently, I’ve been hearing from potential customers who have raved about the open-source tool Ora2Pg and prefer it over AWS SCT for migrating to the AWS cloud.

I got curious and asked our migration specialists to use our technology comparison framework to perform a deep analysis and comparison of the tools to understand how we might be able to use the best features that both tools offer. To make a thorough and honest comparison, I’ve tasked my team to run both tools against diverse Oracle databases that we used to train our Oracle Migration Practice team members.

Creating a comparison methodology for differentiating technologies based on organizational needs

In order to provide a fair comparison, it’s important to define the criteria to use for assessing the capabilities of any technology. In this comparison, we will evaluate the features from the perspective of an AWS Database Migration Service Delivery partner with a dedicated migration practice. AWS counts on partners like DB Best to provide flawless service to its customers for migrating large-scale migrations for mission-critical and business-critical applications using Oracle Database technology to services like Amazon Aurora. At DB Best, we use our 12-step Migration Methodology to define the scope of customer projects. In particular, we will focus on the suitability of the two tools for our ease of use, assessment, database schema conversion, data migration, application conversion, CI/CD integration to support test automation, and product support.

For customers looking to do an Oracle to PostgreSQL migration to AWS using their development team, the criteria would place greater emphasis on features related to ease of use. We’ll include some highlights for first-time users of the tools to consider. Our Oracle migration practice team uses both tools for Oracle to PostgreSQL migrations. We rely on Ora2Pg for migrating Oracle databases to PostgreSQL running outside of the AWS cloud since the AWS Customer Agreement restricts the use of AWS licensed software to AWS services as per section 8.4.

Providing a quantitative way of evaluating technologies

One of the challenges that published comparisons of technologies has to deal with how to assign value to a capability. Using values o through 10 appear to have a subjective slate. Six Sigma practitioners solved this problem with the 0-1-3-9 ranking scale that makes it easier to force a decision on a score. David O. Hunt, PE, discusses in his paper Popping a Project – Using a 1-3-9 Ranking System in Selecting a Project how to use this ranking scale to prioritize projects with limited resources. This scale works very well for deciding how technology meets the criteria established for evaluation.

We use the following definitions for 0-1-3-9 to easily rate a capability:

  1. Does not support the criteria.
  1. Supports the criteria, but requires workarounds or external tools to meet the criteria.
  1. Supports the criteria.
  1. Exceeds the criteria with exceptional or visionary capabilities not found elsewhere.

By using a Power-of-3 scale, it provides a way to clearly differentiate supporting the feature versus a visionary feature. This scale also accounts for greater importance on supporting the criteria versus needing workarounds or not supporting the criteria at all.

Using the 0-1-3-9 scale for weighting capability importance

It turns out that the same 0-1-3-9 scale works well for determining the importance or weight factor for capabilities. Let’s take a look at our 12-step migration methodology plan.

DB Best Technologies 12-step migration process

The percentage values used in the Effort breakdown column represents the level of effort needed to complete a migration project based on the tools we use and the maturity of our migration practice. Ora2Pg and SCT support to some extent our envisioning/assessment, database schema conversion, application conversion/remediation, scripts conversion, and data migration. This is how we would apply the weighting scale to these steps:

  • Assessment — 1
  • Database schema conversion — 9
  • Application conversion/remediation — 9
  • Scripts conversion — 3 — Since both products support loading scripts and converting them, we used the Database schema conversion as a proxy since the same issues will exist
  • Data migration — 1

I decided on a value of 9 for Database schema conversion because our experience using both Ora2Pg and SCT gives us a competitive advantage which makes the percentage lower than unexperienced migration practitioners.

For ease of use capabilities, I used a value of 1 since anything to save time on a migration project means a happy customer. We know very well that high-quality tools can save hundreds or thousands of hours to our team, so every detail counts.

Let the bake-off begin!

Summary of Comparing Ora2Pg with AWS SCT

For each of the capabilities covered in the comparison, I define the criteria that I provided to our migration team members for evaluating the capabilities. The team then provided examples of how each of the products supports the criteria and the rating with respect to the product. Here is a summary of the results that uses the sum of the 0-1-3-9 different criteria ratings within the capabilities and then multiplied the total by the weighting factor.

Here are the overall results of comparing Ora2Pg with AWS SCT for migrating Oracle to PostgreSQL on AWS.

Product areaArea weightOra2Pg Total scoreOra2Pg Weighted scoreAWS SCT Total scoreAWS SCT Weighted score
Ease of use1551818
Assessment1551414
Database schema conversion93228896864
Application conversion900654
Data migration1881818
Overall Assessment Total50306149968

As you can see, AWS SCT provides greater capabilities across the board over Ora2Pg.

First, let’s start out with what versions we tested.

Versions of Ora2Pg and AWS SCT tested

This section includes the software versions used for comparing Ora2Pg with AWS SCT.

Ora2Pg specifications

For Ora2Pg, our team used the deployment scripts in GitHub for the darold / ora2pg master branch known as version 20.0. Since our evaluation took some time, we went over the GitHub issues and changelogs to see if there were any new fixes that addressed any errors that we found with Ora2Pg.

Since we use Ora2Pg, we plan to create new issues for each of the errors so that the Oracle migration community can benefit from this blog post.

AWS SCT specifications

For SCT, we used AWS SCT build #1.0.640. On July 17, 2020, AWS released AWS SCT build #1.0.641 that included new emulation packages for migrating Oracle to Aurora PostgreSQL. We did some quick testing to confirm that the new changes and updated the results in our findings.

We’ve already communicated our findings for any issues related to AWS SCT through our contacts at AWS given our migration partner status.

The database used for comparison

In comparing Ora2Pg with AWS SCT, we wanted to set up a fair comparison using one of our benchmark databases TEST_ORA_PG. This database includes a wide variety of PL/SQL code structures. This database is not huge in size but is very interesting because it is rich in PL/SQL structures very popular with Oracle developers. We built this database over the years, starting from 2003 when we developed the DB Best Migration Suite. Later Microsoft acquired this product to make it a foundation for the SSMA product line.

The choice of the database for this bake-off is very important because the result can be skewed easily focusing on specific capabilities (or lack of them) in each of the tools. We know that if the tool can handle this database well, it will also do a good job with most customer applications. Our Oracle migration practice team routinely add to the TEST_ORA_PG database as we encounter Oracle constructs that we haven’t seen as a way to extend our body of knowledge.

Here are some key characteristics of this database for your review:

TEST_ORA_PG database key characteristics

We ran both tools against this database and looked at all aspects of the process: product ease of use, feature set, quality of conversion, correctness, and performance of converted to PostgreSQL code, depth of coverage of PL/SQL syntax, etc.

Ease of use

When it comes to ease of use for migration products, the table below contains the list of things that reduce the time it takes to come up with a solid migration release for our customers.

Product areaOra2Pg scoreOra2Pg capabilityAWS SCT scoreAWS SCT capability
Support for multiple operating platforms1No support for MacOS, and unavailable features in Windows OS. See more below.3Supports Windows, MacOS, and popular Linux Distros. All features work across supported operating systems.
Ease of installation1Windows needs extra software. Requires a build process for Perl.3Installation packages work as expected.
Good defaults1Requires knowing over 90 settings for CLI interface for different migration actions. Defaults for actual migrations may provide misleading results. See more below.3GUI provides defaults based on migration actions. Settings persisted in the project file. CLI uses the project file for automation.
Command-line interface (CLI)1Perl shell used for CLI runtime environment. Credentials stored in plain text in ora2pg.conf file forces a value of 1.3AWS requires customers to make a support request. See more below. Credentials stored as encrypted strings in the project file.
Graphical user interface (GUI)0Ora2Pg provides no GUI interface for examining the source and target databases and performing common migration tasks.3AWS SCT provides a rich GUI which our team members use as their default environment.
Product support1Uses the GitHub project used for issue tracking and product support. Gilles Darold makes the vast majority of the code changes and product support.3AWS SCT has a product management and development team making updates on a regular basis since its official release in Oct 2015. Users can opt-in to the telemetry program to share error information with the AWS development team. AWS uses this data to prioritize fixes. Partners like DB Best can make suggestions to the development team.
Ease of use totals518

Additional information for Ora2Pg and SCT comparisons

  • Support for multiple operating platforms – Ora2Pg
    • Ora2Pg has two options that are disabled for the Windows operating system.
      • JOBS – Used to set the number of cores to use for parallel data import into PostgreSQL
      • ORACLE_COPIES – Adds multiple thread support to extract data from Oracle.
  • Ease of installation – Ora2Pg
  • Good defaults – Ora2Pg
    • For testing purposes, we changed the following settings in the /etc/ora2pg/ora2pg.conf configuration file that we use as defaults for our migration projects.
SettingValueComments
EXPORT_SCHEMA1By default, Ora2Pg exports all objects into the default Pg namespace. In general, this causes breaking changes in the application code that includes the schema name in SQL code for proper name resolution. We recommend using a value of 1 for generating matching schemas in the PostgreSQL database.
TYPETABLE PACKAGE VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TYPE MVIEW DBLINK SYNONYM TABLESPACE PARTITION DIRECTORYOra2Pg’s documentation advises that some of the types we used should not be loaded directly into the PostgreSQL database because they often require manual editing. Ora2Pg specifically has issues with the following types: GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE, QUERY, and PACKAGE export types especially if you have PLSQL code or Oracle-specific SQL in it. However, if you exclude these types, Ora2Pg excludes them from the conversion.
ORA_RESERVED_WORDSaudit, comment, references, session_userAdded session_user since we see it used very often.
USE_RESERVED_WORDS1All too often we encounter code where developers used keywords for object names. Tools like SQL Developer shield developers from problems by automatically adding quoted delimiters. We recommend turning this flag on with a value of 1.
STOP_ON_ERROR0Ora2Pg defaults to stopping the SQL script generation any time it encounters an error. For small demo projects, the default value of 1 makes sense. However, in large migration projects, this presents a huge problem. We recommend using a value of 0 and then go back and look for the errors in the scripts.
EXPORT_INVALID1By default, Ora2Pg uses the DBMS_UTILITY.COMPILE_SCHEMA procedure in an attempt to see if the original Oracle code was valid. Very few Oracle developers use this feature because it invalidates all the statistics used by the query optimizer. In many cases, we see legacy features with dead code within IF statement blocks that never run, but the application works fine with the live code. We recommend using 1 for this setting and then go back thru the generated code looking for the INVALID comment.
PRESERVE_CASE1By default, Ora2Pg converts all Oracle object names to lower case. This represents a problem where PL/SQL developers used quoted identifiers to create case-sensitive identifiers for applications that required case-sensitive naming. Until Ora2Pg adds code to look for quoted identifiers and preserve their case, we recommend using a value of 1 for migration projects. Our recommendation contradicts the advice from Ora2Pg’s main developer, Giles Darold. But given the 100o’s of Oracle databases we have encountered since DB Best was founded in 2002, we stand by our recommendation.
  • CLI — Ora2Pg
  • CLI — SCT
    • Amazon provides access to the AWS SCT command-line interface upon request from customers or partners. As an APN Advanced Consulting Partner, DB Best used the CLI and compare it with Ora2Pg.
    • SCT uses a secure vault when storing passwords within a project. SCT also provides the least privileges need for connecting to Oracle and PostgreSQL.
    • You need to install the UI version of the application to enable the support of all application features. Leveraging CLI in the AWS SCT allows for reducing memory usage by excluding the UI side of the application. The SCT CLI supports executing commands into your application or an existing DevOps pipeline.

Go back to the summary table

Performing assessments

At the start of any migration project, you may want to get a clear understanding of possible issues or pitfalls. To do so, you need a conversion assessment report. Both tools provide users with this kind of report.

Product areaOra2Pg scoreOra2Pg capabilityAWS SCT scoreAWS SCT capability
Easy to read reports1Ora2Pg conversion report is hard to read and understand. For our test database, this report includes over 8900 lines of plain text. See the example below.3AWS SCT assessment report is well-structured and provides users with accurate and verified information. At the top of the document, you can see a brief executive summary. This section provides a thorough overview of conversion results. See the example below.
Provides an accurate scope of changes1Ora2Pg provides users with optimistic results, based on manifestly incorrect data. For example, the converted PostgreSQL code may include a typical Oracle PL/SQL structure or a poorly blended mix of converted and original Oracle code.3For our test database, we figured out that 87% of the database storage objects and 82% of database code objects can be converted automatically or with minimal changes. Generally speaking, this results in 95% of code lines.
Includes cloud-specific recommendations0Ora2Pg offers no specific conversion actions for either AWS RDS  or Aurora for PostgreSQL in the assessment report.3The AWS SCT assessment report includes specific information for AWS RDS or Aurora for PostgreSQL as shown in the example below.
Computation of migration effort1Ora2Pg does provide estimates on specific issues that it identified as migration blockers. However, Ora2Pg provides no insight as to how it derived these numbers, but they do provide a relative measurement of effort compared to other issues. The values simply can’t be validated.1The AWS SCT assessment report does not provide an estimate of the manual effort required to fully convert the source Oracle database to PostgreSQL. Amazon suggests using the Workloads Qualification Framework to estimate the stuff-hour effort required for manual conversion.
Complexity Estimate1Ora2Pg assigned the highest level of complexity for the conversion of our test Oracle database to PostgreSQL. The tool estimates that the project requires 17710 cost migration units or 211 man-days. Again, the values simply can’t be validated. See the example below.1The AWS SCT report displays diagrams that show the following types of objects: automatically converted, requiring simple actions, requiring medium-complexity actions, and requiring complex actions. The bar chart only provides a sense of effort. See the example below.
Loading Oracle Objects1Ora2Pg loaded only 2071 objects. This means that the Ora2Pg project does not include 109 database objects including tables, indexes, packages, sequences, and jobs. Ora2Pg’s report does not include the object count and requires analyzing the Oracle schema using Oracle tools and reconciling the differences. Ora2Pg unnecessarily loads synonyms into the project scope and tries to convert them and can inflate the scope of the migration report.3AWS SCT creates a comprehensive scope of source database objects as it loads more objects than Ora2Pg. AWS SCT recognized and loaded to the project scope all 2180 objects from the source Oracle database.
Cost of running on AWS0Ora2Pg has no ability to estimate the cost of AWS resources for the migrated solution using PostgreSQL.0AWS SCT has no ability to estimate the cost of AWS resources for the migrated solution using PostgreSQL. We would like to point out that DB Best has our own tools to help estimate the cost of AWS resources for the migrated solution using our Database Migration Platform.
Ease of use totals514

The following image shows a comparison of Ora2Pg and AWS Schema Conversion Tool assessment reports. The Ora2Pg plain text report is on the left side, while the PDF report generated by AWS SCT is on the right side of the image. You can download the original files to compare them by yourself: the Ora2Pg report and the AWS SCT report.

ora2pg vs aws sct assessment reports

Oracle to PostgreSQL Migration Complexity Estimate

Ora2Pg assigned the highest level of complexity for the conversion of our test Oracle database to PostgreSQL. The tool estimates that the project requires 17710 cost migration units or 211 man-days. The AWS SCT report displays diagrams that show the following types of objects: automatically converted, requiring simple actions, requiring medium-complexity actions, and requiring complex actions. Check out the screenshot below.

ora2pg vs aws sct estimates

Go back to the summary table

Database schema conversion

Converting Oracle Database Objects to PostgreSQL

The users can specify the conversion scope in both tools. However, the user interface of AWS SCT provides better options for this.

Ora2Pg runs the conversion process in the command-line and creates several SQL files with converted code. After completing the conversion, you can find a separate file for every type of database object in the specified folder. In the previous versions, Ora2Pg created just one file with all converted code.

AWS SCT stores the converted code within the project. The users may edit this converted code, for example, to address conversion issues. After completing the edits, the user chooses whether to apply the converted code directly to the target database or to save it as a SQL script. Find more details of this process in the Applying Converted Objects to PostgreSQL section.

Addressing Conversion Action Items

All migration automation tools fail to provide 100% accuracy when it comes to converting complex databases from one platform to another. Therefore, all experienced users understand that conversion action items will occur. At this point, the way the migration tool deals with these issues makes the difference.

Ora2Pg adds comments into converted scripts and does not notify users about any of the issues. As a result, the users need to carefully examine all the files with converted code and find the issues.

On the other hand, AWS SCT creates a detailed report with all action items. The users can review this report directly in the application or download it as a PDF file. You can then share this PDF report with your DBA team to understand the conversion complexity and discover possible workarounds. What is even more important, AWS SCT provides suggestions on how to convert all action items manually.

Let us compare the output for different unconverted code fragments. AWS SCT groups these conversion action items by severity. We will use this evaluation strategy and look at the following types of issues: low complexity, medium complexity, and critical.

Low Complexity

Out of the box, PostgreSQL does not support views with the READ ONLY option. You can discover several workarounds for this issue. However, both tools fail to convert this code automatically.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW test_ora_pg.v_account3 (id, accountno) AS
SELECT
a.ID, a.ACCOUNTNO
  FROM ACCOUNT a
WHERE STATEID=0
WITH READ ONLY;
CREATE OR REPLACE VIEW v_account3 (id, accountno) AS SELECT
a.ID, a.ACCOUNTNO
   FROM ACCOUNT a
WHERE STATEID=0;
CREATE OR REPLACE VIEW test_ora_pg.v_account3 (id, accountno) AS
SELECT
    a.id, a.accountno
    FROM test_ora_pg.account AS a
    WHERE stateid = 0
/*
[5075 - Severity LOW - PostgreSQL doesn't support VIEW with the READ ONLY option. Use VIEW without this option instead.]
WITH READ ONLY
*/;

As you can see, AWS SCT provides users with explicit information on the issue.

Medium Complexity

PostgreSQL does not allow for validating XML input values. Therefore, you will need to validate the input manually.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
PROCEDURE prc_xmltype_udfb
AS
    lvDummy varchar2(250) :=  '<tag>??</tag>';
    lnCId INTEGER := 873; -- nls_charset_id('AL32UTF8')
    lbDummy BLOB;
    lxDummy xmltype;
BEGIN
    lbDummy := utl_raw.cast_to_raw(lvDummy);
   
    lxDummy := xmltype(lbDummy, lnCId);
     
END
CREATE OR REPLACE FUNCTION pkg_xml.prc_xmltype_udfb () RETURNS VOID AS $body$
DECLARE
    lvDummy VARCHAR(250) :=  '<tag>??</tag>';
    lnCId NUMERIC := 873; -- nls_charset_id('AL32UTF8')
    lbDummy bytea;
    lxDummy xml;
BEGIN
    lbDummy := utl_raw.cast_to_raw(lvDummy);
    lxDummy := xmltype(lbDummy, lnCId);

END;
CREATE PROCEDURE test_ora_pg.pkg_xml$prc_xmltype_udfb()
AS
$BODY$
DECLARE
    lvDummy CHARACTER VARYING(250) := '<tag>??</tag>';
    lnCId NUMERIC(38) := 873;
    /* nls_charset_id('AL32UTF8') */
    lbDummy BYTEA;
    lxDummy XML;
BEGIN
    lbDummy := ENCODE(lvDummy::BYTEA, 'hex')::BYTEA;
    lxDummy :=
    /*
    [5145 - Severity MEDIUM - PostgreSQL doesn't support validate input values of `XML` type. Perform a manual conversion for the value validation.]
    xmltype(lbDummy, lnCId)
    */
    XMLPARSE (DOCUMENT convert_from(lbDummy, 'utf-8'));
END;
$BODY$
LANGUAGE plpgsql;

As you can see, AWS SCT provides users with a description of this issue, while Ora2Pg leaves the converted code without any comments.

Critical Issue

PostgreSQL does not support ROWID pseudocolumn. Let us look at how Ora2Og and AWS SCT convert Oracle code with ROWID.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE "P_TEST_ROWID_CROSS"
IS
BEGIN
  FOR C IN (SELECT A.ID, A.ROWID AS A_ROWID, B.ROWID AS B_ROWID
    FROM TEST_ORA_CROSS_DB.ACCOUNT A,
      TEST_ORA_CROSS_DB.ACCOUNT B
    WHERE A.ID = B.ID
    AND A.ROWID <> B.ROWID)
  LOOP
    DBMS_OUTPUT.PUT_LINE('id='||c.ID||'  A_ROWID='||c.A_ROWID||' B_ROWID='||c.B_ROWID);
  END LOOP;
END;
CREATE OR REPLACE FUNCTION test_ora_pg2.p_test_rowid_cross () RETURNS VOID AS $body$
DECLARE
  C RECORD;
BEGIN
  FOR C IN (SELECT A.ID, A.ROWID AS A_ROWID, B.ROWID AS B_ROWID
    FROM TEST_ORA_CROSS_DB.ACCOUNT A,
      TEST_ORA_CROSS_DB.ACCOUNT B
    WHERE A.ID = B.ID
      AND A.ROWID <> B.ROWID)
  LOOP
    RAISE NOTICE 'id=%  A_ROWID=% B_ROWID=%', c.ID, c.A_ROWID, c.B_ROWID;
  END LOOP;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.p_test_rowid_cross()
AS
$BODY$
DECLARE
    c RECORD;
BEGIN
  BEGIN
    /*
    [5598 - Severity CRITICAL - PostgreSQL doesn't support a ROWID. Set the option "Create ROWID" ON and convert all related objects., 5598 - Severity CRITICAL - PostgreSQL doesn't support a ROWID. Set the option "Create ROWID" ON and convert all related objects., 5598 - Severity CRITICAL - PostgreSQL doesn't support a ROWID. Set the option "Create ROWID" ON and convert all related objects., 5598 - Severity CRITICAL - PostgreSQL doesn't support a ROWID. Set the option "Create ROWID" ON and convert all related objects., 5578 - Severity CRITICAL - Unable to automatically transform the SELECT statement. Try rewriting the statement.]
    FOR C IN (SELECT A.ID, A.ROWID AS A_ROWID, B.ROWID AS B_ROWID
      FROM TEST_ORA_CROSS_DB.ACCOUNT A,
        TEST_ORA_CROSS_DB.ACCOUNT B
        WHERE A.ID = B.ID
        AND A.ROWID <> B.ROWID)
      LOOP
        DBMS_OUTPUT.PUT_LINE('id='||c.ID||'  A_ROWID='||c.A_ROWID||' B_ROWID='||c.B_ROWID);
      END LOOP
        */
    END;
END;
$BODY$
LANGUAGE plpgsql;

As you can see, AWS SCT comments the whole piece of code with unsupported syntax. In addition to that, AWS SCT provides users with a description of this issue. Ora2Pg leaves ROWID in the converted code.

Applying Converted Objects to PostgreSQL

Ora2Pg saves the converted code into flat files. Then you need to apply SQL scripts from these files to your database manually using Psql, the interactive terminal for working with PostgreSQL. The only way to automate this process and load changes to the target database on the fly is to use the Perl DBD::Pg module.

AWS SCT also allows for creating scripts for selected converted objects. This may be useful if you don’t have access to the target database from the PC, where you run the SCT application. Then you can transfer the scripts to the host connected to your database and apply changes. In addition to that, SCT users may apply converted code to the PostgreSQL database with one click of a mouse button.

It’s hard to call out the best way to apply changes to the database but having different options is certainly a plus for the AWS SCT.

The bad news is that even after applying the code, converted by Ora2Pg, we faced multiple issues. Particularly, these issues were related to the wrong syntax in the converted code, and they lead to failures in runtime. At the same time, AWS SCT converts the same code fragments correctly and applies valid code to the target database.

Depth of Coverage of PL/SQL Features

Oracle provides database developers with a rich set of features. The features list in PostgreSQL is rather more moderate, so, the conversion tool needs to consider quite a few of specific Oracle features. Otherwise, this will fall on the developer’s shoulders, who will need to address these occurred issues manually.

We discovered multiple conversion issues. You can find the full list of those below.

Despite this impressive list of issues, the overall result is not that bad as you may assume. Check out these examples to see how both tools successfully convert complex code structures.

Objects with the Wrong Syntax

We start with the converted objects, which do not compile in PostgreSQL. Of course, you cannot load these code fragments into the database.

User-Defined Type Declared in a Routine

In Oracle, you may declare the user-defined type inside a procedure.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE PROCEDURE TEST_ORA_PG.P_RECORD_TYPE
AS
  TYPE t_bank IS RECORD
    (Id VARCHAR2(10),
   Description VARCHAR2(128),
     Bic VARCHAR2(12)
    );
  l_bank t_bank;
BEGIN
…  
END;
CREATE OR REPLACE FUNCTION test_ora_pg.p_record_type () RETURNS VOID AS $body$
DECLARE
  TYPE t_bank IS RECORD( Id           VARCHAR(10),
       Description  VARCHAR(128),
       Bic          VARCHAR(12)
     );
  l_bank t_bank;
BEGIN
  …
END;
$body$
LANGUAGE PLPGSQL;
CREATE TYPE test_ora_pg.p_record_type$t_bank AS (
Id CHARACTER VARYING(10),
Description CHARACTER VARYING(128),
Bic CHARACTER VARYING(12)
);

CREATE OR REPLACE PROCEDURE test_ora_pg.p_record_type()
AS
$BODY$
DECLARE
    l_bank test_ora_pg.p_record_type$t_bank;
BEGIN

END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxCorrect syntax

Ora2Pg converts this procedure with an issue, and the converted code does not compile in PostgreSQL. In PostgreSQL, you cannot declare the t_bank type inside the procedure. Instead, you need to create a new type with a separate CREATE TYPE statement.

AWS SCT creates TYPE as a stand-alone object and declares variables with this type. This converted code successfully compiles and runs in PostgreSQL.

Partition Range with Several Columns

The source Oracle code includes a multicolumn range-partitioned table.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE "TEST_ORA_PG"."PARTITIONED_BY_RANGE_NUMBER"(

)
(
 PARTITION "P_100" VALUES LESS THAN (100, 200.5),
 PARTITION "P_200" VALUES LESS THAN (200, 300.9),
 PARTITION "P_LAST" VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
CREATE TABLE partitioned_by_range_number (

) PARTITION BY RANGE (id,id2) ;

CREATE TABLE p_100 PARTITION OF partitioned_by_range_number
FOR VALUES FROM (MINVALUE) TO (100) FROM (MINVALUE) TO (200.5);

CREATE TABLE p_200 PARTITION OF partitioned_by_range_number
FOR VALUES FROM (MINVALUE) TO (200) FROM (MINVALUE) TO (300.9);

CREATE TABLE p_last PARTITION OF partitioned_by_range_number
FOR VALUES FROM (MINVALUE) TO (MAXVALUE) FROM (MINVALUE) TO (MAXVALUE);
CREATE TABLE test_ora_pg.partitioned_by_range_number(

)
PARTITION BY RANGE (id, id2);

CREATE TABLE test_ora_pg.partitioned_by_range_number_p_100
  PARTITION OF test_ora_pg.partitioned_by_range_number
  FOR VALUES FROM (MINVALUE, MINVALUE) TO (100, 200.5);

CREATE TABLE test_ora_pg.partitioned_by_range_number_p_200
  PARTITION OF test_ora_pg.partitioned_by_range_number
  FOR VALUES FROM (100, 200.5) TO (200, 300.9);

CREATE TABLE test_ora_pg.partitioned_by_range_number_p_last
  PARTITION OF test_ora_pg.partitioned_by_range_number
  FOR VALUES FROM (200, 300.9) TO (MAXVALUE, MAXVALUE);
Incorrect syntaxCorrect syntax

Ora2Pg uses the wrong syntax to convert this code. The converted code does not work in PostgreSQL, and after you apply it to the target database, the tables will not be created.

AWS SCT creates TYPE as a stand-alone object and declares variables with this type. This converted code successfully compiles and runs in PostgreSQL.

Unsupported Partition Type

PostgreSQL does not support all partition types used in Oracle, for example, partitioning by reference.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE "TEST_ORA_PG"."PARTITIONED_BY_REF_CHILD"(
 "ID" NUMBER(*,0) NOT NULL,
 "PARENT_ID" NUMBER(*,0) NOT NULL
)
PARTITION BY REFERENCE (FK_REF_CHILD_PARENT)(
 PARTITION "SYS_P220",
 PARTITION "SYS_P221"
);
CREATE TABLE partitioned_by_ref_child (
id NUMERIC(38) NOT NULL,
parent_id NUMERIC(38) NOT NULL
) PARTITION BY  () ;
/*Issue 5201: PostgreSQL doesn’t support this partition type*/
CREATE TABLE IF NOT EXISTS test_ora_pg.partitioned_by_ref_child(
id NUMERIC(38,0) NOT NULL,
parent_id NUMERIC(38,0) NOT NULL
);
Incorrect syntaxCorrect syntax

Ora2Pg converts the source Oracle code of a reference-partitioned table with an error. The condition for PARTITION BY is not set, so the code is invalid and the script will not create the table.

AWS SCT creates an action item to warn the user about an issue. The converted code successfully compiles in PostgreSQL. However, the user needs to recreate the original functionality manually.

Functional Index

The syntax of the CREATE INDEX command In PostgreSQL requires writing parentheses around index expressions.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE "TEST_ORA_PG"."TBL_FUNC_IND"(
 "F_INT_1" NUMBER(*,0),
 "F_INT_2" NUMBER(*,0),
 "F_TEXT" VARCHAR2(256 BYTE)
);
CREATE INDEX "TEST_ORA_PG"."IND_NOFUNC_EXPRESSION"
ON "TEST_ORA_PG"."TBL_FUNC_IND" ("F_INT_1"+"F_INT_2");

CREATE INDEX "TEST_ORA_PG"."IND_NOFUNC_EXPRESSION_DESC"
ON "TEST_ORA_PG"."TBL_FUNC_IND" ("F_INT_1"*10 DESC);

CREATE INDEX "TEST_ORA_PG"."IND_NOFUNC_EXPRESSION_MULTIPLE"
ON "TEST_ORA_PG"."TBL_FUNC_IND" ("F_INT_1"+"F_INT_2", "F_INT_1"-"F_INT_2", "F_INT_1");
CREATE TABLE tbl_func_ind (
f_int_1 NUMERIC(38),
f_int_2 NUMERIC(38),
f_text VARCHAR(256)
);

CREATE INDEX ind_nofunc_expression ON tbl_func_ind (f_int_1+f_int_2);

CREATE INDEX ind_nofunc_expression_desc ON tbl_func_ind (f_int_1*10 DESC);

CREATE INDEX ind_nofunc_expression_multiple ON tbl_func_ind (f_int_1+f_int_2, f_int_1-f_int_2, f_int_1);
CREATE TABLE test_ora_pg.tbl_func_ind(f_int_1 NUMERIC(38,0),
f_int_2 NUMERIC(38,0),
f_text CHARACTER VARYING(256)
);
CREATE INDEX ind_nofunc_expression
ON test_ora_pg.tbl_func_ind
USING BTREE ((f_int_1 + f_int_2) ASC);

CREATE INDEX ind_nofunc_expression_desc
ON test_ora_pg.tbl_func_ind
USING BTREE ((f_int_1 * 10) DESC);

CREATE INDEX ind_nofunc_expression_multiple
ON test_ora_pg.tbl_func_ind
USING BTREE ((f_int_1 + f_int_2) ASC, (f_int_1 - f_int_2) ASC, f_int_1 ASC);
Incorrect syntaxCorrect syntax

Ora2Pg omits parentheses and creates an invalid script, which does not compile in PostgreSQL. Also, this script will not create the view in the target database.

AWS SCT converts this code correctly, adding the parentheses (see the highlighted code lines).

Oracle Native Syntax (+) of OUTER JOIN

In Oracle, you can use the (+) operator to specify an outer join operation. PostgreSQL does not support this syntax. In PostgreSQL, you need to rewrite joins with this syntax using LEFT/RIGHT JOIN syntax.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW v_complex_view3 (accountno, stateid, bic) AS
SELECT c.ACCOUNTNO, c.STATEID,
NVL((SELECT st.BIC
     FROM   TEST_ORA_PG.BANK st
       WHERE st.ID(+)='1'
         AND st.ID(+)=cs.ID
),'200') ff
  FROM TEST_ORA_PG.ACCOUNT  c
    LEFT OUTER JOIN TEST_ORA_PG.ACCOUNTSTATE cs
      ON (c.STATEID=cs.ID);
CREATE OR REPLACE VIEW v_complex_view3 (accountno, stateid, bic) AS SELECT  c.ACCOUNTNO, c.STATEID,
COALESCE((SELECT st.BIC
      FROM cs
LEFT OUTER JOIN test_ora_pg.bank st ON (cs.ID = st.ID AND '1' = st.ID) ),'200') ff
  FROM TEST_ORA_PG.ACCOUNT  c
    LEFT OUTER JOIN TEST_ORA_PG.ACCOUNTSTATE cs
      ON (c.STATEID=cs.ID);
CREATE OR REPLACE VIEW test_ora_pg.v_complex_view3 (accountno, stateid, bic) AS
SELECT
    c.accountno, c.stateid, COALESCE(st_ff.bic, '200') AS ff
    FROM test_ora_pg.account AS c
    LEFT OUTER JOIN test_ora_pg.accountstate AS cs
        ON (c.stateid = cs.id)
    LEFT OUTER JOIN (SELECT
        *
        FROM test_ora_pg.bank AS st_ff) AS st_ff
        ON (st_ff.id = cs.id::TEXT AND st_ff.id = '1');
Incorrect syntaxCorrect syntax
Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW v_multi_joins (stateid) AS SELECT a.stateid
FROM test_ora_pg.account a,
     test_ora_pg.accountstate b,
     test_ora_pg.customer c,
     test_ora_pg.customerstate d

 WHERE a.stateid=b.id
   AND a.id=b.id

   AND a.currencyid>0
   AND a.customerid>0

   AND (a.opendate <= c.opendate(+) AND
        a.opendate <= c.closedate(+))
   AND c.typeid=d.id
;
CREATE OR REPLACE VIEW v_multi_joins (stateid) AS SELECT a.stateid
 FROM test_ora_pg.customerstate d, test_ora_pg.accountstate b, test_ora_pg.account a
LEFT OUTER JOIN test_ora_pg.customer c ON (a.opendate <= c.closedate))
, (a
LEFT OUTER JOIN test_ora_pg.customer c ON ((a.opendate <= c.opendate)
WHERE a.stateid=b.id AND a.id=b.id AND a.currencyid>0 AND a.customerid>0 AND c.typeid=d.id;
CREATE OR REPLACE VIEW test_ora_pg.v_multi_joins (stateid) AS
SELECT
    a.stateid
    FROM test_ora_pg.accountstate AS b, test_ora_pg.customerstate AS d, test_ora_pg.account AS a
    LEFT OUTER JOIN test_ora_pg.customer AS c
        ON (a.opendate <= c.opendate AND a.opendate <= c.closedate)
    WHERE a.stateid = b.id AND a.id = b.id AND a.currencyid > 0 AND a.customerid > 0 AND c.typeid = d.id;
Incorrect syntaxCorrect syntax
CREATE OR REPLACE VIEW v_complex_view1 (accountno, stateid, id) AS SELECT
c.ACCOUNTNO,
c.STATEID,
NVL((SELECT  st.ID
     FROM   TEST_ORA_PG.ACCOUNTSTATE st
       WHERE st.ID(+)=cs.ID
),200) ff
  FROM TEST_ORA_PG.ACCOUNT  c
    LEFT OUTER JOIN TEST_ORA_PG.ACCOUNTSTATE cs
      ON (c.STATEID=cs.ID);
CREATE OR REPLACE VIEW v_complex_view1 (accountno, stateid, id) AS SELECT
c.ACCOUNTNO,
c.STATEID,
COALESCE((SELECT  st.ID
      FROM cs
LEFT OUTER JOIN test_ora_pg.accountstate st ON (cs.ID = st.ID) ),200) ff
  FROM TEST_ORA_PG.ACCOUNT  c
    LEFT OUTER JOIN TEST_ORA_PG.ACCOUNTSTATE cs
      ON (c.STATEID=cs.ID);
CREATE OR REPLACE VIEW test_ora_pg.v_complex_view1 (accountno, stateid, id) AS
SELECT
    c.accountno, c.stateid, COALESCE(st_ff.id, 200) AS ff
    FROM test_ora_pg.account AS c
    LEFT OUTER JOIN test_ora_pg.accountstate AS cs
        ON (c.stateid = cs.id)
    LEFT OUTER JOIN (SELECT
        *
        FROM test_ora_pg.accountstate AS st_ff) AS st_ff
        ON (st_ff.id = cs.id);
Incorrect syntaxCorrect syntax

Ora2Pg rewrites queries with Oracle native syntax (+). Because PostgreSQL does not support this syntax, the scripts are invalid. These converted scripts will not create the views in the target database.

AWS SCT uses the correct join operators to convert this code correctly. All these scripts successfully compile and run in PostgreSQL.

ROWNUM Pseudocolumn

In Oracle, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW test_ora_pg.v_rownum_column_comparison_1 AS
SELECT a.id AS a_id, s.name
FROM account a, accountstate s
WHERE a.stateid = s.id
AND rownum <= s.id
AND a.id <> 0;
CREATE OR REPLACE VIEW v_rownum_column_comparison_1 (a_id, name) AS SELECT  a.id AS a_id, s.name
 FROM account a, accountstate s
WHERE a.stateid = s.id
AND a.id <> 0 LIMIT (s.id);
CREATE OR REPLACE VIEW test_ora_pg.v_rownum_column_comparison_1 (a_id, name) AS
SELECT
    a_id, name
    FROM (SELECT
        ROW_NUMBER() OVER (ORDER BY NULL) AS rownum, a.id AS a_id, s.name, s.id AS rownum_condition
        FROM test_ora_pg.account AS a, test_ora_pg.accountstate AS s
        WHERE a.stateid = s.id AND a.id <> 0) AS inner_query
    WHERE rownum <= rownum_condition;
Incorrect syntaxCorrect syntax
Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW test_ora_pg.v_rownum_column_comparison_2 AS
SELECT a.id, s.name AS s_name
FROM account a, accountstate s
WHERE a.stateid = s.id AND s.id > 0
AND rownum <= a.id;
CREATE OR REPLACE VIEW v_rownum_column_comparison_2 (id, s_name) AS SELECT  a.id, s.name AS s_name
 FROM account a, accountstate s
WHERE a.stateid = s.id AND s.id > 0  LIMIT (a.id);
CREATE OR REPLACE VIEW test_ora_pg.v_rownum_column_comparison_2 (id, s_name) AS
SELECT
    id, s_name
    FROM (SELECT
        ROW_NUMBER() OVER (ORDER BY NULL) AS rownum, a.id, s.name AS s_name
        FROM test_ora_pg.account AS a, test_ora_pg.accountstate AS s
        WHERE a.stateid = s.id AND s.id > 0) AS inner_query
    WHERE rownum <= id;
Incorrect syntaxCorrect syntax

Ora2Pg converts ROWNUM pseudocolum to LIMIT(table_column). This syntax is invalid, you need to use LIMIT { number | ALL } instead. This converted script does not compile in PostgreSQL. Also, this script will not create a view in the target database. The users need to find an alternative conversion approach because the converted code does not compile in PostgreSQL.

AWS SCT converts ROWNUM using window function row_number(), and the converted code compiles and runs in PostgreSQL.

Hierarchical Queries

PostgreSQL does not support hierarchical queries. Therefore, you need to rewrite these queries from Oracle using recursive queries in PostgreSQL.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
test_ora_pg.v_connect_by_with_const_1 (c1, c2, c3, c4, c5) AS
SELECT -1, level, stateid, '', description
  FROM account
  CONNECT BY id = prior stateid
  START WITH description = '*ROOT'
;
CREATE OR REPLACE VIEW v_connect_by_with_const_1 (c1, c2, c3, c4, c5) AS WITH RECURSIVE cte AS (
SELECT  -1,1 AS level,stateid,'',description
   FROM account  WHERE description = '*ROOT'
  UNION ALL
SELECT -1,(c.level+1),stateid,'',description
  FROM account  JOIN cte c ON (c.prior stateid  = id)

) SELECT * FROM cte;
CREATE OR REPLACE VIEW test_ora_pg.v_connect_by_with_const_1 (c1, c2, c3, c4, c5) AS
WITH RECURSIVE t (level, stateid, description)
AS (SELECT
    1 AS level, stateid, description
    FROM test_ora_pg.account
    WHERE description = '*ROOT'
UNION ALL
SELECT
    t.level + 1 AS level, t_2.stateid, t_2.description
    FROM test_ora_pg.account AS t_2, t
    WHERE t_2.id = t.stateid)
SELECT
    -1, level, stateid, ''::TEXT, description
    FROM t;
Incorrect syntaxCorrect syntax

Ora2Pg converts hierarchical queries incorrectly. This converted script does not compile in PostgreSQL. Also, this script will not create a view in the target database.

AWS SCT converts this code correctly, and the script successfully compiles and runs in PostgreSQL.

Package Level Cursors

Oracle allows using global variables defined in packages. Oracle’s Steven Feuerstein in his blog post titled Packaged Cursors Equal Global Cursors does a great job describing the subtleties of package cursors.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
cursor private_simple_cursor IS
  SELECT id FROM account;

PROCEDURE use_private_cursor
  IS
    l NUMBER;
  BEGIN
    OPEN private_simple_cursor;
    fetch private_simple_cursor INTO l;
    close private_simple_cursor;
  END;
CREATE OR REPLACE FUNCTION global_cursors.use_private_cursor () RETURNS VOID AS $body$
DECLARE
    l BIGINT;
BEGIN
    OPEN current_setting('global_cursors.private_simple_cursor')::CURSOR;
    fetch current_setting('global_cursors.private_simple_cursor')::INTO CURSOR l;
    close current_setting('global_cursors.private_simple_cursor')::CURSOR;
  END;
$body$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION test_ora_pg.global_cursors$private_simple_cursor$o()
RETURNS VOID
AS
$BODY$
DECLARE test_ora_pg$global_cursors$private_simple_cursor CURSOR WITH HOLD FOR
SELECT
    id
    FROM test_ora_pg.account;
$BODY$
LANGUAGE  SQL;

CREATE OR REPLACE PROCEDURE test_ora_pg.global_cursors$use_private_cursor()
AS
$BODY$
DECLARE
    l DOUBLE PRECISION;
test_ora_pg$global_cursors$private_simple_cursor$FETCH test_ora_pg.global_cursors$private_simple_cursor;
BEGIN
    PERFORM test_ora_pg.global_cursors$private_simple_cursor$o() ;
    EXECUTE 'FETCH test_ora_pg$global_cursors$private_simple_cursor' INTO test_ora_pg$global_cursors$private_simple_cursor$FETCH.id;

    IF FOUND THEN
        l := test_ora_pg$global_cursors$private_simple_cursor$FETCH.id;
    END IF;
    EXECUTE 'CLOSE test_ora_pg$global_cursors$private_simple_cursor';
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxCorrect syntax

Ora2Pg treats all global variables in the same way. However, global cursors must be treated differently. As a result, the Ora2Pg generated code fails to compile. The AWS SCT code uses its Extension Pack Schema to generate the correct code for providing the Oracle functionality in PostgreSQL. See the highlighted code shown for AWS SCT to see the extra code required to handle cursor global variables.

Synonyms

Oracle synonyms cause multiple conversion issues.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
/* Public synonym for function */
CREATE OR REPLACE PUBLIC SYNONYM F_WITH_DEF_ARG_PUBLIC_S FOR TEST_ORA_PG.F_WITH_DEF_ARG_PUBLIC;

/* Synonym usage */
CREATE OR REPLACE PROCEDURE P_TEST_SYNONYM_PUBLIC_FUNC2
IS
var1 VARCHAR2(200);
BEGIN
 var1:= F_WITH_DEF_ARG_PUBLIC_S(3,6,8);

END;
/* View created */
CREATE VIEW public.f_with_def_arg_public_s AS SELECT * FROM test_ora_pg.f_with_def_arg_public; /* ()must be here */

/* Synonym usage */
CREATE OR REPLACE FUNCTION test_ora_pg.p_test_synonym_public_func2 () RETURNS VOID AS $body$
DECLARE var1 VARCHAR(200);
BEGIN
 var1:= f_with_def_arg_public_s(3,6,8);

END;
$body$
LANGUAGE PLPGSQL
;
/* Function created */
CREATE OR REPLACE FUNCTION public_synonyms.f_with_def_arg_public_s(IN a DOUBLE PRECISION DEFAULT 1, IN b DOUBLE PRECISION DEFAULT 2, IN c DOUBLE PRECISION DEFAULT 3)
RETURNS TEXT
AS
$BODY$
DECLARE
    f$result TEXT;
BEGIN
    f$result := test_ora_pg.f_with_def_arg_public(a, b, c);
    RETURN f$result;
END;
$BODY$
LANGUAGE  plpgsql;

/* Synonym usage */
CREATE PROCEDURE test_ora_pg.p_test_synonym_public_func2()
AS
$BODY$
DECLARE
    var1 CHARACTER VARYING(200);
BEGIN
    var1 := f_with_def_arg_public_s(3, 6, 8);

END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxCorrect syntax

 

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
/* Private synonym for function */
CREATE OR REPLACE SYNONYM
TEST_ORA_PG.FUNC_IN
FOR TEST_ORA_PG.F_IN;
/* Synonym usage */
CREATE OR REPLACE FUNCTION test_ora_pg.p_test_synonym_private_func () RETURNS VOID AS $body$
DECLARE
  pVal BIGINT;
BEGIN
pVal := FUNC_IN(10);
END;
$body$
LANGUAGE PLPGSQL
/* Using fully qualified name of an underlying object */
CREATE OR REPLACE PROCEDURE test_ora_pg.p_test_synonym_private_func()
AS
$BODY$
DECLARE
    pVal NUMERIC(10);
BEGIN
    pVal := test_ora_pg.f_in(10);
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntax, the view and the function were not createdCorrect syntax
/* Private synonym for package */
CREATE OR REPLACE SYNONYM TEST_ORA_PG.TEST_SELECT# FOR TEST_ORA_PG.TEST_SELECT;

/* Synonym usage */
CREATE PROCEDURE              
"P_TEST_SYNONYM_PRIVATE_PACK"
IS
BEGIN  TEST_SELECT#.p_SELECT_UNIQUE();
END;
/* View created */
CREATE VIEW "test_ora_pg"."test_select#" AS SELECT * FROM test_ora_pg.test_select;

/* Synonym usage */
CREATE OR REPLACE FUNCTION test_ora_pg.p_test_synonym_private_pack () RETURNS VOID AS $body$
BEGIN
TEST_SELECT#.p_SELECT_UNIQUE();
END;
$body$
LANGUAGE PLPGSQL;
/* Using fully qualified name of an underlying object */

/* Synonym usage */
CREATE OR REPLACE PROCEDURE test_ora_pg.p_test_synonym_private_pack()
AS
$BODY$
BEGIN
    CALL test_ora_pg.test_select$p_select_unique();
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxCorrect syntax

The typical conversion issues include:

  1. Ora2Pg converts public synonyms for functions and procedures as views. These scripts are incorrect due to the missing parentheses required when calling functions. Since all procedures have converted to functions, their calls are the same as for functions and must contain parentheses (as well as parameters if necessary). So, the view declaration does not compile in PostgreSQL.
  2. Even if you create the views in PostgreSQL manually, the converted routines, which call of these synonyms, will cause an error. These code fragments successfully compile in PostgreSQL but fail in runtime. This happens because the calls for the views and functions are different.
  3. Ora2Pg did not convert private synonyms for functions and procedures. As a result, you cannot use them. The converted code successfully compiles and loads into the PostgreSQL database. However, it fails at runtime.
  4. Finally, Ora2Pg converts private synonyms for packages to views. This does not have any sense and the converted code does not compile in PostgreSQL.

AWS SCT creates the corresponding objects for each public synonym. For a synonym on a function, AWS SCT creates a function, and for a synonym of a procedure, SCT creates an appropriate procedure. To convert a private synonym SCT uses the fully qualified name of an underlying object.

Objects with Unsupported Syntax in PostgreSQL

We continue with incorrectly converted objects.

Transaction Management

Oracle supports transaction management for a long time already. PostgreSQL supports transaction management starting from version 11.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE PROCEDURE TEST_ORA_PG.PROC_COMMIT
AS
BEGIN
    UPDATE account acc
       SET acc.stateid = 2
     WHERE acc.accountno = '2625000'
       AND acc.currencyid = 980;
   
    UPDATE account acc
       SET acc.stateid = 1
     WHERE acc.accountno = '2625001'
       AND acc.currencyid = 980;
     
    COMMIT;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.proc_commit () RETURNS VOID AS $body$
BEGIN
    UPDATE account acc
       SET acc.stateid = 2
     WHERE acc.accountno = '2625000'
       AND acc.currencyid = 980;

    UPDATE account acc
       SET acc.stateid = 1
     WHERE acc.accountno = '2625001'
       AND acc.currencyid = 980;

   COMMIT;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE PROCEDURE test_ora_pg.proc_commit()
AS
$BODY$
BEGIN
    UPDATE test_ora_pg.account AS acc
    SET stateid = 2
        WHERE acc.accountno = '2625000' AND acc.currencyid = 980;
    UPDATE test_ora_pg.account AS acc
    SET stateid = 1
        WHERE acc.accountno = '2625001' AND acc.currencyid = 980;
    COMMIT;
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxCorrect syntax

By default, Ora2Pg leaves COMMIT/ROLLBACK commands untouched. PostgreSQL supports transaction management only in procedures. Because Ora2Pg converts all procedures to functions, transaction management in the converted code is not be supported.

AWS SCT converts Oracle procedures to PostgreSQL procedures, and this converted code successfully compiles and runs in PostgreSQL.

Unsupported ANY Data Types

In Oracle, the ANYDATA type contains an instance of a given type, as well as a description of the type. In this sense, the ANYDATA type is self-describing. However, PostgreSQL does not support this data type.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE datatype_any (
anyt ANYTYPE,
anyd ANYDATA,
anyds ANYDATASET
)
CREATE TABLE datatype_any (
anyt ANYTYPE,
anyd ANYDATA,
anyds ANYDATASET
)
CREATE TABLE IF NOT EXISTS test_ora_pg.datatype_any(
    anyt VARCHAR(8000),
    anyd JSONB,
    anyds VARCHAR(8000)
)
Incorrect syntaxNominally correct syntax

Ora2Pg converts ANYTYPE, ANYDATA, and ANYDATASET types “as is”. You cannot apply objects that use these types to the target database. Ora2Pg includes the DATA_TYPE directive which would allow for a simple replacement in the code for these data types. For example:

DATA_TYPE ANYTYPE:VARCHAR(8000),ANYDATA:JSONB,ANYDATASET:VARCHAR(8000)

Because Ora2Pg does not include these as a standard default, we scored the conversion as a 1.

AWS SCT converts ANYDATA type to the native PostgreSQL type JSONB. Also, AWS SCT uses VARCHAR(8000) type for ANYTYPE and ANYDATASET types.

Unfortunately, the substitutions for both products using the VARCHAR(8000)  type may not be large enough to handle large values for ANYTYPE. The ANYTYPE problem occurs when used as part of a data type for a column in a CREATE TABLE statement. AWS SCT does provide users with the appropriate notification message for these scenarios.

Unsupported Built-in Functions

PostgreSQL does not support quite a few specific Oracle functions. This relates, for example, to the NLS_UPPER function, which returns a string with all letters uppercase.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE TEST_ORA_PG. T_TEST_DEFAULT_4
(S1 VARCHAR2(30) DEFAULT
NVL(sys_context('USERENV', 'CLIENT_IDENTIFIER'), USER),
S2 VARCHAR2(10) DEFAULT
NLS_UPPER('none')
NOT NULL ENABLE
   )
CREATE TABLE t_test_default_4 (
s1 VARCHAR(30) DEFAULT
COALESCE(sys_context('USERENV', 'CLIENT_IDENTIFIER'), USER),
s2 VARCHAR(10) NOT NULL DEFAULT
NLS_UPPER('none')
) ;
/*
[5340 - Severity CRITICAL - PostgreSQL doesn't support the STANDARD.NLS_UPPER(VARCHAR2,VARCHAR2) function. Use suitable function or create user defined function.
*/

CREATE TABLE IF NOT EXISTS test_ora_pg.t_test_default_4(
s1 CHARACTER VARYING(30) DEFAULT
COALESCE(aws_oracle_ext.SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'), SESSION_USER),
s2 CHARACTER VARYING(10) NOT NULL
)
Incorrect syntaxNominally correct syntax

Ora2Pg converts these built-in functions as if they already exist in PostgreSQL. This approach produces invalid converted code, and you cannot apply this code to your PostgreSQL database.

AWS SCT also does not convert these built-in functions. However, AWS SCT creates valid code, which you can apply to the target database. In addition to that, AWS SCT generates an action item that thoroughly describes the issue.

Necessary Typecasting Unsupported

Oracle makes implicit type casting in many cases, while PostgreSQL needs explicit type casting. In the following example, the ID column of the test_ora_pg.bank table has a VARCHAR2(10 byte) type. Therefore, you need to cast the numbers in WHERE clause to TEXT.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW v_bank (id, description, bic, source_id) AS
SELECT  ID, DESCRIPTION, BIC, SOURCE_ID  FROM test_ora_pg.bank
WHERE ID IN (52114, 56532);
CREATE OR REPLACE VIEW v_bank (id, description, bic, source_id) AS SELECT  ID, DESCRIPTION, BIC, SOURCE_ID FROM test_ora_pg.bank
WHERE ID IN (52114, 56532)
CREATE OR REPLACE VIEW v_bank (id, description, bic, source_id) AS SELECT  ID, DESCRIPTION, BIC, SOURCE_ID FROM test_ora_pg.bank
WHERE id IN (52114::TEXT, 56532::TEXT);
Incorrect syntaxCorrect syntax

Ora2Pg converts the code, which you cannot compile in PostgreSQL.

AWS SCT adds ::TEXT to the numbers, and creates the code that successfully compiles and runs in PostgreSQL.

UNPIVOT Clause

The Oracle UNPIVOT clause allows you to transpose columns to rows. PostgreSQL does not support the UNPIVOT clause.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW test_ora_pg. v_account_unpivot (id, accountno, currencyid, description, customerid, stateid, accountbalance, blockedamount, respmanagerid, bankid, account_date, quantity) AS
SELECT ID, ACCOUNTNO, CURRENCYID, DESCRIPTION, CUSTOMERID, STATEID, ACCOUNTBALANCE, BLOCKEDAMOUNT, RESPMANAGERID, BANKID, ACCOUNT_DATE, QUANTITY
FROM   ACCOUNT
UNPIVOT INCLUDE NULLS (quantity FOR account_date IN (OPENDATE AS 'OPENDATE', CLOSEDATE AS
'CLOSEDATE'));
CREATE OR REPLACE VIEW v_account_unpivot (id, accountno, currencyid, description, customerid, stateid, accountbalance, blockedamount, respmanagerid, bankid, account_date, quantity) AS SELECT  ID, ACCOUNTNO, CURRENCYID, DESCRIPTION, CUSTOMERID, STATEID, ACCOUNTBALANCE, BLOCKEDAMOUNT, RESPMANAGERID, BANKID, ACCOUNT_DATE, QUANTITY
 FROM   ACCOUNT
UNPIVOT INCLUDE NULLS(quantity FOR account_date IN (OPENDATE AS 'OPENDATE', CLOSEDATE AS
'CLOSEDATE')) alias1;
/* UNPIVOT converted to UNNEST */
CREATE OR REPLACE VIEW test_ora_pg. v_account_unpivot (id, accountno, currencyid, description, customerid, stateid, accountbalance, blockedamount, respmanagerid, bankid, account_date, quantity) AS
SELECT
    id, accountno, currencyid, description, customerid, stateid, accountbalance, blockedamount, respmanagerid, bankid, UNNEST(ARRAY[OPENDATE, CLOSEDATE]) AS ACCOUNT_DATE,
  UNNEST(ARRAY['OPENDATE', 'CLOSEDATE']) AS QUANTITY
    FROM test_ora_pg.account;
Incorrect syntaxCorrect syntax

Ora2Pg converts the UNPIVOT clause as is. This results in the incorrect code, which you cannot compile in PostgreSQL.

AWS SCT uses the UNNEST function to convert Oracle’s UNPIVOT clause. The converted code successfully compiles and runs in PostgreSQL.

Package Level Variables

Oracle allows using global variables defined in packages. Oracle’s Steven Feuerstein in his blog post titled Packaged Cursors Equal Global Cursors does a great job describing the subtleties of package cursors.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE FUNCTION test_ora_pg.f_local_cursor_inner6
RETURN NUMBER
AS
  cursor crMain (z NUMBER DEFAULT 1) IS
    SELECT global_vars.z + 1 num
      FROM dual
      WHERE test_ora_pg.global_vars.z = z
    ;
  lnDummy NUMERIC;
BEGIN
  OPEN crMain;
  fetch crMain INTO lnDummy;
  dbms_output.put_line(lnDummy);
  close crMain;
  RETURN NULL;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.f_local_cursor_inner6 () RETURNS BIGINT AS $body$
DECLARE
  crMain CURSOR(z BIGINT := 1) FOR
    SELECT global_vars.z + 1 num
    WHERE test_ora_pg.global_vars.z = z
;
  lnDummy NUMERIC;
BEGIN
/* BODY CODE*/
END;
$body$
LANGUAGE PLPGSQL
 STABLE;
CREATE OR REPLACE FUNCTION test_ora_pg.f_local_cursor_inner6$crmain(IN z DOUBLE PRECISION DEFAULT 1)
RETURNS refcursor
AS
$BODY$
DECLARE
  RESULT refcursor;
BEGIN
  OPEN RESULT FOR
  SELECT aws_oracle_ext.get_package_variable('TEST_ORA_PG', 'GLOBAL_VARS', 'z')::NUMERIC(38) + 1 AS num
    WHERE aws_oracle_ext.get_package_variable('TEST_ORA_PG', 'GLOBAL_VARS', 'z')::NUMERIC(38) = z;
  RETURN RESULT;
END;
$BODY$
LANGUAGE  plpgsql;
Incorrect syntaxCorrect syntax

Ora2Pg treats all Oracle global variables in the same way. However, Oracle global variables that represent cursors require extra code to PERFORM the cursor operation, execute a FETCH statement into the global variable and CLOSE the cursor. As a result, Ora2Pg produces incorrect code, which fails to compile in PostgreSQL.

In contrast, AWS SCT uses the extension pack to preserve Oracle logic for global variables.

Reserved Words

If you have tables or column names that are reserved words for PostgreSQL, you can enable the directive USE_RESERVED_WORDS. In this case, Ora2Pg will double-quote the name of the object. However, for all other objects, this directive does not apply.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE FUNCTION func_keywords(EXTRACT IN varchar2, analyze IN varchar2) RETURN INT
IS
 initially varchar2(200);
 PRIMARY varchar2(200);
 USER varchar2(200);
BEGIN
  RETURN 1;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.func_keywords (EXTRACT text, analyze text) RETURNS NUMERIC AS $body$
DECLARE
 initially VARCHAR(200);
 PRIMARY VARCHAR(200);
 USER VARCHAR(200);
BEGIN
  RETURN 1;
END;
$body$
LANGUAGE PLPGSQL
 STABLE;
CREATE OR REPLACE FUNCTION test_ora_pg.func_keywords(IN "EXTRACT" TEXT, IN "ANALYZE" TEXT)
RETURNS NUMERIC
AS
$BODY$
DECLARE
    "INITIALLY" CHARACTER VARYING(200);
    "PRIMARY" CHARACTER VARYING(200);
    "USER" CHARACTER VARYING(200);
BEGIN
    RETURN 1;
END;
$BODY$
LANGUAGE  plpgsql;
Incorrect syntaxCorrect syntax

For example, for reserved words as routine parameters, Ora2Pg does not use double-quotes. Therefore, the converted code is invalid, and you cannot apply it to the target database.

AWS SCT creates the converted code, which you can successfully compile and run in PostgreSQL.

Exceptions

PostgreSQL does not support all Oracle exceptions. Let us look at how Ora2Pg and AWS deal with the SYS_INVALID_ROWID exception.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE PROC_EXCEPTION_COMBO
IS
  lNum NUMBER;
BEGIN
  SELECT 1/0
    INTO lNum
    FROM dual;
exception
  WHEN ZERO_DIVIDE THEN dbms_output.put_line('zero');
  WHEN SYS_INVALID_ROWID THEN NULL;
WHEN others THEN dbms_output.put_line('other');
END;
CREATE OR REPLACE FUNCTION test_exception.proc_exception_combo () RETURNS VOID AS $body$
DECLARE
  lNum BIGINT;
BEGIN
  SELECT 1/0
    INTO STRICT lNum
;
exception
  WHEN division_by_zero THEN RAISE NOTICE 'zero';
  WHEN SYS_INVALID_ROWID THEN NULL;
  WHEN others THEN RAISE NOTICE 'other';
END;
$body$
LANGUAGE PLPGSQL
 STABLE;
CREATE OR REPLACE PROCEDURE test_ora_pg.test_exception$proc_exception_combo()
AS
$BODY$
DECLARE
    lNum DOUBLE PRECISION;
BEGIN
  SELECT
 1::NUMERIC / 0::NUMERIC
        INTO STRICT lNum;
    EXCEPTION
        WHEN division_by_zero THEN
   RAISE DEBUG USING MESSAGE = 'zero';
        /*
        [5561 - Severity CRITICAL - PostgreSQL doesn't support the SYS_INVALID_ROWID exception. Review the exception used, and if possible convert it to another condition.]
        when SYS_INVALID_ROWID then null;
        */
 WHEN others THEN
    RAISE DEBUG USING MESSAGE = 'other';
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxNominally correct syntax

Ora2Pg leaves this unsupported exception unchanged. As a result, the converted code does not compile in PostgreSQL.

AWS SCT understands that PostgreSQL does not support the SYS_INVALID_ROWID exception. So, this tool generates an action item and produces valid code. You can apply this code to the target database. However, this cove will not run correctly because the exception is missing on the PostgreSQL side.

Autonomous Transactions

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or rollback those operations, without committing or rolling back the data in the main transaction. PostgreSQL also supports Pragma autonomous_transaction. However, the conversion is all about the details.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE             p_autonomous_transaction(
  username    IN VARCHAR2 DEFAULT USER,
  event_date  IN OUT DATE,
  msg         IN VARCHAR2 DEFAULT NULL,
  result_code OUT INTEGER)
AS

  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  IF event_date IS NULL
    THEN
      event_date := sysdate;
  END IF;

  INSERT INTO autonomous_transaction_log VALUES (seq_autonomous_transaction_log.nextval, NVL(username,USER), event_date, NVL(msg,l_msg));

  IF event_date >= sysdate
    THEN
      COMMIT;
      result_code := 1;  
    ELSE
      ROLLBACK;
      result_code := 0;  
  END IF;      
END;
CREATE OR REPLACE FUNCTION test_ora_pg.p_autonomous_transaction ( event_date INOUT TIMESTAMP, result_code OUT NUMERIC, username text DEFAULT USER, msg text DEFAULT NULL) AS $body$
DECLARE

BEGIN
  v_query := 'SELECT * FROM p_autonomous_transaction_atx ( ' || quote_nullable(username) || ',' || quote_nullable(event_date) || ',' || quote_nullable(msg) || ',' || quote_nullable(result_code) || ' )';

END;
$body$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION test_ora_pg.p_autonomous_transaction ( event_date INOUT TIMESTAMP, result_code OUT NUMERIC, username text DEFAULT USER, msg text DEFAULT NULL) AS $body$
DECLARE
..
BEGIN

 IF event_date >= clock_timestamp()
 THEN
  result_code := 1;
 ELSE
   ROLLBACK;
   result_code := 0;
 END IF;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.p_autonomous_transaction$at(INOUT aws_params JSON, INOUT aws_session_env JSON)
AS
$BODY$
DECLARE

BEGIN

p_autonomous_transaction$at.aws_params := json_build_object('event_date', event_date, 'result_code', result_code);
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE test_ora_pg.p_autonomous_transaction(IN username TEXT DEFAULT SESSION_USER, INOUT event_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, IN msg TEXT DEFAULT NULL, INOUT result_code NUMERIC DEFAULT NULL)
AS
$BODY$
DECLARE
    aws_params JSON := json_build_object('username', username, 'event_date', event_date, 'msg', msg, 'result_code', result_code);
    aws_session_env JSON := aws_oracle_ext.session_env_store();
BEGIN
    SELECT  *
        FROM aws_oracle_ext.autonomous_exec('CALL test_ora_pg.p_autonomous_transaction$at('||quote_nullable(aws_params)||','||quote_nullable(aws_session_env)||')')
        INTO aws_params, aws_session_env;

END
$BODY$
LANGUAGE plpgsql;
Calls a missing function and fails in runtimeCorrect syntax, works well in runtime
Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
PROCEDURE p_autonomous_transaction4
AS
  FUNCTION f_nested
  RETURN VARCHAR2
  AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    COMMIT;
    RETURN NULL;
  END;

  PROCEDURE p_nested
  AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    RESULT VARCHAR2(10);
  BEGIN
    COMMIT;
    RESULT := f_nested;
  END;
BEGIN
  p_nested;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.p_autonomous_transaction4 (()AS
 FUNCTION f_nested ) RETURNS VARCHAR AS $body$
DECLARE
..
BEGIN
  v_query := 'SELECT * FROM p_autonomous_transaction4_atx ( ' || quote_nullable()AS) || ' )';
  SELECT * INTO v_ret FROM dblink(v_conn_str, v_query) AS p (ret VARCHAR);
  RETURN v_ret;

END;
$body$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION test_ora_pg.p_autonomous_transaction4 (()AS
 FUNCTION f_nested ) RETURNS VARCHAR AS $body$
BEGIN
    RETURN NULL;
  END;
  FUNCTION p_nested
  AS
    PRAGMA AUTONOMOUS_TRANSACTION;
    RESULT VARCHAR2(10);
  BEGIN
    COMMIT;
    RESULT := f_nested;
  END;
BEGIN
  p_nested;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.p_nested$1445338806$at(INOUT aws_params JSON, INOUT aws_session_env JSON)
AS
$BODY$
DECLARE
    RESULT CHARACTER VARYING(10);
BEGIN
    COMMIT;
    RESULT := test_ora_pg.f_nested$1445338806();
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE test_ora_pg.p_nested$1445338806()
AS
$BODY$
DECLARE
    aws_params JSON := json_build_object();
    aws_session_env JSON := aws_oracle_ext.session_env_store();
BEGIN
    SELECT
        *
        FROM aws_oracle_ext.autonomous_exec('CALL test_ora_pg.p_nested$1445338806$at('||quote_nullable(aws_params)||','||quote_nullable(aws_session_env)||')')
        INTO aws_params, aws_session_env;
END
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE test_ora_pg.p_autonomous_transaction4()
AS
$BODY$
BEGIN
    CALL test_ora_pg.p_nested$1445338806();
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntaxCorrect syntax

Let us review the typical problems with the conversion of PRAGMA AUTONOMOUS_TRANSACTION by Ora2Pg:

  1. Ora2Pg converts all Oracle functions or procedures using the pragma AUTONOMOUS_TRANSACTION with the oracle_fdw extension that uses dblink. Amazon RDS does not support this extension. AWS does support a similar postgre_fdw extension which is included in the standard PostgreSQL distribution.
  2. Ora2Pg includes the PG_BACKGROUND directive which uses the pg_background extension. However, PostgreSQL on Amazon RDS lacks support for the pg_background extension. The Ora2Pg documentation file makes no mention of the lack of support on AWS.
  3. Ora2Pg creates a wrapper for function, which should take the name of the function and adds an _atx suffix. In the function declaration, Ora2Pg does not add this suffix. As a result, the converted code includes several functions with the same name in the script.
  4. Ora2Pg uses COMMIT/ROLLBACK commands and does not recognize that PostgreSQL lacks support if the commands in stored functions. AWS SCT resolves this problem by using a procedure instead of a function.
  5. Ora2Pg migrated for autonomous transactions incorrectly generates global package variables.
  6. In some routines, Ora2Pg does not convert PRAGMA AUTONOMOUS_TRANSACTION and fails to highlight this problem.
  7. In triggers, Ora2Pg does not convert PRAGMA AUTONOMOUS_TRANSACTION and the syntax remains unchanged.

AWS SCT converts Pragma autonomous_transaction correctly and creates valid code, which you can apply to the PostgreSQL database.

Objects not Compiled in Amazon RDS

For databases that use Oracle’s CREATE DATABASE LINK feature to access objects in another database or with the Oracle Heterogeneous Services, the Foreign Data Wrapper for Oracle (postgre_fdw) open-source extension provides a popular workaround.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE DATABASE LINK "TEST_ORA_PG"."ORCL12EE_PRIVATE_TEST_ORA_PG.168.15.19" CONNECT TO MIN_PRIVS IDENTIFIED BY user_password
    USING
    '(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.26.68)(PORT = 1521))    )    (CONNECT_DATA =      (SID = ORCL12EE)    )  )';
CREATE SERVER
orcl12ee_private_test_ora_pg.168.15.19
FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.26.68)(PORT = 1521))    )    (CONNECT_DATA =      (SID = ORCL12EE)    )  )');
CREATE SERVER
"orcl12ee_private_test_ora_pg.168.15.19"
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host '172.31.26.68',
    port '1521',
    dbname 'ORCL12EE' );
Incorrect syntaxCorrect syntax

Ora2Pg converts the source Oracle code with database links generates code using the oracle_fdw extension. Amazon RDS does not support this extension. AWS does support a similar postgre_fdw extension which is included in the standard PostgreSQL distribution.

Objects Failed at Runtime (Dynamic SQL)

EXECUTE IMMEDIATE

In Oracle, the EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. PostgreSQL also supports the EXECUTE IMMEDIATE statement. However, conversion tools do not always convert this syntax correctly.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE proc_exec_immed14
IS
  pVar  VARCHAR2(10);
BEGIN
  EXECUTE IMMEDIATE 'SELECT
   DECODE(COUNT(*),0,''N'',''Y'')
  FROM TEST_ORA_PG.ACCOUNT'
    INTO pVar;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.proc_exec_immed14 () RETURNS VOID AS $body$
DECLARE
  pVar  VARCHAR(10);
BEGIN
  EXECUTE 'SELECT
 DECODE(COUNT(*),0,''N'',''Y'')
 FROM test_ora_pg.ACCOUNT'
    INTO STRICT pVar;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.proc_exec_immed14()
AS
$BODY$
DECLARE
    pVar CHARACTER VARYING(10);
BEGIN
    EXECUTE 'SELECT
  CASE COUNT(*) WHEN 0 THEN ''N'' ELSE ''Y'' END
  FROM test_ora_pg.account' INTO STRICT pVar;
END;
$BODY$
LANGUAGE plpgsql;
Fails in runtimeCorrect syntax, works well in runtime
Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE proc_exec_immed13
IS

BEGIN
  update_str := 'UPDATE TEST_ORA_PG.BANK'
   || ' SET '
   || ' DESCRIPTION = :1, '
   || ' BIC = ''6548'' '
   || ' WHERE '
   || ' DESCRIPTION = :2 ';
 EXECUTE IMMEDIATE update_str USING
   new_desc, old_desc;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.proc_exec_immed13 () RETURNS VOID AS $body$
DECLARE

BEGIN
  update_str := 'UPDATE test_ora_pg.BANK'
   || ' SET '
   || ' DESCRIPTION = :1, '
   || ' BIC = ''6548'' '
   || ' WHERE '
   || ' DESCRIPTION = :2 ';
 EXECUTE update_str USING
   new_desc, old_desc;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.proc_exec_immed13()
AS
$BODY$
DECLARE

BEGIN
    update_str := CONCAT_WS('', 'UPDATE test_ora_pg.bank', ' SET  ', ' description = $1,  ', ' bic = ''6548'' ', ' WHERE  ', ' description = $2 ');
    EXECUTE update_str USING new_desc, old_desc;
END;
$BODY$
LANGUAGE plpgsql;
Fails in runtimeCorrect syntax, works well in runtime
CREATE PROCEDURE              proc_exec_immed50
 IS

BEGIN
  l_table_name := 'TEST_ORA_PG.ACCOUNT';
  l_sql := 'select ' || l_table_name || '.ID from ' || l_table_name || '
   where  rownum = 1 order by 1 desc' ;
  EXECUTE IMMEDIATE l_sql INTO l_result;
  DBMS_OUTPUT.PUT_LINE(l_result);
END;
CREATE OR REPLACE FUNCTION test_ora_pg.proc_exec_immed50 () RETURNS VOID AS $body$
DECLARE

BEGIN
  l_table_name := 'test_ora_pg.ACCOUNT';
  l_sql := 'select ' || l_table_name || '.ID from ' || l_table_name || '
   where  rownum = 1 order by 1 desc';
  EXECUTE l_sql INTO STRICT l_result;
  RAISE NOTICE '%', l_result;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.proc_exec_immed50()
AS
$BODY$
DECLARE
    …
BEGIN
    l_table_name := 'TEST_ORA_PG.ACCOUNT';
    l_sql := CONCAT_WS('', 'select ', l_table_name, '.ID from ', l_table_name, ' where  rownum = 1 order by 1 desc');
    /*
    [5334 - Severity CRITICAL - Unable to convert statements with dynamic SQL statement. Please perform a manual conversion.]
    EXECUTE IMMEDIATE l_sql into l_result
    */
    RAISE DEBUG USING MESSAGE = COALESCE(l_result::TEXT, '');
END;
$BODY$
LANGUAGE plpgsql;
Fails in runtimeCorrect syntax, runs well in runtime but does not produce the required result

Ora2Pg does not convert the statement in dynamic SQL correctly. The syntax for statements remains unchanged and bind variables remain in Oracle syntax.

AWS SCT tries to convert the statement, and if this attempt fails, the statement remains unchanged. In this case, AWS SCT comments the dynamic statement and displays a critical message to inform a user.

Dynamic Cursors

Let us look at how conversion tools approach Oracle dynamic cursors.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE proc_dynamic_cursor1( v_docid INTEGER DEFAULT 111)
  IS

BEGIN
  SQL_str := ' SELECT ''X'' ' || ' FROM TEST_ORA_PG.ACCOUNT' ||
    ' WHERE id = :id_in';
OPEN cursor_c FOR SQL_str USING v_docid;

END
CREATE OR REPLACE FUNCTION pkg_dynamic_cursor.proc_dynamic_cursor1 ( v_docid NUMERIC DEFAULT 111) RETURNS VOID AS $body$
DECLARE

BEGIN
  SQL_str := ' SELECT ''X'' ' || ' FROM test_ora_pg.ACCOUNT' ||
    ' WHERE id = :id_in';
OPEN cursor_c FOR EXECUTE SQL_str USING v_docid;

END;
CREATE OR REPLACE PROCEDURE test_ora_pg.pkg_dynamic_cursor$proc_dynamic_cursor1(IN v_docid NUMERIC DEFAULT 111)
AS
$BODY$
DECLARE

BEGIN
SELECT NULL::CHARACTER VARYING(1) AS Test_str
        INTO cursor_c$FETCH;
SQL_str := CONCAT_WS('', ' SELECT ''X'' ', ' FROM test_ora_pg.account', ' WHERE id = $1');
OPEN cursor_c FOR
EXECUTE SQL_str USING v_docid;

END;
$BODY$
LANGUAGE plpgsql;
Fails in runtimeCorrect syntax, works well in runtime
Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE proc_dynamic_cursor6
 IS

BEGIN
  SQL_str := 'SELECT NLS_INITCAP(description)'||
    ' FROM test_ora_pg.bank'||
    ' ORDER BY ID';
    OPEN lv_cursor FOR SQL_str|| ' DESC';
    FETCH lv_cursor INTO vPar1;
    CLOSE lv_cursor;
END
CREATE OR REPLACE FUNCTION pkg_dynamic_cursor.proc_dynamic_cursor6 () RETURNS VOID AS $body$
DECLARE

BEGIN
  SQL_str := 'SELECT NLS_INITCAP(description)'||
    ' FROM test_ora_pg.bank'||
    ' ORDER BY ID';
    OPEN lv_cursor FOR EXECUTE SQL_str|| ' DESC';
    FETCH lv_cursor INTO vPar1;
    CLOSE lv_cursor;
END;
$body$
LANGUAGE PLPGSQL
 STABLE;
CREATE OR REPLACE PROCEDURE test_ora_pg.pkg_dynamic_cursor$proc_dynamic_cursor6()
AS
$BODY$
DECLARE

BEGIN
  SELECT
    NULL::CHARACTER VARYING(100) AS vPar1
    INTO lv_cursor$FETCH;
  SQL_str := CONCAT_WS('', 'SELECT NLS_INITCAP(description)', ' FROM test_ora_pg.bank', ' ORDER BY ID');
  /*
  [5340 - Severity CRITICAL - PostgreSQL doesn't support the STANDARD.NLS_INITCAP(VARCHAR2) function. Use suitable function or create user defined function., 5578 - Severity CRITICAL - Unable to automatically transform the SELECT statement. Try rewriting the statement., 5334 - Severity CRITICAL - Unable to convert statements with dynamic SQL statement. Please perform a manual conversion.]
  OPEN lv_cursor FOR SQL_str|| ' DESC'
  */
  FETCH lv_cursor INTO lv_cursor$FETCH.vPar1;
  IF FOUND THEN
    vPar1 := lv_cursor$FETCH.vPar1;
  END IF;
  CLOSE lv_cursor;
END;
$BODY$
LANGUAGE plpgsql;
Fails in runtimeCorrect syntax, works well in runtime but does not produce the required result

Ora2Pg does not convert the statement with dynamic cursors. The syntax for statements remains unchanged and bind variables remain in Oracle syntax.

AWS SCT tries to convert the statement, and if this attempt fails, the statement remains unchanged. In this case, AWS SCT comments the dynamic cursor and displays a critical message to inform a user.

Reserved Words — Part 2

If you have tables or column names that are reserved words for PostgreSQL, you can enable the directive USE_RESERVED_WORDS. In this case, Ora2Pg will double-quote the name of the object.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE test_ora_pg.SESSION_USER(
id NUMBER,
description varchar2(10));

CREATE OR REPLACE PROCEDURE TEST_ORA_PG.P_USE_KEYWORDS_3
AS
  p_desc varchar2(10);
BEGIN
  SELECT description
    INTO p_desc
    FROM SESSION_USER
   WHERE id = 1;
END;
CREATE TABLE "session_user" (
  id BIGINT,
  description VARCHAR(10)
);

CREATE OR REPLACE FUNCTION test_ora_pg.p_use_keywords_3 () RETURNS VOID AS $body$
DECLARE p_desc VARCHAR(10);
BEGIN
  SELECT description
    INTO STRICT p_desc
    FROM SESSION_USER
   WHERE id = 1;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER;
CREATE TABLE test_ora_pg."SESSION_USER"(
  id DOUBLE PRECISION,
  description CHARACTER VARYING(10)
);
       
CREATE OR REPLACE PROCEDURE test_ora_pg.p_use_keywords_3()
AS
$BODY$
DECLARE p_desc CHARACTER VARYING(10);
BEGIN
  SELECT description
    INTO STRICT p_desc
    FROM test_ora_pg."SESSION_USER"
    WHERE id = 1;
END;
$BODY$
LANGUAGE plpgsql;
Fails in activationCorrect syntax, runs well in runtime

Ora2Pg quotes the names of objects but not their usage. Moreover, for all other objects, the USE_RESERVED_WORDS directive does not apply. For example, for reserved words as routine parameters, this directive does not work.

AWS SCT converts the code with reserved words correctly and you can compile and run this code in PostgreSQL.

Objects that Produce a Result Different from Oracle

Transaction Management

In Oracle, you can use COMMIT and ROLLBACK statements both in procedures and functions. However, PostgreSQL supports transaction management only in procedures.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE PROCEDURE TEST_ORA_PG.PROC_COMMIT
AS
BEGIN
  UPDATE account acc
      SET acc.stateid = 2
    WHERE acc.accountno = '2625000'
    AND acc.currencyid = 980;
…    
    COMMIT;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.proc_commit () RETURNS VOID AS $body$
BEGIN
  UPDATE account acc
     SET acc.stateid = 2
   WHERE acc.accountno = '2625000'
     AND acc.currencyid = 980;

  -- COMMIT;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE PROCEDURE test_ora_pg.proc_commit()
AS
$BODY$
BEGIN
  UPDATE test_ora_pg.account AS acc
  SET stateid = 2
    WHERE acc.accountno = '2625000' AND acc.currencyid = 980;

    COMMIT;
END;
$BODY$
LANGUAGE plpgsql;
Produces different resultCorrect syntax

Ora2Pg converts Oracle procedures to functions. By default, Ora2Pg leaves the COMMIT and ROLLBACK statements untouched from the source Oracle code. Ora2Pg does not flag the inclusion of COMMIT and ROLLBACK as a potential issue.

Ora2Pg does provide the COMMENT_COMMIT_ROLLBACK directive to automatically comment out the COMMIT and ROLLBACK statements. It would be better if Ora2Pg would fix the generated code and retire the directive.

In contrast, AWS SCT converts the procedure correctly and does not comment the COMMIT statement. So, this converted code keeps the original logic, and you can compile and run it in PostgreSQL.

Default Partition

Ora2Pg does not create all needed tables for partitions and subpartitions. PostgreSQL v11 allows creating a “default” partition, which can store rows that do not fall into any existing partition’s range or list.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE test_ora_pg.partition_default
(
  id NUMBER NOT NULL,
  fname VARCHAR2(10 CHAR) NOT NULL
)
PARTITION BY LIST(id)
SUBPARTITION BY LIST (fname)
( PARTITION p1 VALUES(1)
  (SUBPARTITION p1_1 VALUES('a')
  ),
  PARTITION p2 VALUES(2)
  (SUBPARTITION SYS_SUBP17501  VALUES (DEFAULT)
  )
);
CREATE TABLE test_ora_pg.partition_default(
    id DOUBLE PRECISION NOT NULL,
    fname CHARACTER VARYING(10) NOT NULL
)
PARTITION BY LIST (id);

CREATE TABLE p1 PARTITION OF partition_default
FOR VALUES IN (1)
PARTITION BY LIST (fname);

CREATE TABLE p1_1 PARTITION OF p1
FOR VALUES IN ('a');

CREATE TABLE p2 PARTITION OF partition_default
FOR VALUES IN (2)
PARTITION BY LIST (fname);
CREATE TABLE test_ora_pg.partition_default
(
    id DOUBLE PRECISION NOT NULL,
    fname VARCHAR(10) NOT NULL
)
PARTITION BY LIST (id);

CREATE TABLE test_ora_pg.partition_default_p1 PARTITION OF test_ora_pg.partition_default FOR VALUES IN (1) PARTITION BY LIST(fname);

CREATE TABLE  test_ora_pg.partition_default_p1_1 PARTITION OF test_ora_pg.partition_default_p1 FOR VALUES IN ('a');

CREATE TABLE test_ora_pg.partition_default_p2 PARTITION OF test_ora_pg.partition_default FOR VALUES IN (2) PARTITION BY LIST(fname);

CREATE TABLE  test_ora_pg.partition_default_SYS_SUBP17501 PARTITION OF test_ora_pg.partition_default_p2 DEFAULT;
One partition will be lostCorrect syntax

In our example, the PARTITION_DEFAULT table has a “default” partition. Therefore, you need to create the appropriate table. However, the converted script by Ora2Pg does not include a table for “default” subpartition. As a result, you will create only one of two partitions in the target PostgreSQL database.

A user reported this problem as a GitHub issue #742 in December of 2018. Gilles Darold closed the issue as probably solved with the new PG_VERSION directive. When we used PG_VERSION 11, the problem still occurs.

AWS SCT converts default partitions correctly and this converted code produces the expected results.

Cursor Attributes

In Oracle, every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. Let us see how the conversion tools address these attributes taking SQL%FOUND and SQL%NOTFOUND cursor attributes as an example.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE proc_exec_immed75
 IS
  sql_stmt  VARCHAR2(200);
BEGIN
  sql_stmt := 'UPDATE EMPLOYEES SET name = upper(name)';
  EXECUTE IMMEDIATE sql_stmt;
  IF SQL%FOUND THEN
    dbms_output.put_line('found');
  END IF;

  IF SQL%NOTFOUND THEN
    dbms_output.put_line('not found');
  END IF;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.proc_exec_immed75 () RETURNS VOID AS $body$
DECLARE
sql_stmt  VARCHAR(200);
BEGIN
  sql_stmt := 'UPDATE EMPLOYEES SET name = upper(name)';
  EXECUTE sql_stmt;
  IF FOUND THEN
    RAISE NOTICE 'found';
  END IF;

  IF NOT FOUND THEN
    RAISE NOTICE 'not found';
  END IF;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.proc_exec_immed75()
AS
$BODY$
DECLARE
  sql_stmt CHARACTER VARYING(200);
  var_integer BIGINT;
BEGIN
  sql_stmt := 'UPDATE test_ora_pg.employees SET name = UPPER(name)';
  EXECUTE sql_stmt;
  GET DIAGNOSTICS var_integer = ROW_COUNT;

  IF var_integer <> 0 THEN
    RAISE DEBUG USING MESSAGE = 'found';
  END IF;
  IF var_integer = 0 THEN
    RAISE DEBUG USING MESSAGE = 'not found';
  END IF;
END;
$BODY$
LANGUAGE plpgsql;
Produces different resultCorrect syntax

Ora2Pg converts SQL%FOUND and SQL%NOTFOUND cursor attributes after the dynamic SQL to FOUND and NOT FOUND respectively. However, in PostgreSQL, the EXECUTE statement changes the output of GET DIAGNOSTICS but does not change FOUND. Therefore, you cannot use FOUND and NOT FOUND in this case. The code converted by Ora2Pg does not produce expected results.

Oddly, a user created issue 483 – Missing Feature – SQL%ROWCOUNT in July 2017. Gilles Darold checked in a fix that looks to have solved the issue. However, in our example, it appears that the problem still exists.

AWS SCT converts SQL%FOUND and SQL%NOTFOUND cursor attributes after the dynamic SQL using GET DIAGNOSTICS. This code successfully compiles and runs in PostgreSQL.

Autonomous Transactions

Let us once again look at how Ora2Pg and AWS SCT address the conversion of AUTONOMOUS_TRANSACTION pragma.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE TRIGGER test_ora_pg.tr_autonomous_transaction
BEFORE INSERT OR UPDATE OR DELETE
ON test_ora_pg.autonomous_transaction_tab
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_transaction VARCHAR2(10);
BEGIN
  l_transaction :=
  CASE
    WHEN INSERTING THEN 'INSERT'
    WHEN UPDATING THEN 'UPDATE'
    WHEN DELETING THEN 'DELETE'
  END;

  IF INSERTING THEN
    INSERT INTO autonomous_transaction_log VALUES (seq_autonomous_transaction_log.nextval, USER, SYSDATE, l_transaction || ' n= ' || :NEW.n);
    COMMIT;
    :NEW.n := '123';
  END IF;

  IF UPDATING THEN
    INSERT INTO autonomous_transaction_log VALUES (seq_autonomous_transaction_log.nextval, USER, SYSDATE, l_transaction || ' n= ' || :NEW.n);
    IF :NEW.n = :OLD.n
      THEN
        COMMIT;
      ELSE
        ROLLBACK;
    END IF;
  END IF;

  IF DELETING THEN
    IF :OLD.n IS NULL THEN
      dbms_output.put_line('n is null');
    ELSE
      RETURN;
    END IF;
  END IF;
END
CREATE OR REPLACE FUNCTION trigger_fct_tr_autonomous_transaction() RETURNS TRIGGER AS $BODY$
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_transaction VARCHAR(10);
BEGIN
…  
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER tr_autonomous_transaction
  BEFORE INSERT OR UPDATE OR DELETE ON autonomous_transaction_tab FOR EACH ROW
  EXECUTE PROCEDURE trigger_fct_tr_autonomous_transaction();
CREATE PROCEDURE test_ora_pg.tr_autonomous_transaction$autonomous_transaction_tab$at(INOUT aws_params JSON, INOUT aws_session_env JSON)
AS
$BODY$
DECLARE

BEGIN

END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_ora_pg.tr_autonomous_transaction$autonomous_transaction_tab()
RETURNS TRIGGER
AS
$BODY$
DECLARE
  …
BEGIN
  SELECT *
    FROM aws_oracle_ext.autonomous_exec('CALL test_ora_pg.tr_autonomous_transaction$autonomous_transaction_tab$at('||quote_nullable(aws_params)||','||quote_nullable(aws_session_env)||')')
    INTO aws_params, aws_session_env;
SELECT t.f$result$array
    INTO f$result$array
  FROM json_to_record(aws_params)
    AS t (f$result$array test_ora_pg.autonomous_transaction_tab[]);
  RETURN f$result$array[1];
END;
$BODY$
LANGUAGE  plpgsql;

CREATE TRIGGER tr_autonomous_transaction
BEFORE INSERT OR UPDATE OR DELETE
ON test_ora_pg.autonomous_transaction_tab
FOR EACH ROW
EXECUTE PROCEDURE test_ora_pg.tr_autonomous_transaction$autonomous_transaction_tab();
Produces different resultCorrect syntax

Ora2Pg converts the trigger from our example as a usual trigger without PRAGMA AUTONOMOUS_TRANSACTION. This code does not produce the required results in runtime.

AWS SCT converts this code correctly, and you can compile and run it in PostgreSQL, keeping the original logic of the source database.

Inconvenience

Parameters with Default Values

Parameters in routines can have default values. In PostgreSQL, all input parameters following a parameter with a default value must have default values as well. This limitation does not apply to Oracle databases. Therefore, in order to meet the requirements of PostgreSQL, you need to change the order of the parameters in our example.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE test_ora_pg.F_IN_DEFAULT_1(
  a1 IN NUMBER, a2 IN NUMBER DEFAULT Trunc(0.5), a3 IN NUMBER, a4 IN VARCHAR2)
IS
BEGIN
  NULL;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.f_in_default_1
  (a1 BIGINT, a3 BIGINT, a4 text, a2 BIGINT DEFAULT Trunc(0.5))
  RETURNS VOID AS $body$
BEGIN
   NULL;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.f_in_default_1(
IN a1 DOUBLE PRECISION, IN a2 DOUBLE PRECISION DEFAULT TRUNC(0.5), IN a3 DOUBLE PRECISION DEFAULT NULL, IN a4 TEXT DEFAULT NULL)
AS
$BODY$
BEGIN
    NULL;
END;
$BODY$
LANGUAGE plpgsql;
Incorrect syntax, manual changes requiredCorrect syntax

In the converted code by Ora2Pg, you need to move parameters with default values to the end manually. Despite the converted code compiles and runs in PostgreSQL, you will still need to reorder parameters in the function calls in your application.

In AWS SCT, the order of parameters remains unchanged. For parameters, which follow a parameter with a default, AWS SCT adds “DEFAULT NULL”. As a result, you do not need to change the order of routines parameters, which reduces the time to improve the code.

Packages

Let us review the different approaches to the conversion of Oracle packages.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE PACKAGE TEST_ORA_PG.PKG_DYNAMIC_CURSOR
 AS

CREATE OR REPLACE PACKAGE BODY TEST_ORA_PG.PKG_DYNAMIC_CURSOR
AS
PROCEDURE proc_dynamic_cursor1( v_docid INTEGER DEFAULT 111)
 IS
CREATE SCHEMA pkg_dynamic_cursor;

CREATE OR REPLACE FUNCTION pkg_dynamic_cursor.proc_dynamic_cursor1 ( v_docid NUMERIC DEFAULT 111) RETURNS VOID AS
CREATE PROCEDURE test_ora_pg.pkg_dynamic_cursor$proc_dynamic_cursor1(IN v_docid NUMERIC DEFAULT 111)
AS
Correct syntax, may cause inconvenienceCorrect syntax

Ora2Pg uses a schema to emulate the package. This code compiles and loads to PostgreSQL but can cause inconvenience in the case of a large number of packages. Also, if you use more than one schema in the source database, this may have unpredictable consequences.

AWS SCT does not use a schema to emulate the package. Instead, AWS SCT creates a procedure. This code successfully compiles and runs in PostgreSQL.

Performance Issues

Constraint issue using NLS_UPPER()

PostgreSQL does not support Oracle’s NLS_UPPER function, which returns a string with all letters uppercase.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE TEST_ORA_PG.T_TEST_CHECK_4
(S0 VARCHAR2(84),
CONSTRAINT "CKC_T_TEST_CHECK_4" CHECK (s0 =
NLS_UPPER(s0))
ENABLE
   )
CREATE TABLE t_test_check_4 (
s0 VARCHAR(84)
) ;

ALTER TABLE t_test_check_4 ADD CONSTRAINT ckc_t_test_check_4 CHECK (s0 =
NLS_UPPER(s0));
/*
[5340 - Severity CRITICAL - PostgreSQL doesn't support the STANDARD.NLS_UPPER(VARCHAR2,VARCHAR2) function. Use suitable function or create user defined function.
*/

CREATE TABLE IF NOT EXISTS test_ora_pg.t_test_check_4(
    s0 CHARACTER VARYING(84)
)
Incorrect syntaxCorrect syntax

Ora2Pg converts the source code to create a table using the unsupported NLS_UPPER() as a check constraint. However, this leads to a PostgreSQL runtime error when trying to evaluate the check constraint on the table.

Although AWS SCT doesn’t solve the problem either, it does provide a detailed message in the generated code to indicate the problem.

Unsupported Features

Procedures

PostgreSQL supports procedures starting from version 11.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE
TEST_ORA_PG.a_substr
CREATE OR REPLACE FUNCTION
test_ora_pg.a_substr () RETURNS VOID
CREATE PROCEDURE
test_ora_pg.a_substr()
Incorrect conversionCorrect syntax

Ora2Pg converts source Oracle procedures to functions. However, some PostgreSQL features can work only in procedures. For example, TRANSACTION MANAGEMENT does not work in functions.

Ora2Pg does include the PG_SUPPORTS_PROCEDURE directive which causes Ora2Pg to generate procedures instead of functions. However, the latest developer code no longer supports the documented directive.

Issue 908 – Oracle procedure getting migrated as Postgre Function documents the problem. We did not get a chance to test this recent development code fix. In this issue, Gilles Darold wrote: to obtain the latest development code, download https://github.com/darold/ora2pg/archive/master.zip then on Windows:

unzip master.zip
cd ora2pg-master/
perl Makefile.PL
dmake
dmake install

AWS SCT converts Oracle procedures to PostgreSQL procedures. All procedural features work correctly in the converted code since SCT can tell what version of PostgreSQL runs on the target destination.

Case Sensitivity

Oracle is case sensitive, while many other databases by default are not. In PostgreSQL, unquoted names are case-insensitive.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE OR REPLACE VIEW
TEST_ORA_PG.VIEW_CASE_SENSITIVE

CREATE OR REPLACE VIEW
TEST_ORA_PG."VIEW_Case_Sensitive"

CREATE OR REPLACE VIEW
TEST_ORA_PG."view_case_sensitive"
CREATE OR REPLACE VIEW
view_case_sensitive

CREATE OR REPLACE VIEW
view_case_sensitive

CREATE OR REPLACE VIEW
view_case_sensitive
CREATE OR REPLACE VIEW
test_ora_pg."VIEW_CASE_SENSITIVE"

CREATE OR REPLACE VIEW
test_ora_pg."VIEW_Case_Sensitive"

CREATE OR REPLACE VIEW
test_ora_pg.view_case_sensitive
Two views will be lostCorrect syntax

Ora2Pg does not support case sensitivity. As a result, Ora2Pg converts three views with case sensitive names to three views with the same name in lower case. As a result, the script includes three views of the same name. When you execute this code, PostgreSQL simply rewrites the views. As a result, you will have just one view of the target PostgreSQL database.

Ora2Pg includes documentation about a directive called PRESERVE_CASE. However, Issue 837 – ora2pg don’t work if PRESERVE_CASE = 1 remains as an open issue since November 2019.

AWS SCT adds quotes to the names of the views. This code successfully compiles and works in PostgreSQL.

User Defined Type

You need to create the User Defined Type (UDT) before using it in the CREATE TABLE statement.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE aaa (
  id BIGINT,
  nt TN
)
CREATE TABLE aaa (
  id BIGINT,
  nt TN
) ;
CREATE DOMAIN test_ora_pg.tn AS DOUBLE PRECISION [];
CREATE TABLE test_ora_pg.aaa(
    id DOUBLE PRECISION,
    nt test_ora_pg.tn
);
Incorrect syntax, the code does not compileCorrect syntax, the code compiles and works correctly
Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE TABLE customers_nested_table (
custid BIGINT,
address ADDRESS_TAB
) ;
CREATE TABLE customers_nested_table (
custid BIGINT,
address ADDRESS_TAB
) ;
CREATE DOMAIN test_ora_pg.address_tab AS test_ora_pg.address_t [];

CREATE TABLE test_ora_pg.customers_nested_table(
 custid DOUBLE PRECISION,
 address test_ora_pg.address_tab
);
Incorrect syntax, the code does not compileCorrect syntax, the code compiles and works correctly

Ora2Pg converts table fields with User Defined Type “as is”. As you can see, in our examples, this UDT is not declared. Because of that, the converted code does not compile in PostgreSQL.

In contrast, AWS SCT creates DOMAIN as a stand-alone object and declares the table field using this type.

Successful Conversion Examples

Complex Grouping Operations

In Oracle, the GROUPING function distinguishes superaggregate rows from regular grouped rows. Let us look at how Ora2Pg and AWS SCT address the following source Oracle code with complex grouping operations.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE  ROC_SEL_GROUPING
IS
BEGIN
  FOR cur IN(SELECT CUSTOMERID,
    STATEID,
    SUM(ACCOUNTBALANCE) AS SUM_BALANCE,
    GROUPING(CUSTOMERID) AS GRP_CUSTOMERID,
    GROUPING(STATEID) AS GRP_STATEID,
    CASE
      WHEN GROUPING(CUSTOMERID) = 1
        THEN 'All Customers'
        ELSE TO_CHAR(CUSTOMERID)
        END AS CUSTOMER                      
      FROM ACCOUNT
      GROUP BY CUBE(CUSTOMERID,STATEID),
        ROLLUP(CUSTOMERID, STATEID)
      ORDER BY CUSTOMERID,GROUPING(STATEID)
      )
    loop
      NULL;
    END loop;
END;
CREATE OR REPLACE FUNCTION test_ora_pg2.proc_sel_grouping () RETURNS VOID AS $body$
DECLARE
  cur RECORD;
BEGIN
  FOR cur IN (SELECT CUSTOMERID,
    STATEID,
    SUM(ACCOUNTBALANCE) AS SUM_BALANCE,
GROUPING(CUSTOMERID) AS GRP_CUSTOMERID,
GROUPING(STATEID) AS GRP_STATEID,
    CASE
      WHEN GROUPING(CUSTOMERID) = 1
        THEN 'All Customers'
        ELSE CUSTOMERID::VARCHAR
      END AS CUSTOMER                      
    FROM ACCOUNT
    GROUP BY CUBE(CUSTOMERID,STATEID),
      ROLLUP(CUSTOMERID, STATEID)
    ORDER BY CUSTOMERID,GROUPING(STATEID)
    )
    loop
      NULL;
    END loop;
END;
$body$
LANGUAGE PLPGSQL;
CREATE OR REPLACE PROCEDURE test_ora_pg.proc_sel_grouping()
AS
$BODY$
DECLARE
  cur RECORD;
BEGIN
  FOR cur IN
  SELECT
    customerid, stateid, SUM(accountbalance) AS sum_balance, GROUPING(customerid) AS grp_customerid, GROUPING(stateid) AS grp_stateid,
    CASE
      WHEN GROUPING(customerid) = 1 THEN 'All Customers'
      ELSE aws_oracle_ext.TO_CHAR(customerid)
    END AS customer
    FROM test_ora_pg.account
    GROUP BY CUBE (customerid, stateid), ROLLUP (customerid, stateid)
    ORDER BY customerid, GROUPING(stateid)
    LOOP
      NULL;
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;
Correct syntaxCorrect syntax

Apparently, both conversion tools produce similar results.

Multiple Implicit Cursors

The following example shows how Ora2pg and AWS SCT convert code with multiple implicit cursors.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE "P_MULTI_CURSOR" (hh NUMBER)
IS
BEGIN
IF hh=1 THEN
  FOR item IN( SELECT
e.NAME, e.ID, e.MANAGERID
FROM EMPLOYEES  e
WHERE NOT (e.NAME = 'PU_CLERK'
AND e.MANAGERID = 30)
ORDER BY e.MANAGERID )   LOOP
      NULL;
    END LOOP;

FOR item IN( SELECT
e.NAME, e.ID, e.MANAGERID
FROM EMPLOYEES  e
WHERE NOT (e.NAME = 'PU_CLERK'
AND e.MANAGERID = 30)
ORDER BY e.MANAGERID )   LOOP
      NULL;
    END LOOP;
ELSE
FOR item IN( SELECT
e.NAME, e.ID, e.MANAGERID
FROM EMPLOYEES  e
WHERE NOT (e.NAME = 'PU_CLERK'
AND e.MANAGERID = 30)
ORDER BY e.MANAGERID )   LOOP
      NULL;
    END LOOP;
END IF;
END;
CREATE OR REPLACE FUNCTION test_ora_pg.p_multi_cursor (hh BIGINT) RETURNS VOID AS $body$
DECLARE
  item RECORD;
BEGIN
IF hh=1 THEN
  FOR item IN ( SELECT
e.NAME, e.ID, e.MANAGERID
FROM employees  e
WHERE NOT(e.NAME = 'PU_CLERK'
AND e.MANAGERID = 30)
ORDER BY e.MANAGERID )   LOOP
      NULL;
    END LOOP;

FOR item IN ( SELECT
e.NAME, e.ID, e.MANAGERID
FROM employees  e
WHERE NOT(e.NAME = 'PU_CLERK'
AND e.MANAGERID = 30)
ORDER BY e.MANAGERID )   LOOP
      NULL;
    END LOOP;
ELSE
FOR item IN ( SELECT
e.NAME, e.ID, e.MANAGERID
FROM employees  e
WHERE NOT(e.NAME = 'PU_CLERK'
AND e.MANAGERID = 30)
ORDER BY e.MANAGERID )   LOOP
      NULL;
    END LOOP;
END IF;
END;
$body$
LANGUAGE PLPGSQL;
CREATE OR REPLACE PROCEDURE test_ora_pg.p_multi_cursor(IN hh DOUBLE PRECISION)
AS
$BODY$
DECLARE
    item RECORD;
BEGIN
  IF hh = 1 THEN
    FOR item IN
    SELECT
      e.name, e.id, e.managerid
      FROM test_ora_pg.employees AS e
      WHERE NOT (e.name = 'PU_CLERK' AND e.managerid = 30)
      ORDER BY e.managerid
    LOOP
      NULL;
    END LOOP;

  FOR item IN
      SELECT
        e.name, e.id, e.managerid
        FROM test_ora_pg.employees AS e
        WHERE NOT (e.name = 'PU_CLERK' AND e.managerid = 30)
        ORDER BY e.managerid
      LOOP
        NULL;
      END LOOP;
    ELSE
      FOR item IN
      SELECT
        e.name, e.id, e.managerid
        FROM test_ora_pg.employees AS e
        WHERE NOT (e.name = 'PU_CLERK' AND e.managerid = 30)
        ORDER BY e.managerid
      LOOP
        NULL;
      END LOOP;
  END IF;
END;
$BODY$
LANGUAGE plpgsql;
Correct syntaxCorrect syntax

Both converted code fragments use correct syntax and work well in PostgreSQL.

Spatial Type

Oracle Spatial consists of a set of object data types, type methods, and operators, functions, and procedures that use these types. Geometry is stored as an object, in a single row, in a column of type SDO_GEOMETRY.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
FUNCTION "F_IN_DATATYPE7_GEOM"( var1 IN SDO_GEOMETRY)
RETURN SDO_GEOMETRY
IS
  l SDO_GEOMETRY;
BEGIN
  SELECT geom INTO l FROM test_geom WHERE geom = var1;
  RETURN l;
END;
CREATE OR REPLACE FUNCTION test_ora_pg2.f_in_datatype7_geom ( var1 geometry) RETURNS geometry AS $body$
DECLARE
  l geometry;
BEGIN
  SELECT geom INTO STRICT l FROM test_geom WHERE geom = var1;
  RETURN l;
END;
$body$
LANGUAGE PLPGSQL
 STABLE;
CREATE OR REPLACE FUNCTION test_ora_pg.f_in_datatype7_geom(IN var1 GEOMETRY)
RETURNS GEOMETRY
AS
$BODY$
DECLARE
  l GEOMETRY;
BEGIN
  SELECT
    geom
    INTO STRICT l
    FROM test_ora_pg.test_geom
    WHERE geom = var1;
  RETURN l;
END;
$BODY$
LANGUAGE  plpgsql;
Correct syntaxCorrect syntax

Both Ora2Pg and AWS SCT correctly convert Oracle Spatial data type to Geometric type in PostgreSQL.

%ROWTYPE Attribute

In Oracle, the %ROWTYPE attribute lets you declare a record that represents a row in a table or view.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE PROCEDURE p_account_rowtype(pid INTEGER)
AS
  a_rec test_ora_pg.account%rowtype;
  a_rec1 test_ora_pg.account%rowtype;
BEGIN
  SELECT *
    INTO a_rec
  FROM test_ora_pg.account a
  WHERE id = pid;
  SELECT *
    INTO a_rec1
  FROM test_ora_pg.account a;
END;
CREATE OR REPLACE FUNCTION test_ora_pg2.p_account_rowtype (pid NUMERIC) RETURNS VOID AS $body$
DECLARE
  a_rec test_ora_pg2.account;
  a_rec1 test_ora_pg2.account;
BEGIN
  SELECT *
    INTO STRICT a_rec
  FROM test_ora_pg2.account a
  WHERE id = pid;
  SELECT *
    INTO STRICT a_rec1
  FROM test_ora_pg2.account a;
END;
$body$
LANGUAGE PLPGSQL
;
CREATE OR REPLACE PROCEDURE test_ora_pg.p_account_rowtype(IN pid NUMERIC)
AS
$BODY$
DECLARE
  a_rec test_ora_pg.account;
  a_rec1 test_ora_pg.account;
BEGIN
  SELECT
    *
    INTO STRICT a_rec
    FROM test_ora_pg.account AS a
    WHERE id = pid;
  SELECT
    *
    INTO STRICT a_rec1
    FROM test_ora_pg.account AS a;
END;
$BODY$
LANGUAGE plpgsql;
Correct syntaxCorrect syntax

AWS SCT and Ora2Pg produce correct converted code for this specific Oracle attribute.

%TYPE Attribute

In Oracle, the %TYPE attribute lets you declare a constant, variable, collection element, record field, or subprogram parameter to be of the same data type as a previously declared variable or column.

Original Oracle codeConverted code by Ora2PgConverted code by AWS SCT
CREATE FUNCTION F_DECL_DATATYPE_TYPE
   RETURN NUMBER
  IS
   var1 ACCOUNT.ID%TYPE;
   var2 var1%TYPE;
  BEGIN
    RETURN 1;
  END;
CREATE OR REPLACE FUNCTION test_ora_pg2.f_decl_datatype_type () RETURNS BIGINT AS $body$
DECLARE
  var1 ACCOUNT.ID%TYPE;
  var2 var1%TYPE;
BEGIN
    RETURN 1;
  END;
$body$
LANGUAGE PLPGSQL
  STABLE;
CREATE OR REPLACE FUNCTION test_ora_pg.f_decl_datatype_type()
RETURNS DOUBLE PRECISION
AS
$BODY$
DECLARE
  var1 TEST_ORA_PG.ACCOUNT.ID%TYPE;
  var2 TEST_ORA_PG.ACCOUNT.ID%TYPE;
BEGIN
  RETURN 1;
END;
$BODY$
LANGUAGE  plpgsql;
Correct syntaxCorrect syntax

This example shows that both AWS SCT and Ora2Pg convert the source Oracle code using the correct syntax.

Go back to the summary table

Data migration

Data migration needs to consider large-scale migrations to the target platform on a cloud-like AWS. We used to spend more time on Data Migration before tools like AWS DMS and SCT with data extractors. The following table shows the results of our evaluation of the two toolsets.

Product areaOra2Pg scoreOra2Pg capabilityAWS SCT scoreAWS SCT capability
Filter data 3Uses the WHERE directive to limit data dumps and transfers.3AWS SCT relies on AWS Database Migration Service (DMS) data extraction agents to filter data. Since SCT us a component of DMS, we can rate this a 3.
Multi-threaded support3Uses the JOBS and ORACLE_COPIES  directives. Note: Assigned this a value of 3 since we already deducted the score for not supporting these features with the Windows OS version.3AWS DMS fully supports multi-threaded operations on all platforms.
Massively parallel data load transfer1Ora2Pg does provide an option to generate XML ktr files for Hitachi Vantara Pentaho Data Integrator (Kettle). However, the free community edition of Kettle lacks the scalability features to support a database migration practice.9AWS DMS and SCT support massively parallel data migration with multiple migration agent VMs on-premise to AWS. This includes the use of AWS Snowball Edge to transfer up to 100 TB of storage to AWS. Amazon offers DMS for free for six months when moving data to Amazon Aurora. DB Best helped demonstrate migrating from Oracle to Amazon Aurora PostgreSQL at Re:Invent 2017.
Ongoing replication1Ora2Pg offers no support for on-going replication. Ora2Pg does provide an option to generate XML ktr files for Hitachi Vantara Pentaho Data Integrator (Kettle). The free community edition of Kettle lacks the scalability features to support a database migration practice. We did not test this capability with Pentaho.3AWS DMS provides for ongoing replication as a standard feature to minimize downtime when going from the staging environment to production.
Data migration totals818

Go back to the summary table

Application Conversion

Based on our experience, vendors often overlook that application conversion typically represents approximately 25% of the overall migration effort. Let’s see how Ora2Pg and AWS SCT compare in this important area.

Product areaOra2Pg scoreOra2Pg capabilityAWS SCT scoreAWS SCT capability
Scan application code for SQL statements0Ora2Pg offers no support. A developer would need to write their own code to search through application code.3The AWS SCT Application Conversion Project supports JAVA, C++, C#, or any code projects. AWS SCT analyzes your application code and extracts the SQL code and displays it in a parsed SQL Scripts pane. It also includes and Application Assessment report for scoping the effort. AWS provides options to choose from to look for parameterized SQL commands based on common coding practices.
Convert SQL statements to use PostgreSQL syntax0Ora2Pg offers no support3The GUI shows the original code and the converted code that can be modified to address any functional issues. In addition, AWS SCT can merge the migrated SQL commands back into the application source code.
Application  totals06

Go back to the summary table

Further Improvements

Wabi Sabi, the ancient Japanese wisdom, states that “Nothing lasts, nothing is finished, and nothing is perfect”. This idea perfectly applies to automation tools for database conversion, mentioned in this epic blog post. We do not mean that we can find beauty in imperfection. Rather, we find these imperfect tools really helpful. Moreover, we strongly believe that these tools have room for improvement. Just because nothing is finished, and the developers can go the extra mile to improve both Ora2Pg and AWS SCT.

To sum the things up, here is a brief summary of what our team figured while running this bake-off. Ora2Pg converts well small Oracle databases with simple code. However, this product can do nothing with enterprise-grade Oracle databases with complex logic. AWS Schema Conversion Tool comes to the rescue here and helps convert huge Oracle databases to PostgreSQL quickly and seamlessly.

Ora2Pg Advantages

Simply put, we figured 3 major Ora2Pg benefits:

  1. Natural command-line interface.
    Ora2Pg runs only in CLI and has fairly simple settings and convenient interface. AWS SCT is all about UI, while the CLI in this product is available only for selected Amazon partners. Using AWS SCT in CLI is hard, and the developers need to simplify it.
  2. Estimates of manual effort required.
    Ora2Pg provides users with clear estimates of man-hours required to complete manual conversion of the source code, which could not be automatically converted. AWS SCT displays only the number of objects that can be and cannot be automatically converted. Assuming the fact that AWS SCT finds more issues with the code conversion, we would definitely like to compare the estimates from Ora2Pg and AWS SCT.
  3. Simple naming for packages.
    Because Ora2Pg converts Oracle packages to schemas in PostgreSQL, the converted code is simple, clear, and easily readable. In opposite, AWS SCT uses a complex naming convention, which results in packagename$functionname names for converted packages. We prefer the Ora2Pg approach for source Oracle databases with a single schema.

AWS SCT Advantages

With that said, we should admit that Ora2Pg has lots of features to improve. Particularly, these include:

  1. Conversion of specific Oracle features.
    With a deep understanding of the amazingly rich Oracle feature set, our team easily figured conversion weaknesses of Ora2Pg. Commonly, AWS SCT converted these code fragments correctly. In addition, Ora2Pg does not consider cloud limitations.
  2. Better visibility.
    For complex enterprise-grade databases, Ora2Pg does not provide users with a perfect understanding of the migration scope. AWS SCT loads all database objects and displays them in the user interface.
  3. Avoiding runtime errors.
    Often the code converted by Ora2Pg has correct syntax and applies to the target database but fails in runtime. The other issue relates to the converted objects that produce a different result in the target database compared to the source Oracle database. Both these cases require thorough testing, otherwise, the converted system will be unstable.
  4. Wrong and unsupported syntax
    Quite often Ora2Pg produces the code, which you cannot apply to the target PostgreSQL database. This article includes multiple examples of code, which fails to compile in PostgreSQL. AWS SCT correctly converts the same code fragments.
  5. Action items.
    Since nothing is perfect, we understand that conversion automation tools do not provide 100% accuracy. However, we appreciate handy AWS SCT notifications and a summary of conversion issues. Ora2Pg does not provide users with an explicit understanding of unconverted code pieces.

Finally, both tools do not provide users with automated tests for the converted database. According to our 12-step database migration methodology, you always need to ensure that converted objects in the target database work exactly the same as the source objects. This is where the DB Best Migration Platform may help.

Take Advantage of our Experience

We strongly believe that this thorough comparison and detailed analysis will help improve both Ora2Pg and AWS SCT. However, if you just think of migrating your Oracle workloads to PostgreSQL, be sure to contact DB Best. Our experienced team can streamline your Oracle to PostgreSQL migration.

Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin