Oracle -> RDS for PostgreSQL 11.5

longraw_support

LONG RAW data type

It is applicable if tables contain one or more LONG RAW columns. In Oracle, the maximum size of LONG RAW data type is 2 GB. In RDS for PostgreSQL 11.5, the size of BYTEA data type is variable.

Table, function, stored procedure, trigger, and user-defined type

IGNORE

Do not convert LONG RAW type. (Default value)

RDS for PostgreSQL 11.5 does not support LONG RAW and it is not converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail.

BYTEA

Convert LONG RAW to BYTEA.

Oracle LONG RAW supports up to 2 GB data; but the maximum size of BYTEA data type may vary in RDS for PostgreSQL 11.5. There is no equivalent for LONG RAW in RDS for PostgreSQL 11.5. If the table column data is beyond the limit, the data change operations (INSERT/UPDATE) fail and an error is thrown.

long_datatype_show_error

LONG data type

It is applicable if the database objects contain one or more LONG columns. In Oracle, the maximum size of LONG data type is 2 GB. In RDS for PostgreSQL 11.5, the maximum size of LONG data type is 1 GB.

Table, function, stored procedure, trigger, and user-defined type

TRUE

Do not convert LONG data type. LONG data type will remain as it is, and you will receive an error message. (Default value)

If Oracle supports 2 GB data for LONG data type. RDS for PostgreSQL 11.5 supports LONG data type, but internally converts it to the TEXT data type. If the data in a table column exceeds 1 GB, data change operations (INSERT/UPDATE) will fail and an error will be thrown.

FALSE

Do not convert LONG data type. LONG data type will remain as it is, and you will not receive an error message.

If the data in a table column exceeds 1 GB, an error is thrown.

float_support

FLOAT data type

It is applicable if tables contain one or more FLOAT columns and FLOAT is specified as FLOAT/FLOAT(n), where n > 53. If n is not provided, the default value 126 is used.

Table, function, stored procedure, trigger, and user-defined type

IGNORE

Do not convert FLOAT data type. (Default value)

RDS for PostgreSQL 11.5 supports the FLOAT data type. In Oracle, the default precision (maximum precision) of FLOAT is 126. In RDS for PostgreSQL 11.5, if the FLOAT precision is greater than 53, an error will occur and the statement execution will fail.

NUMBER

Convert FLOAT/FLOAT(n) to NUMBER.

In RDS for PostgreSQL 11.5, if no precision is specified for NUMBER data type, the maximum number of digits before the decimal point is 131072, and the maximum number of digits after the decimal point is 16383. RDS for PostgreSQL 11.5 internally converts NUMBER to NUMERIC. The stored values are more accurate.

DOUBLE PRECISION

Convert FLOAT/FLOAT(n) data type to DOUBLE PRECISION.

RDS for PostgreSQL 11.5 supports DOUBLE PRECISION, but internally converts it to FLOAT8. FLOAT8 is a 4-byte floating point number.

number_with_zero_scale

NUMBER data type with zero scale

It is applicable if tables contain NUMBER with zero scale or NUMBER without scale.

Table, function, stored procedure, trigger, and user-defined type

IGNORE

Do not convert NUMBER. RDS for PostgreSQL 11.5 supports NUMBER, but it is internally converted to NUMERIC. (Default value)

Do not convert NUMBER. RDS for PostgreSQL 11.5 supports NUMBER. The migration precision is exact.

INT

If the precision of NUMBER is between 1 and 4, it is converted to SMALLINT type.

If the precision of NUMBER is between 5 and 9, it is converted to INT type.

If the precision of NUMBER is between 10 and 18, it is converted to BIGINT type.

If the input script is whole numbers, INTEGER is a better option to improve performance, but its migration precision is not exact.

SDO_GEOMETRY

SDO_GEOMETRY object type

It is applicable if tables contain SDO_GEOMETRY object type.

