Menu
AWS Schema Conversion Tool
User Guide (Version 1.0)

Oracle to PostgreSQL Conversion Reference

Use the following sections to get information on the types of issues you might encounter during an Oracle to PostgreSQL conversion. Choose the link in the Issue column to get more detailed resolution information about the issue if it is available.

Build_in_services

Item Issue Resolution

UTL_SMTP DBMS_JOB

Issue 5590: The function was converted as procedure

Can't return a reply because this functionality is asynchronous.

Built-In SQL Functions

Item Issue Resolution

DateTime function

Issue 5584: The function %s depends on the time zone setting

Review the transformed code, and set time zone manually if necessary.

FUNCTION

Issue 5340: PostgreSQL doesn't support the %s function

Use suitable function or create user defined function.

GREATEST(exp, exp2, ...)

Issue 5271: The Oracle GREATEST function and PostgreSQL GREATEST function might give different results

Review your transformed code and modify it if necessary. Change an argument's type if necessary.

LEAST(exp, exp2, ...)

Issue 5272: The Oracle LEAST function and PostgreSQL LEAST function might give different results

Review your transformed code and modify it if necessary. Change an argument's type if necessary.

TO_CHAR, TO_DATE, TO_NUMBER

Issue 5579: The second parameter of %s function can be processed incorrectly

Check converted code and make manual corrections if it's necessary.

CREATE

Item Issue Resolution

Issue 5099: Unable convert object due to %s not created

Review the %s object.

Issue 5332: The object has references on the object in %s schema

Convert the object in %s schema also.

Encrypted Objects

Issue 5582: Encrypted Objects

Decrypt the object before conversion.

CREATE CONSTRAINT

Item Issue Resolution

status=DISABLED

Issue 5326: PostgreSQL doesn't support constraints with the status DISABLED

Drop the constraint.

CREATE INDEX

Item Issue Resolution

Creating a Domain Index

Issue 5208: PostgreSQL doesn't support domain indexes

Revise your code and try to use simple index.

Creating a Function-Based Index

Issue 5555: PostgreSQL doesn't support functional indexes that aren't single-column

Revise your code and try to use simple index.

Creating an BITMAP Index

Issue 5206: PostgreSQL doesn't support bitmap indexes

Revise your code and try to use simple index.

CREATE TABLE

Item Issue Resolution

BFILE Data Type

Issue 5212: PostgreSQL doesn't have a data type BFILE

Either store a named file with the data and create a routine that gets that file from the file system, or store the data blob inside your database.

CREATING A PARTITIONED TABLE

Issue 5201: PostgreSQL doesn't support all partition types

Perform a manual conversion for the partition types that aren't supported.

CREATING CLUSTERED TABLE

Issue 5199: PostgreSQL doesn't support CLUSTERED TABLE

Try using a table with triggers.

CREATING EXTERNAL TABLES

Issue 5200: PostgreSQL doesn't support EXTERNAL TABLES

Try using a table.

CREATING GLOBAL TEMPORARY TABLE

Issue 5198: PostgreSQL doesn't support GLOBAL TEMPORARY TABLE

Try using a local temporary table.

CREATING OBJECT TABLES

Issue 5196: PostgreSQL doesn't support OBJECT TABLE

Revise your code to avoid OBJECT TABLE.

Index-organized table

Issue 5581: PostgreSQL doesn't support index-organized table

Revise your architecture with a custom solution for the table type.

NESTED TABLE

Issue 5348: PostgreSQL doesn't support NESTED TABLES

Revise your code to avoid NESTED TABLE.

ROWID data type

Issue 5550: PostgreSQL doesn't have a data type ROWID

Review your transformed code and modify it if necessary.

TABLE WITH VIRTUAL COLUMNS

Issue 5554: PostgreSQL doesn't support virtual columns

They were emulated by a trigger.

TIMESTAMP(n>6) Data Type

Issue 5213: PostgreSQL expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) of precision

Review your transformed code and modify it if necessary to avoid a loss of accuracy.

TIMESTAMP(n>6) WITH TIME ZONE data type

Issue 5552: PostgreSQL expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) of precision

Review your transformed code and modify it if necessary to avoid a loss of accuracy.

TIMESTAMP(n>6) WITH LOCAL TIME ZONE data type

Issue 5553: PostgreSQL expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) of precision

Review your transformed code and modify it if necessary to avoid a loss of accuracy.

UROWID data type

Issue 5551: PostgreSQL doesn't have a data type UROWID

Review your transformed code and modify it if necessary.

CREATE TYPE

Item Issue Resolution

OBJECT TYPE

Issue 5572: PostgreSQL doesn't support object type methods

Revise your architecture with a custom solution for the user type.

CURSOR

Item Issue Resolution