Table, function, stored procedure, trigger, and user-defined type

IGNORE

Do not convert SDO_GEOMETRY object type. (Default value)

RDS for PostgreSQL 11.5 does not support SDO_GEOMETRY and it is not converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

TEXT

Convert SDO_GEOMETRY object type to TEXT.

There is no equivalent for SDO_GEOMETRY in RDS for PostgreSQL 11.5. The DML statements should be corrected to store the TEXT data and to fetch the data into different object attributes.

MBRCOORDLIST

MDSYS.MBRCOORDLIST object type

It is applicable if table scripts contain MDSYS.MBRCOORDLIST object type.

Table, function, stored procedure, trigger, and user-defined type

IGNORE

Do not convert MBRCOORDLIST object type. (Default value)

RDS for PostgreSQL 11.5 does not support MBRCOORDLIST and it is not converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

TEXT

Convert MBRCOORDLIST to TEXT.

There is no equivalent for MBRCOORDLIST in RDS for PostgreSQL 11.5. The DML statements should be corrected to store the TEXT data and to fetch the data into different object attributes.

GEOMETRY

GEOMETRY object type

It is applicable if the table script contains GEOMETRY object type.

Table, function, stored procedure, trigger, and user-defined type

IGNORE

Do not convert GEOMETRY object type. (Default value)

RDS for PostgreSQL 11.5 does not support GEOMETRY and it is not converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

TEXT

Convert GEOMETRY to TEXT.

There is no equivalent for GEOMETRY in RDS for PostgreSQL 11.5. The DML statements should be corrected to store the TEXT data and to fetch the data into different object attributes.

partition_columns_with_unique_constraint

Unique constraints for table column partition

It is applicable if table scripts contain the partition syntax and primary keys or unique keys.

Table, index

0

Ignore the migration. (Default value)

This configuration ignores the migration. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

1

Comment the partition syntax.

Partitions can provide many benefits to applications by improving performance, manageability, and availability. After the partition syntax is commented, tables will become normal tables (not partitioned tables) and you will miss out the partition benefits specified above.

2

Comment unique constraints (primary keys and unique keys).

Unique constraints help:

• Avoid creating duplicate records in a table
• Create referential integrity constraints (not supported by RDS for PostgreSQL 11.5).
• Accelerate database operations such as searching for records because indexes are created based on unique keys.

After the unique constraints (primary keys and unique keys) are commented, you will miss out the benefits specified above.

3

Add partition keys to the unique constraints (primary keys and unique keys).

After partition columns are added to the unique constraints (primary keys and unique keys), duplicate records may be created in the table.

mig_interval_partition

Interval partition in tables

It is applicable if table scripts contain interval partition syntax.

Table

IGNORE

Do not convert the INTERVAL partition syntax. (Default value)

RDS for PostgreSQL 11.5 does not support the INTERVAL partition syntax. It will not be converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

COMMENT

Comment the INTERVAL partition syntax.

After the INTERVAL partition syntax is commented, you will miss out the partition benefits in performance, manageability and availability.

on_null_support

DEFAULT ON NULL clause

It is applicable if the input table scripts contain DEFAULT ON NULL keyword. A DEFAULT ON NULL clause will configure DEFAULT for a column even when a null value has been explicitly included in the INSERT statement.

Table

IGNORE

Do not convert the DEFAULT ON NULL keyword. (Default value)

RDS for PostgreSQL 11.5 does not support the DEFAULT ON NULL clause. It will not be converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

COMMENT

Comment the ON NULL keyword.

RDS for PostgreSQL 11.5 does not support the DEFAULT ON NULL clause. It will be commented. If NULL is provided explicitly, the DML statements should be changed to store appropriate values.

generated_identity_support

IDENTITY columns

It is applicable if input table scripts contain IDENTITY columns using GENERATED AS IDENTITY.

Table, sequence

IGNORE

Do not convert IDENTITY columns. (Default value)

RDS for PostgreSQL 11.5 does not support the GNERATED AS IDENTITY syntax. It will not be converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

SERIAL

Convert IDENTITY columns to SERIAL columns.

SERIAL data type is four-byte auto-incrementing integer. Its range is 1 to 2,147,483,647. If the upper limit or lower limit is exceeded, an error will be reported and DML statements fail to be executed.

SMALL SERIAL

Convert IDENTITY columns to small SERIAL columns.

Small SERIAL data type is two-byte auto-incrementing integer. Its range is 1 to 32,767. If the upper limit or lower limit is exceeded, an error will be reported and DML statements fail to be executed.

BIG SERIAL

Convert IDENTITY columns to big SERIAL columns.

Big SERIAL data type is eight-byte auto-incrementing integer. Its range is 1 to 9,223,372,036,854,775,807. If the upper limit or lower limit is exceeded, an error will be reported and DML statements fail to be executed.

DEFAULT_SEQ

Create a sequence and use it as the default value.

The maximum and minimum values are 2⁶³-1 and -2⁶³-1. If the upper limit or lower limit is exceeded, an error will be reported and DML statements fail to be executed.

read_only_table

Read-only keyword in tables

It is applicable if the input table scripts contain read only keyword. In Oracle, tables are marked as read only using the ALTER TABLE command. When a table is in read-only mode, operations that attempt to modify table data are not allowed.

Table

IGNORE

Do not convert the READ ONLY keyword

RDS for PostgreSQL 11.5 does not support the READ ONLY keyword. It will not be converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

COMMENT

Comment the READ ONLY keyword in ALTER TABLE statements. (Default value)

RDS for PostgreSQL 11.5 does not support the READ ONLY keyword. It will be commented. The keyword is generally not used in application scripts, but is used in database maintenance. It can be handled by creating a trigger.

TRIGGER

Create a trigger to support the READ ONLY mode.

A trigger will be created to support the READ ONLY mode.

bitmap_index

BITMAP index

It is applicable if CREATE INDEX statements contains BITMAP keyword.

Index

0

No conversion will be happened. BITMAP keyword will be retained as it is. (Default value)

RDS for PostgreSQL 11.5 does not support BITMAP indexes. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

1

Comment BITMAP indexes.

RDS for PostgreSQL 11.5 does not support BITMAP indexes. The indexes will be commented. When indexed columns are used in search criteria, performance problems may occur. Therefore, you should create proper indexes.

2

Create BTREE indexes instead of BITMAP indexes.

BITMAP indexes are typically used for columns with a large number of duplicate values (low cardinality), while BTREE indexes are suitable for high cardinality columns. There is no exact equivalent for BITMAP indexes in RDS for PostgreSQL 11.5. You can create BTREE indexes.

3

Create HASH indexes instead of BITMAP indexes.

There is no exact equivalent for BITMAP indexes in RDS for PostgreSQL 11.5. The HASH index can be considered if an indexed column is used only when = operator is used for comparison.

reverse_support

REVERSE keyword in CREATE INDEX statements

It is applicable if CREATE INDEX statements contain REVERSE keyword.

Index

IGNORE

Do not convert the REVERSE keyword. (Default value)

RDS for PostgreSQL 11.5 does not support REVERSE indexes. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

COMMENT

Comment the REVERSE keyword.

RDS for PostgreSQL 11.5 does not support REVERSE indexes. The REVERSE keyword will be commented. The REVERSE indexes will be converted to common indexes supported by RDS for PostgreSQL 11.5. There is no exact equivalent for REVERSE indexes in RDS for PostgreSQL 11.5. You can use B-tree indexes.

viewEditioning

EDITIONING views

It is applicable if the input view scripts contain EDITIONING keyword. In Oracle, it is allowed to create DML triggers on EDITIONING views.

View

0

Do not convert the EDITIONING keyword. (Default value)