Cursor attributes

Issue 5084: PostgreSQL doesn't support the cursor attribute %ISOPEN

Revise how you are using this attribute in your code.

TYPE .. IS REF CURSOR

Issue 5226: PostgreSQL doesn't support TYPE ... IS REF CURSOR

Change the function to a stored procedure, and try using a table to store results.

Datetime Expressions

Item Issue Resolution

at local at time zone

Issue 5594: PostgreSQL doesn't support %s datetime expression. The loss of precision and/or accuracy of data is possible

Check, if the data violates these restrictions. If so, perform a manual migration.

DELETE

Item Issue Resolution

ERROR LOG

Issue 5067: PostgreSQL doesn't support the DELETE statement with the ERROR LOG option

You can add error records by inserting them into the log in the exception block. Iterate through the errors in the exception block, add them to the log, and use the EXIT command when finished.

PARTITION

Issue 5098: PostgreSQL doesn't support the DELETE statement for a PARTITION

Either insert data into the overlying partition, or perform a manual conversion using the DELETE statement.

QUERY

Issue 5068: PostgreSQL doesn't support the DELETE statement for a subquery

Perform this operation on the underlying tables instead.

DYNAMIC SQL

Item Issue Resolution

BULK COLLECT

Issue 5088: PostgreSQL doesn't support the EXECUTE IMMEDIATE statement with BULK COLLECT

Perform a manual conversion.

EXECUTE IMMEDIATE

Issue 5334: PostgreSQL doesn't support the dynamic SQL statement

Review and modify the execution string.

RETURNING BULK COLLECT INTO

Issue 5087: PostgreSQL doesn't support the RETURNING BULK COLLECT INTO clause

Perform a manual conversion.

EXCEPTION

Item Issue Resolution

ACCESS_INTO_NULL

Issue 5561: PostgreSQL doesn't support the ACCESS_INTO_NULL exception

Review the exception used, and if possible convert it to another condition.

COLLECTION_IS_NULL

Issue 5562: PostgreSQL doesn't support the COLLECTION_IS_NULL exception

Review the exception used, and if possible convert it to another condition.

EMPTY_BLOCK

Issue 5580: The exception block was emptied after conversion

Check converted code and make manual corrections if it is necessary.

EXCEPTION

Issue 5570: PostgreSQL doesn't support exception name declaration

Review the exception used, and if possible convert it to another condition.

NO_DATA_NEEDED

Issue 5563: PostgreSQL doesn't support the NO_DATA_NEEDED exception

Review the exception used, and if possible convert it to another condition.

PROGRAM_ERROR

Issue 5560: PostgreSQL doesn't support the PROGRAM_ERROR exception

Review the exception used, and if possible convert it to another condition.

ROWTYPE_MISMATCH

Issue 5564: PostgreSQL doesn't support the ROWTYPE_MISMATCH exception

Review the exception used, and if possible convert it to another condition.

SELF_IS_NULL

Issue 5565: PostgreSQL doesn't support the SELF_IS_NULL exception

Review the exception used, and if possible convert it to another condition.

SUBSCRIPT_BEYOND_COUNT

Issue 5566: PostgreSQL doesn't support the SUBSCRIPT_BEYOND_COUNT exception

Review the exception used, and if possible convert it to another condition.

SUBSCRIPT_OUTSIDE_LIMIT

Issue 5567: PostgreSQL doesn't support the SUBSCRIPT_OUTSIDE_LIMIT exception

Review the exception used, and if possible convert it to another condition.

SYS_INVALID_ROWID

Issue 5568: PostgreSQL doesn't support the SYS_INVALID_ROWID exception

Review the exception used, and if possible convert it to another condition.

EXPLICIT CURSORS

Item Issue Resolution

RETURN

Issue 5559: PostgreSQL doesn't support the RETURN TYPE for the cursor

Use cursor declaration without this statement.

FUNCTION

Item Issue Resolution

FUNCTIONS WITH DML (START TRANSACTION, COMMIT, or ROLLBACK.)

Issue 5350: The function can't use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION, COMMIT, or ROLLBACK

Revise you code to implement a transaction control at application side.

INSERT

Item Issue Resolution

ERROR LOG

Issue 5070: PostgreSQL doesn't support the INSERT statement with the ERROR LOG option

You can add error records by inserting them into the log in the exception block. Iterate through the errors in the exception block, add them to the log, and use the EXIT command when finished.

INSERT with partition_extension_clause

Issue 5024: PostgreSQL doesn't support the INSERT statement with partition_extension_clause

Perform a manual conversion for the partition types that aren't supported.

QUERY

Issue 5071: PostgreSQL doesn't support the INSERT statement for a subquery

Perform this operation on the underlying tables instead.

SUBPARTITION