RDS for PostgreSQL 11.5 does not support the EDITIONING keyword. It will not be converted. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it. If a DML trigger is created in views, the trigger should be rewritten in the base table without affecting the business logic.

1

Comment EDITIONING keyword in view statements

RDS for PostgreSQL 11.5 does not support the EDITIONING keyword. It will be commented. If a DML trigger is created in views, the trigger should be rewritten in the base table without affecting the business logic.

seq_max_min_value

Sequence value beyond the limit

It is applicable if the sequence scripts contain MAXVALUE and MINVALUE.

Sequence, table

0

Do not change the value of MINVALUE or MAXVALUE even if its value exceeds the lower or upper limit (9223372036854775808 or 9223372036854775807). (Default value)

Oracle supports MAXVALUE and MINVALUE up to 10²⁷ and -10²⁶ respectively. If the input MINVALUE/MAXVALUE of a sequence goes beyond the limit (-9223372036854775808 or 9223372036854775807), compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

1

If the input MINVALUE/MAXVALUE of a sequence goes beyond the limit (-9223372036854775808 or 9223372036854775807), use the boundary value.

If the input MINVALUE/MAXVALUE of a sequence goes beyond the limit (-9223372036854775808 or 9223372036854775807), the boundary value is used. The generated sequence value does not exceed the upper and lower limits of RDS for PostgreSQL 11.5.

otherthan_gregorian

Calendar types other than GREGORIAN

It is applicable if the input TO_DATE function contains calendar type other than GREGORIAN.

Table

0

If the third parameter is specified in TO_DATE and TO_TIMESTAMP, do not comment GREGORIAN calendar. (Default value)

RDS for PostgreSQL 11.5 supports only GREGORIAN calendar and it supports only TO_DATE and TO_TIMESTAMP. If other than GREGORIAN calendar is specified as a third parameter in Oracle TO_DATE function, GREGORIAN calendar will not be commented, but compiling the migrated scripts on RDS for PostgreSQL 11.5 will fail. You should manually correct it.

1

If the third parameter is specified in TO_DATE and TO_TIMESTAMP, comment GREGORIAN calendar.

If other than GREGORIAN calendar is specified as a third parameter in Oracle TO_DATE function, GREGORIAN calendar will be commented. Compiling the migrated scripts on RDS for PostgreSQL 11.5 will success.

object_name_combiner

object_name_combiner parameter

It is applicable if the CREATE INDEX scripts contain schema names or the CREATE TABLE scripts contain partition syntax.

Function, stored procedure, and trigger

$

Merge:

- Schema names and index names in CREATED INDEX statements

- Table names and partition names in CREATE PARTITIONED TABLE statements

Sample delimiters are: $#_

The name contains up to 4 characters. All special characters are not allowed.

default_schema

Setting default schema names

It is applicable if schema names are not provided in creating database objects.

schema

-

The default schema name is used for a database object whose schema name is not specified.

The default schema name is used. The schema name cannot be empty.

varray_size

PL/SQL tables

It is applicable if PL/SQL tables are used in stored procedures, functions and packages.

Package, function, and stored procedure

1024

Change the size of VARRAY as required.

RDS for PostgreSQL 11.5 does not support table type. Therefore, PL/SQL tables will be converted to VARRAY tables. There will be no performance impact.

package_name_delimiter

Setting package name delimiters

It is applicable if the input scripts contain CREATE PACKAGE statements.

Function, trigger, and stored procedure

$

Set a delimiter to form the name of a sub-object of a package during the Oracle package migration.

Delimiters are used for package conversion. For example, if the dollar sign ($) is used as a delimiter, the name of the specified stored procedure or function in a package will be converted to packageName$procedureName or packageName$functionName.

Sample delimiters are: $#_

A delimiter string can contain a maximum of four characters. DOT(.) and any other special characters cannot be used as the delimiters.

comment_commit

COMMIT/ROLLBACK keyword

It is applicable if procedures or functions contain the COMMIT/ROLLBACK statements.

Stored procedure

FALSE

Do not convert COMMIT/ROLLBACK statements. (Default value)

RDS for PostgreSQL 11.5 does not support COMMIT and ROLLBACK in stored procedures/functions. The stored procedures/functions will fail to be executed. You should manually correct the error.

TRUE

Comment the COMMIT/ROLLBACK statements.

RDS for PostgreSQL 11.5 does not support COMMIT and ROLLBACK in stored procedures/functions. COMMIT and ROLLBACK statements will be commented. You need to correct the PL/SQL code to ensure that the service logic, especially the rollback logic, is not affected.

comment_tablespace

Tablespace

It is applicable if CREATE TABLE and CREATE INDEX statements contain TABLESPACE clauses.

Tablespace, table

FALSE

Do not convert TABLESPACE clauses. (Default value)

RDS for PostgreSQL 11.5 supports the tablespace clauses in CREATE TABLE and CREATE INDEX statements. However, if the specified tablespace does not exist in RDS for PostgreSQL 11.5, the CREATE TABLE and CREATE INDEX statements will fail to be executed. Before compiling the migration scripts on RDS for PostgreSQL 11.5, you need to create the required tablespace.

TRUE

Comment TABLESPACE clauses.

The TABLESPACE clauses are commented. The default tablespace is used when a table or index is created.

pkg_naming

Packages

It is applicable for packages. Based on parameter configuration, you can use packages as schemas or merge package names along with procedure and function names.

Package

pkg_as_schema

Convert packages to schemas.

RDS for PostgreSQL 11.5 does not support packages. Package stored procedures and functions are converted into independent stored procedures and functions. These objects will be created in schemas with the same name as packages. They are not the part of packages, performance may be affected.

merge_pkg_plnames

Merge the package names with package procedure names. (Default value)

RDS for PostgreSQL 11.5 does not support packages. Package stored procedures and functions are converted into independent stored procedures. The package names are merged with package procedure names. Independent stored procedures are not part of packages, performance may be affected.

global_temp_table_support

Global temporary tables

It is applicable if the input table scripts contain GLOBAL TEMPORARY keyword. A global temporary table is a permanent database object. Its table structure will be retained in the database even after the session is disconnected. A local temporary table is scoped to the session in which you created it. Its table is deleted once the session disconnects. RDS for PostgreSQL 11.5 supports only local temporary tables.

Table

0

Do not convert the GLOBAL keyword. (Default value)

A local temporary table will be created by RDS for PostgreSQL 11.5 regardless of whether a global or local table is specified. If no temporary table exists, create it manually.

1

Create a local temporary table.

A global temporary table will be converted to a local temporary table. If no temporary table exists, create it manually.

grant_support

GRANT

Object privileges

Table, view, index, stored procedure, and function

ignore

Do not convert GRANT statements. The scripts will be retained unchanged. (Default value)

In RDS for PostgreSQL 11.5, object privileges are granted separately. This configuration will ignore the migration and scripts will be retained unchanged.

comment

Comment the entire GRANT statements.

In RDS for PostgreSQL 11.5, object privileges are granted separately. This configuration will comment the entire GRANT statements.

sqlplus_unsupported_command

SQL PLUS

RDS for PostgreSQL 11.5 does not fully support the SQL PLUS command. This configuration will handle such type of requirements.

SQL PLUS commands

IGNORE

Ignore the migration. The script will be retained as it is.

RDS for PostgreSQL 11.5 does not support the SQL PLUS command. This configuration will ignore the script.

COMMENT

Comment the SQL PLUS commands. (Default value)

RDS for PostgreSQL 11.5 does not support the SQL PLUS command. This configuration will comment the script.

ERROR

Raise an error.

RDS for PostgreSQL 11.5 does not support the SQL PLUS command. This configuration will raise an error.