Issue 5090: PostgreSQL doesn't support the INSERT statement for a SUBPARTITION

Either insert data into the overlying partition, or perform a manual conversion using the INSERT statement.

MATERIALIZED VIEW

Item Issue Resolution

CREATE

Issue 5094: PostgreSQL doesn't support a materialized VIEW

Revise your code to replace materialized VIEW with another solution.

USING MATERIALIZED VIEW

Issue 5095: PostgreSQL doesn't support using a materialized VIEW

Revise your code to replace materialized VIEW with another solution.

MERGE

Item Issue Resolution

MERGE

Issue 5102: PostgreSQL doesn't support the MERGE statement

To achieve the effect of a MERGE statement, use separate INSERT, DELETE, and UPDATE statements.

Optimizer Hints

Item Issue Resolution

HINT

Issue 5103: PostgreSQL doesn't support the %s hint

Use PostgreSQL methods for performance tuning.

PACKAGES

Item Issue Resolution

Package global cursor

Issue 5330: PostgreSQL doesn't support global cursors

Use another method for this functionality.

Package global user EXCEPTION

Issue 5331: PostgreSQL doesn't support a global user exception

Use another method for this functionality.

Package user type declaration

Issue 5569: PostgreSQL only supports session variables using SQL standard date and time types

Revise your architecture with a custom solution for the user type.

Parser Error

Item Issue Resolution

Parser Error

Issue 5293: Unable to resolve the object

Verify if object %s is present in the database. If it isn't, check the object name or add the object. If the object is present, transform the code manually.

PRAGMA

Item Issue Resolution

PRAGMA

Issue 5571: PostgreSQL doesn't support PRAGMA options

Review the exception used, and if possible convert it to another condition.

SELECT

Item Issue Resolution

BULK COLLECT INTO

Issue 5140: PostgreSQL doesn't support BULK COLLECT INTO

You can try to include all of the fields from your table in an INTO clause.

FOR UPDATE SKIP LOCKED

Issue 5139: PostgreSQL doesn't support FOR UPDATE SKIP LOCKED

Try using FOR UPDATE without SKIP LOCKED.

FOR UPDATE WAIT

Issue 5144: PostgreSQL doesn't support FOR UPDATE WAIT clauses

Try using FOR UPDATE without WAIT.

Hierarchical queries

Issue 5586: Automatic conversion for queries with NOCYCLE clause not supported

Perform a manual conversion.

MODEL

Issue 5126: PostgreSQL doesn't support the MODEL statement

Revise your code and try using user procedures and temporary tables.

OUTER JOIN

Issue 5585: Automatic conversion of an outer join into a correlation query is not supported

Perform a manual conversion.

ROLLUP

Issue 5557: PostgreSQL doesn't support GROUPING SETS, CUBE, and ROLLUP functions

It can be emulated using CTE expressions. GROUPING SETS, CUBE, and ROLLUP ara available in PostgreSQL database v.9.5 or higher.

Unsupported statement

Issue 5578: Unable to automatically transform the SELECT statement

Try rewriting the statement.

SEQUENCE

Item Issue Resolution

STATUS=INVALID

Issue 5574: PostgreSQL doesn't support the sequence's status

Check the original sequence for errors, and perform the conversion again.

Statements

Item Issue Resolution

GOTO

Issue 5335: PostgreSQL doesn't support the GOTO operator

Perform a manual conversion.

Synonym

Item Issue Resolution

private synonym

Issue 5333: This object uses a private synonym

Check that a referenced object created after synonym conversion exists.

public synonym

Issue 5592: This object uses a public synonym which references a table, a view, or a function

Check that a referenced object created after synonym conversion exists.

public synonym

Issue 5593: This object uses a public synonym which doesn't reference any other objects

Check that a referenced object created after synonym conversion exists.

System Package

Item Issue Resolution

APEX_UTIL package

Issue 5503: MySQL does not have functionality similar to %s module

Try using the Amazon Simple Workflow (Amazon SWF).

DBMS_DATAPUMP

Issue 5502: MySQL does not have functionality similar to %s module

Try using the AWS Import/Export Snowball.

DBMS_JOB module

Issue 5501: MySQL does not have functionality similar to %s module

Try using the AWS Lambda Service with Scheduled Events.

UTL_MAIL module

Issue 5500: MySQL does not have functionality similar to %s module

Try using the Amazon Simple Notification Service (Amazon SNS).

TRIGGERS

Item Issue Resolution

action-type=CALL or PL/SQL

Issue 5313: PostgreSQL doesn't support the action-type clause

Use another method for this functionality.

base-object-type=DATABASE

Issue 5415: PostgreSQL doesn't support system triggers

Revise your code to replace system triggers with another solution.

base-object-type=SCHEMA

Issue 5311: PostgreSQL doesn't support system triggers

Revise your code to replace system triggers with another solution.

COMPOUND TRIGGER

Issue 5242: PostgreSQL doesn't support COMPOUND TRIGGER

Create a single trigger for each part of the compound trigger.

FOLLOWS | PRECEDES

Issue 5241: PostgreSQL doesn't support the FOLLOWS | PRECEDES clause

Try using the FOR EACH ROW trigger.

ON NESTED TABLE

Issue 5240: PostgreSQL doesn't support triggers on nested table columns in views

Revise your code to replace nested table with another solution.

REFERENCING NEW AS .. OLD AS ..

Issue 5238: PostgreSQL doesn't support the REFERENCING clauses

Modify references to pseudo-rows to use OLD and NEW instead.

referencing-names=PARENT

Issue 5317: PostgreSQL doesn't support the PARENT referencing clause

Use another method for this functionality.

status=invalid

Issue 5306: Transformation from invalid trigger

Check the original trigger for errors, and perform the conversion again.

UPDATING(column_name)

Issue 5556: PostgreSQL doesn't support conditional predicates

Revise your code and try using simple triggers.

TRUNCATE TABLE

Item Issue Resolution

DROP STORAGE

Issue 5298: PostgreSQL doesn't support the DROP STORAGE clause in the TRUNCATE statement

Use TRUNCATE without this option.

PRESERVE MATERIALIZED VIEW LOG

Issue 5300: PostgreSQL doesn't support the PRESERVE clause in the TRUNCATE statement

Use TRUNCATE without this option.

PURGE MATERIALIZED VIEW LOG

Issue 5301: PostgreSQL doesn't support the PURGE clause in the TRUNCATE statement

Use TRUNCATE without this option.

REUSE STORAGE

Issue 5299: PostgreSQL doesn't support the REUSE STORAGE clause in the TRUNCATE statement

Use TRUNCATE without this option.

UDT

Item Issue Resolution

STATUS=INVALID

Issue 5577: PostgreSQL doesn't support a UDT with body

Perform a manual conversion.

UDT collection-type arguments

Item Issue Resolution

:= collection_type(...)

Issue 5120: PostgreSQL doesn't support constructors of the "collection" type

Use the "array" type.

EXTEND(n[,i])

Issue 5587: PostgreSQL doesn't support EXTEND method with parameters

Perform a manual conversion.

FORALL

Issue 5121: PostgreSQL doesn't support the FORALL statement

Use a FOR LOOP statement.

TYPE .. IS TABLE OF...

Issue 5118: PostgreSQL doesn't support table type variables

Use the "array" type.

Universal

Item Issue Resolution

Universal action item

Issue 5339: The %s object is invalid

To correct this, update the original source code to make the specified object valid.

Universal action item

Issue 5591: The %s synonym is system

Perform a manual conversion.

Unknown

Item Issue Resolution

Issue 5025: This syntactic element conversion is not supported yet

Perform a manual conversion.

Unknown object

Issue 5351: Automatic conversion of %s object is not supported

Perform a manual conversion.

Unparsed SQL

Issue 5576: Unparsed SQL

Perform a manual conversion.

UPDATE

Item Issue Resolution

ERROR LOG

Issue 5064: PostgreSQL doesn't support the UPDATE statement with the ERROR LOG option

You can add error records by inserting them into the log in the exception block. Iterate through the errors in the exception block, add them to the log, and use the EXIT command when finished.

PARTITION

Issue 5558: PostgreSQL doesn't support the UPDATE statement for a PARTITION

Either insert data into the overlying partition, or perform a manual transformation using the UPDATE statement.

QUERY

Issue 5065: PostgreSQL doesn't support the UPDATE statement for a subquery

Perform this operation on the underlying tables instead.

VIEW

Item Issue Resolution

CAST (MULTISET(

Issue 5245: PostgreSQL doesn't support views with nested table columns

Revise your code to replace nested table with another solution.

constraint

Issue 5583: PostgreSQL doesn't support constraints for view

Perform a manual conversion.

OID_TEXT IS NOT NULL

Issue 5321: PostgreSQL doesn't support the object view

Perform a manual conversion.

PIVOT clause

Issue 5077: PostgreSQL doesn't support the PIVOT clause for the SELECT statement

You can use a stored procedure to prepare data, and then return the data with a VIEW.

STATUS=INVALID

Issue 5320: PostgreSQL doesn't support the view with invalid status

Perform a manual conversion.

VIEW_TYPE_OWNER IS NOT NULL

Issue 5322: PostgreSQL doesn't support the typed view

Perform a manual conversion.

WITH READ ONLY

Issue 5075: PostgreSQL doesn't support VIEW with the READ ONLY option

Use VIEW without this option instead.