Chapter 1. ora2pgpro

Comma-separated list of objects to allow from export. Can be used with SHOW_COLUMN too.

Set the default output directory where files resulting from exports will be stored.

Set an alternative configuration file other than the default /etc/ora2pgpro/ora2pgpro.conf.

File used to store/read SCN per table during export. Default: TABLES_SCN.log in the current directory. This is the file written by the --cdc_ready option.

Enable verbose output.

Allow custom type replacement in command line.

Comma-separated list of objects to exclude from export. Can be used with SHOW_COLUMN too.

Print this short help.

Extract privilege for the given object type. See possible values in GRANT_OBJECT configuration.

File containing Oracle PL/SQL code to convert with no Oracle database connection initiated.

Number of parallel processes to send data to Postgres Pro.

Number of parallel connections to extract data from Oracle.

Set a log file. Default is stdout.

Number of tuples extracted from Oracle and stored in memory before writing, default: 10000.

Set the Oracle schema to extract from.

Set Postgres Pro search_path.

Set the path to the output file where SQL will be written. Default: output.sql in running directory.

Enable PL/SQL to PL/pgSQL code conversion.

Number of parallel tables to extract at the same time.

Disable progress bar.

Use \ir instead of \i in the psql scripts generated.

Set the Oracle DBI data source.

Set the Oracle System Change Number (SCN) to use to export data. It will be used in the WHERE clause to get the data. It is used with COPY or INSERT.

Set the export type. It will override the one given in the configuration file (TYPE).

Set a distinct temporary directory when two or more ora2pgpro instances run in parallel.

Set the Oracle database connection user. ORA2PG_USER environment variable can be used instead.

Show ora2pgpro version and exit.

Set the password of the Oracle database user. ORA2PG_PASSWD environment variable can be used instead.

Set the WHERE clause to apply to the Oracle query to retrieve data. Can be used multiple times.

Force ora2pgpro to set tables and sequences owner like in Oracle database. If the value is set to a username, this one will be used as the objects owner. By default it is the user used to connect to the Postgres Pro database that will be the owner.

Set the Oracle NLS_LANG client encoding.

Set the Postgres Pro client encoding.

Comma-separated list of views to export as table.

Activate the migration cost evaluation with SHOW_REPORT.

Number of minutes for a cost evaluation unit. Default: 5 minutes, corresponds to a migration conducted by a Postgres Pro expert. Set it to 10 if this is your first migration.

Force ora2pgpro to dump report in HTML, used only with SHOW_REPORT. Default is to dump report as simple text.

As above but force ora2pgpro to dump report in CSV.

Report migration assessment with one CSV line per database.

Initialize a typical ora2pgpro project tree. Top directory will be created under the project base directory.

Define the base directory for ora2pgpro project trees. Default is the current directory.

Used with --dump_as_sheet to print the CSV header, especially for the first run of ora2pgpro.

Set the number of man-days limit where the migration assessment level switches from B to C. Default is set to 5 man-days.

Comma-separated list of usernames to filter queries in the AUDIT_USER table. Used only with SHOW_REPORT and QUERY export type.

Set the data source to Postgres Pro for direct import.

Set the Postgres Pro user to be used.

Set the Postgres Pro password to be used.

Force ora2pgpro to perform a real row count in TEST, TEST_COUNT, and SHOW_TABLE actions.

Do not append ora2pgpro header to output file.

Use to know at which speed Oracle is able to send data. No data will be processed or written.

Use to know at which speed ora2pgpro is able to send transformed data. Nothing will be written.

Export BLOBs as large objects, can only be used with action SHOW_COLUMN, TABLE, and INSERT.

Use current SCN per table to export data and register them into a file named TABLES_SCN.log by default. It can be changed using -C|--cdc_file.

Use psql \lo_import command to import BLOBs as large objects. Can be used to import data with COPY and import the large object manually in a second pass. It is required for BLOBs larger than 1GB.

Comma-separated list of materialized views to export as regular tables.

Drop the object before creation if it exists.

Used to set ORACLE_HOME environment variable to the Oracle libraries required by the DBD::Oracle Perl module.

This directive is used to set the data source name in the form of standard DBI DSN. For example:

dbi:Oracle:host=oradb_host.myhost.com;sid=DB_SID;port=1521

or

dbi:Oracle:DB_SID

On 18c, this could be for example:

dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521

For the second notation, the SID should be declared in the file $ORACLE_HOME/network/admin/tnsnames.ora or in the path given to the TNS_ADMIN environment variable.

These two directives are used to define the user and password for the Oracle database connection. Note that if you can, it is better to log in as Oracle super admin to avoid grants problem during the database scan and be sure that nothing is missing.

If you do not supply a credential with ORACLE_PWD, and you have installed the Term::ReadKey Perl module, ora2pgpro will ask for the password interactively. If ORACLE_USER is not set, it will be asked interactively too.

To connect to a local Oracle instance with connections as SYSDBA, you have to set ORACLE_USER to / and an empty password.

Set this directive to 1 if you connect to the Oracle database as a simple user and do not have enough grants to extract things from the DBA_ tables. It will use tables ALL_ instead.

Warning: if you use the export type GRANT, you must set this configuration option to 0, or it will not work.

This directive may be used if you want to change the default isolation level of the data export transaction. Default is to set the level to a serializable transaction to ensure data consistency. The allowed values for this directive are:

This directive can be used to send an initial command to Oracle, just after the connection, for example, to unlock a policy before reading objects or to set some session parameters. This directive can be used multiple times.

By default, all messages are sent to the standard output. If you give a file path to that directive, all output will be appended to this file.

This directive is used to set the schema name to use during export. For example, SCHEMA APPS will extract objects associated to the APPS schema.

When no schema name is provided and EXPORT_SCHEMA is enabled, ora2pgpro will export all objects from all schemas of the Oracle instance with their names prefixed with the schema name.

By default, the Oracle schema is not exported into the Postgres Pro database, and all objects are created under the default Postgres Pro namespace. If you also want to export this schema and create all objects under this namespace, set the EXPORT_SCHEMA directive to 1. This will set the schema search_path at the top of the export SQL file to the schema name set in the SCHEMA directive with the default pg_catalog schema. If you want to change this path, use the directive PG_SCHEMA.

Enable/disable the CREATE SCHEMA command at starting of the output file. It is enabled by default and concerns the TABLE export type.

By default ora2pgpro will only export valid PL/SQL code. You can force Oracle to compile again the invalidated code to get a chance to have it obtain the valid status and then be able to export it.

Enable this directive to force Oracle to compile schema before exporting code. When this directive is enabled, and SCHEMA is set to a specific schema name, only invalid objects in this schema will be recompiled. If SCHEMA is not set, then all schemas will be recompiled. To force recompile invalid objects in a specific schema, set COMPILE_SCHEMA to the schema name you want to recompile.

This will ask to Oracle to validate the PL/SQL code that could have been invalidated after an export/import, for example. The VALID or INVALID status applies to functions, procedures, packages, and user-defined types. It also concerns disabled triggers.

If the above configuration directive is not enough to validate your PL/SQL code, enable this configuration directive to allow export of all PL/SQL code even if it is marked as invalid. The VALID or INVALID status applies to functions, procedures, packages and user-defined types.

Allows you to define/force the Postgres Pro schema to use. By default, if you set EXPORT_SCHEMA to 1, the Postgres Pro search_path will be set to the schema name exported set as the value of the SCHEMA directive.

The value can be a comma-separated list of schema names but not when using the TABLE export type because in this case it will generate the CREATE SCHEMA statement, and it does not support multiple schema names. For example, if you set PG_SCHEMA to something like user_schema, public, the search path will be set like this:

SET search_path = user_schema, public;

This forces the use of another schema (here user_schema) than the one from Oracle schema set in SCHEMA directive.

You can also set the default search_path for the Postgres Pro user you are using to connect to the destination database by using:

ALTER ROLE username SET search_path TO user_schema, public;

In this case, you do not have to set PG_SCHEMA.

Without an explicit schema, ora2pgpro will export all objects that do not belong to the system schema or role:

SYSTEM,CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,
OLAPSYS,ORDDATA,OWBSYS,ORDPLUGINS,ORDSYS,OUTLN,
SI_INFORMTN_SCHEMA,SYS,SYSMAN,WK_TEST,WKSYS,WKPROXY,
WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
FLOWS_040100,FLOWS_010600,FLOWS_FILES,MDDATA,ORACLE_OCM,
SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,
SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,APEX_040000,APEX_040200,
DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS,DVSYS,DVF,
AUDSYS,APEX_030200,MGMT_VIEW,ODM,ODM_MTR,TRACESRV,MTMSYS,
OWBSYS_AUDIT,WEBSYS,WK_PROXY,OSE$HTTP$ADMIN,
AURORA$JIS$UTILITY$,AURORA$ORB$UNAUTHENTICATED,
DBMS_PRIVILEGE_CAPTURE,CSMIG,MGDSYS,SDE,DBSFWUSER

Following your Oracle installation, you may have several other system roles defined. To append these users to the schema exclusion list, set the SYSUSERS configuration directive to a comma-separated list of system users to exclude. For example:

SYSUSERS        INTERNAL,SYSDBA,BI,HR,IX,OE,PM,SH

This will add users INTERNAL and SYSDBA to the schema exclusion list.

By default the owner of the database objects is the one you are using to connect to Postgres Pro using psql. If you use another user (postgres, for example), you can force ora2pgpro to set the object owner to be the one used in the Oracle database by setting the directive to 1, or to a completely different username by setting the directive value to that username.

ora2pgpro use the function security privileges set in Oracle, and it is often defined as SECURITY DEFINER. If you want to override those security privileges for all functions and use SECURITY INVOKER instead, enable this directive.

When enabled, this directive forces ora2pgpro to export all tables, indexes constraints and indexes using the tablespace name defined in Oracle database. This works only with tablespaces that are not TEMP, USERS, and SYSTEM.

Activating this directive will force ora2pgpro to add WITH OIDS when creating tables or views as tables. Default is the same as in Postgres Pro, disabled.

List of schemas to get functions/procedures meta information that is used in the current schema export. When replacing call to function with OUT parameters, if a function is declared in another package then the function call rewriting can not be done because ora2pgpro only knows about functions declared in the current schema. By setting a comma-separated list of schemas as the value of this directive, ora2pgpro will look forward in these packages for all functions, procedures and packages declaration before proceeding to current schema export.

Force ora2pgpro to not look for function declaration. Note that this will prevent ora2pgpro to rewrite function replacement call if needed. Do not enable it unless looking forward at function breaks other export.

By default, ora2pgpro is looking at indexes to see the spatial constraint type and dimensions defined under Oracle. Those constraints are passed at index creation using for example:

CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=point');

If those Oracle constraints parameters are not set, the default is to export those columns as generic type GEOMETRY to be able to receive any spatial type.

The AUTODETECT_SPATIAL_TYPE directive allows to force ora2pgpro to autodetect the real spatial type and dimensions used in a spatial column otherwise a non-constrained geometry type is used. Enabling this feature will force ora2pgpro to scan a sample of 50000 column to look at the GTYPE used. You can increase or reduce the sample size by setting the value of AUTODETECT_SPATIAL_TYPE to the desired number of line to scan. The directive is enabled by default.

For example, in the case of a column named shape and defined with Oracle type SDO_GEOMETRY, with AUTODETECT_SPATIAL_TYPE disabled, it will be converted as:

shape geometry(GEOMETRY) or shape geometry(GEOMETRYZ, 4326)

If the directive is enabled and the column just contains a single geometry type that uses a single dimension with a two or three dimensional polygon:

shape geometry(POLYGON, 4326) or shape geometry(POLYGONZ, 4326)

This directive allows you to control the automatic conversion of Oracle SRID to standard EPSG. If enabled, ora2pgpro will use the Oracle function sdo_cs.map_oracle_srid_to_epsg() to convert all SRIDs. Enabled by default.

If the SDO_SRID returned by Oracle is NULL, it will be replaced by the default value 8307 converted to its EPSG value: 4326 (see DEFAULT_SRID).

If the value is more than 1, all SRIDs will be forced to this value, in this case DEFAULT_SRID will not be used when Oracle returns a null value and the value will be forced to CONVERT_SRID.

Note that it is also possible to set the EPSG value on Oracle side when sdo_cs.map_oracle_srid_to_epsg() returns NULL if your want to force the value:

  system@db> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;
  

Use this directive to override the default EPSG SRID to be used: 4326. Can be overwritten by CONVERT_SRID, see above.

This directive can take three values: WKT (default), WKB, and INTERNAL. When it is set to WKT, ora2pgpro will use SDO_UTIL.TO_WKTGEOMETRY() to extract the geometry data. When it is set to WKB, ora2pgpro will use the binary output using SDO_UTIL.TO_WKBGEOMETRY(). If those two extract types are calls at Oracle side, they are slow and you can easily reach Out Of Memory when you have a lot of rows. Also WKB is not able to export 3D geometry and some geometries like CURVEPOLYGON. In this case you may use the INTERNAL extraction type. It will use a Pure Perl library to convert the SDO_GEOMETRY data into a WKT representation, the translation is done on ora2pgpro side. This is a work in progress, validate your exported data geometries before use. Default spatial object extraction type is INTERNAL.

Use this directive to add a specific schema to the search path to look for PostGIS functions.

Oracle function to use to extract the SRID from ST_Geometry meta information. Default: ST_SRID, for example, it should be set to sde.st_srid for ArcSDE.

Oracle function to use to extract the dimension from ST_Geometry meta information. Default: ST_DIMENSION, for example it should be set to sde.st_dimention for ArcSDE.

Oracle function to use to extract the geometry type from a ST_Geometry column. Default: ST_GEOMETRYTYPE, for example it should be set to sde.st_geometrytype for ArcSDE.

Oracle function to be used to convert an ST_Geometry value into WKB format. Default: ST_ASBINARY, for example it should be set to sde.st_asbinary for ArcSDE.

Oracle function to be used to convert an ST_Geometry value into WKT format. Default: ST_ASTEXT, for example it should be set to sde.st_astext for ArcSDE.

When you are performing INSERT/COPY export, ora2pgpro proceeds by chunks of DATA_LIMIT tuples for speed improvement. Tuples are stored in memory before being written to disk, so if you want speed and have enough system resources you can raise this limit to an higher value for example: 100000 or 1000000. A value of 0 means that the chunk will be set to the default: 10000.

When ora2pgpro detects a table with some BLOB, it will automatically reduce the value of this directive by dividing it by 10 until its value is below 1000. You can control this value by setting BLOB_LIMIT. Exporting BLOB use lot of resources, setting it to a too high value can produce OOM.

The ora2pgpro output filename can be changed with this directive. Default value is output.sql. If you set the file name with extension .gz or .bz2, the output will be automatically compressed. This requires that the Compress::Zlib Perl module is installed if the filename extension is .gz and that the bzip2 system command is installed for the .bz2 extension.

You can define a base directory where the file will be written. The directory must exist.

This directive allows you to specify the full path to the bzip2 program if it can not be found in the PATH environment variable.

Allow object constraints to be saved in a separate file during schema export. The file will be named CONSTRAINTS_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can use .gz or .bz2 extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE export type.

The constraints can be imported quickly into Postgres Pro using the LOAD export type to parallelize their creation over multiple (-j or JOBS) connections.

Allow indexes to be saved in a separate file during schema export. The file will be named INDEXES_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can use .gz or .bz2 file extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE AND TABLESPACE export type. With the TABLESPACE export, it is used to write ALTER INDEX ... TABLESPACE ... into a separate file named TBSP_INDEXES_OUTPUT that can be loaded at end of the migration after the indexes creation to move the indexes.

The indexes can be imported quickly into Postgres Pro using the LOAD export type to parallelize their creation over multiple (-j or JOBS) connections.

Allow foreign key declaration to be saved in a separate file during schema export. By default foreign keys are exported into the main output file or in the CONSTRAINT_output.sql file. When enabled, foreign keys will be exported into a file named FKEYS_output.sql.

Allow data export to be saved in one file per table/view. The files will be named as tablename_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can still use .gz or .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all data in one file, set it to 1 to enable this feature. This is usable only during INSERT or COPY export type.

Allow functions, procedures and triggers to be saved in one file per object. The files will be named as objectname_OUTPUT, where OUTPUT is the value of the corresponding configuration directive. You can still use .gz or .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all in one single file, set it to 1 to enable this feature. This is usable only during the corresponding export type, the package body export has a special behavior.

When export type is PACKAGE, and you enabled this directive, ora2pgpro will create a directory per package, named with the lower-case name of the package, and will create one file per function/procedure into that directory. If the configuration directive is not enabled, it will create one file per package as packagename_OUTPUT, where OUTPUT is the value of the corresponding directive.

If this directive is set to 1, a TRUNCATE TABLE instruction will be added before loading data. This is usable only during INSERT or COPY export type.

When activated, the instruction will be added only if there is no global DELETE clause or not one specific to the current table (see below).

Support for include a DELETE FROM ... WHERE clause filter before importing data and perform a deletion of some lines instead of truncating tables. Value is constructed as follows: TABLE_NAME[DELETE_WHERE_CLAUSE], or if you have only one WHERE clause for all tables just put the DELETE clause as a single value. Both are possible too. Here are some examples:

DELETE  1=1    # Apply to all tables and delete all tuples
DELETE  TABLE_TEST[ID1='001']   # Apply only on table TABLE_TEST
DELETE  TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']

The last applies two different delete where clause on tables TABLE_TEST and TABLE_INFO and a generic DELETE clause on DATE_CREATE to all other tables. If TRUNCATE_TABLE is enabled, it will be applied to all tables not covered by the DELETE definition. These DELETE clauses might be useful with regular updates.

Set this parameter to 0 to not include the call to \set ON_ERROR_STOP ON in all SQL scripts generated by ora2pgpro. By default this order is always present so that the script will immediately abort when an error is encountered.

Enable this directive to use COPY FREEZE instead of a simple COPY to export data with rows already frozen. This is intended as a performance option for initial data loading. Rows will be frozen only if the table being loaded has been created or truncated in the current sub-transaction. This will only work with export to file and when -J or ORACLE_COPIES is not set or defaults to 1. It can be used with direct import into Postgres Pro under the same condition, but -j or JOBS must also be unset or default to 1.

By default ora2pgpro uses CREATE OR REPLACE in functions and views DDL, if you need not to override existing functions or views disable this configuration directive, DDL will not include OR REPLACE.

To add a DROP OBJECT IF EXISTS before creating the object, enable this directive. Can be useful in an iterative work. Default is disabled.

Postgres Pro does not support Global Temporary Table natively but you can use the pgtt extension to emulate this behavior. Enable this directive to export global temporary table.

Enabling this directive will prevent ora2pgpro to print its header into output files. Only the translated code will be written.

By default ora2pgpro use \i psql command to execute generated SQL files if you want to use a relative path following the script execution file enabling this option will use \ir. See psql help for more information.

Number of rows that must be retrieved on both sides for data validation. Default it to compare the 10000 first rows. A value of 0 means to compare all rows.

Order of rows between both sides is different once the data have been modified. In this case data must be ordered using a primary key or a unique index, that means that a table without such objects can not be compared. If the validation is done just after the data migration without any data modification, the validation can be done on all tables without any ordering.

Stop validating data from a table after a certain amount of row mismatch. Default is to stop after 10 rows validation errors.

Use this directive to specify which transformation should be applied to a column when exporting data. Value must be a semicolon-separated list of the following:

TABLE[COLUMN_NAME, code in SELECT target list]

For example, to replace string “Oracle” by “PostgreSQL” in a varchar2 column, use the following:

TRANSFORM_VALUE   ERROR_LOG_SAMPLE[DBMS_TYPE:regexp_replace("DBMS_TYPE",'Oracle','PostgreSQL')]

To replace all Oracle char(0) in a string with a space character:

TRANSFORM_VALUE   CLOB_TABLE[CHARDATA:translate("CHARDATA", chr(0), ' ')]

The expression will be applied in the SQL statement used to extract data from the source database.

Use this directive to set the Postgres Pro data source namespace using DBD::Pg Perl module as follows:

dbi:Pg:dbname=pgdb;host=localhost;port=5432

It will connect to the database pgdb on localhost at TCP port 5432.

Note that this directive is only used for data export, other export needs to be imported manually through the use of psql or any other Postgres Pro client.

To use SSL-encrypted connection, you must add sslmode=require to the connection string like this:

dbi:Pg:dbname=pgdb;host=localhost;port=5432;sslmode=require

These two directives are used to set the login user and password. If you do not supply a credential with PG_PWD, and you have installed the Term::ReadKey Perl module, ora2pgpro will ask for the password interactively. If PG_USER is not set, it will be asked interactively too.

Specifies whether the transaction commit will wait for WAL records to be written to disk before the command returns a success indication to the client. This is the equivalent to setting synchronous_commit directive of the postgresql.conf file. This is only used when you load data directly to Postgres Pro, the default is off to disable the synchronous commit to gain speed at writing data.

This directive can be used to send an initial command to Postgres Pro, just after the connection, for example, to set some session parameters. This directive can be used multiple times.

If set to 1, replace portable numeric types with Postgres Pro internal types. Oracle data type NUMBER(p,s) is approximatively converted to real and float Postgres Pro data types. If you have monetary fields or do not want rounding issues with the extra decimals, you should preserve the same numeric(p,s) Postgres Pro data type. Do that only if you need exactness because using numeric(p,s) is slower than using real or double.

If set to 1, replace portable numeric type into Postgres Pro internal type. Oracle data type NUMBER(p) or NUMBER are converted to a smallint, integer, or bigint Postgres Pro data type following the value of the precision. NUMBER without precision is set to DEFAULT_NUMERIC (see below).

NUMBER without precision is converted by default to bigint only if PG_INTEGER_TYPE is true. You can overwrite this value to any Postgres Pro type, like integer or float.

If you are experiencing any problem in data type schema conversion with this directive, you can take full control of the correspondence between Oracle and Postgres Pro types to redefine data type translation used in ora2pgpro. The syntax is a comma-separated list of Oracle datatype:Postgres Pro datatype. Here is the default list used:

DATA_TYPE       VARCHAR2:varchar,NVARCHAR2:varchar,NVARCHAR:varchar,NCHAR:char,DATE:timestamp(0),LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW(16):uuid,RAW(32):uuid,RAW:bytea,UROWID:oid,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:integer,INTEGER:integer,REAL:real,SMALLINT:smallint,BINARY_FLOAT:double precision,BINARY_DOUBLE:double precision,TIMESTAMP:timestamp,XMLTYPE:xml,BINARY_INTEGER:integer,PLS_INTEGER:integer,TIMESTAMP WITH TIME ZONE:timestamp with time zone,TIMESTAMP WITH LOCAL TIME ZONE:timestamp with time zone

The directive and the list definition must be a single line.

Note that when RAW(16) and RAW(32) columns is found or that the RAW column has SYS_GUID() as the default value, ora2pgpro will automatically translate the type of the column into uuid, which might be the right translation in most of cases. In this case data will be automatically migrated as Postgres Pro uuid data type provided by the uuid-ossp extension.

If you want to replace a type with a precision and scale, you need to escape the comma with a backslash. For example, if you want to replace all NUMBER(*,0) into bigint instead of numeric(38), add the following:

DATA_TYPE       NUMBER(*\,0):bigint

You do not have to recopy all default type conversion but just the one you want to rewrite.

There is a special case with BFILE when they are converted to type TEXT, they will just contain the full path to the external file. If you set the destination type to BYTEA, the default, ora2pgpro will export the content of the BFILE as bytea. The third case is when you set the destination type to EFILE, in this case, ora2pgpro will export it as an EFILE record: (DIRECTORY, FILENAME). Use the DIRECTORY export type to export the existing directories as well as the privileges on those directories.

There is no SQL function available to retrieve the path to BFILE. ora2pgpro has to create one using the DBMS_LOB package.

CREATE OR REPLACE FUNCTION ora2pg_get_bfilename( p_bfile IN BFILE )
RETURN VARCHAR2
AS
    l_dir   VARCHAR2(4000);
    l_fname VARCHAR2(4000);
    l_path  VARCHAR2(4000);
BEGIN
    dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
    SELECT directory_path INTO l_path FROM all_directories
        WHERE directory_name = l_dir;
    l_dir := rtrim(l_path,'/');
    RETURN l_dir || '/' || l_fname;
END;

This function is only created if ora2pgpro found a table with a BFILE column and that the destination type is TEXT. The function is dropped at the end of export. This concerns both COPY and INSERT export types.

There is no SQL function available to retrieve BFILE as an EFILE record, then ora2pgpro have to create one using the DBMS_LOB package.

CREATE OR REPLACE FUNCTION ora2pg_get_efile( p_bfile IN BFILE )
RETURN VARCHAR2
AS
    l_dir   VARCHAR2(4000);
    l_fname VARCHAR2(4000);
BEGIN
    dbms_lob.FILEGETNAME( p_bfile, l_dir, l_fname );
    RETURN '(' || l_dir || ',' || l_fnamei || ')';
END;

This function is only created if ora2pgpro found a table with a BFILE column and that the destination type is EFILE. The function is dropped at the end of the export. This concerns both COPY and INSERT export types.

To set the destination type, use the DATA_TYPE configuration directive:

DATA_TYPE       BFILE:EFILE

The EFILE type is a user-defined type created by the extension that can be found here: external_file This is a port of the BFILE Oracle type to Postgres Pro.

There is no SQL function available to retrieve the content of a BFILE. ora2pgpro have to create one using the DBMS_LOB package.

CREATE OR REPLACE FUNCTION ora2pg_get_bfile( p_bfile IN BFILE ) RETURN
BLOB
  AS
        filecontent BLOB := NULL;
        src_file BFILE := NULL;
        l_step PLS_INTEGER := 12000;
        l_dir   VARCHAR2(4000);
        l_fname VARCHAR2(4000);
        offset NUMBER := 1;
  BEGIN
    IF p_bfile IS NULL THEN
      RETURN NULL;
    END IF;

DBMS_LOB.FILEGETNAME( p_bfile, l_dir, l_fname );
src_file := BFILENAME( l_dir, l_fname );
IF src_file IS NULL THEN
    RETURN NULL;
END IF;

    DBMS_LOB.FILEOPEN(src_file, DBMS_LOB.FILE_READONLY);
    DBMS_LOB.CREATETEMPORARY(filecontent, true);
    DBMS_LOB.LOADBLOBFROMFILE (filecontent, src_file, DBMS_LOB.LOBMAXSIZE, offset, offset);
    DBMS_LOB.FILECLOSE(src_file);
    RETURN filecontent;
END;

This function is only created if ora2pgpro found a table with a BFILE column and that the destination type is bytea (the default). The function is dropped at the end of the export. This concerns both COPY and INSERT export type.

About the ROWID and UROWID, they are converted into OID by logical default but this will throw an error at data import. There is no equivalent data type so you might want to use the DATA_TYPE directive to change the corresponding type in Postgres Pro. You should consider replacing this data type by a bigserial (autoincremented sequence), text or uuid data type.

Sometimes you need to force the destination type, for example a column exported as timestamp by ora2pgpro can be forced into type date. Value is a comma-separated list of TABLE:COLUMN:TYPE structure. If you need to use comma or space inside type definition, you will have to backslash them.

MODIFY_TYPE     TABLE1:COL3:varchar,TABLE1:COL4:decimal(9\,6)

Type of table1.col3 will be replaced by a varchar and table1.col4 by a decimal with precision and scale.

If the column's type is a user-defined type, ora2pgpro will autodetect the composite type and will export its data using ROW(). Some Oracle user defined types are just array of a native type, in this case you may want to transform this column in simple array of a Postgres Pro native type. To do so, just redefine the destination type as wanted and ora2pgpro will also transform the data as an array. For example, with the following definition in Oracle:

CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
CREATE TABLE club (Name VARCHAR2(10),
        Address VARCHAR2(20),
        City VARCHAR2(20),
        Phone VARCHAR2(8),
        Members mem_type
);

Here custom type mem_type is just a string array and can be translated into the following in Postgres Pro:

CREATE TABLE club (
        name varchar(10),
        address varchar(20),
        city varchar(20),
        phone varchar(8),
        members text[]
) ;

To do so, just use the directive as follow:

MODIFY_TYPE     CLUB:MEMBERS:text[]

ora2pgpro will take care to transform all data of this column in the correct format. Only arrays of characters and numerics types are supported.

By default Oracle calls to function TO_NUMBER will be translated as a cast into numeric. For example, TO_NUMBER('10.1234') is converted into a Postgres Pro call to_number('10.1234')::numeric. If you want you can cast the call to integer or bigint by changing the value of the configuration directive. If you need better control of the format, just set it as value, for example: TO_NUMBER_CONVERSION 99999999999999999999.9999999999 will convert the code above as: TO_NUMBER('10.1234', '99999999999999999999.9999999999') Any value of the directive that is not numeric, integer, or bigint will be taken as a mask format. If set to none, no conversion will be done.

By default varchar2 without size constraint is tranlated into text. If you want to keep the varchar name, disable this directive.

Usually identity column must be bigint to correspond to an auto increment sequence so ora2pgpro always forces it to be a bigint. If, for any reason you want ora2pgpro to respect the DATA_TYPE you have set for identity column, then disable this directive.

If you want ora2pgpro to remove any timezone information into the format part of the TO_CHAR() function, enable this directive. Disabled by default.

For TABLE export, you may not want to export all schema constraints, the SKIP configuration directive allows you to specify a space or comma-separated list of constraints that should not be exported. Possible values are:

SKIP    indexes,checks

For example, this will remove indexes and check constraints from export.

Enable this directive if you want to add primary key definition inside the CREATE TABLE statement. If disabled (the default), primary key definition will be added with an ALTER TABLE statement.

By default, names of the primary and unique keys in the source Oracle database are ignored, and key names are autogenerated in the target Postgres Pro database with the Postgres Pro internal default naming rules. If you want to preserve Oracle primary and unique key names, set this option to 1.

This directive allows you to add an ON UPDATE CASCADE option to a foreign key when ON DELETE CASCADE is defined or always. Oracle does not support this feature, you have to use a trigger to operate the ON UPDATE CASCADE. As Postgres Pro has this feature, you can choose how to add the foreign key option. This directive has the following values:

When exporting tables, ora2pgpro normally exports constraints as they are, if they are non-deferrable, they are exported as non-deferrable. However, non-deferrable constraints will probably cause problems when attempting to import data to Postgres Pro. The FKEY_DEFERRABLE option set to 1 will cause all foreign key constraints to be exported as deferrable.

In addition to exporting data when the DEFER_FKEY option is set to 1, it will add a command to defer all foreign key constraints during data export, and the import will be done in a single transaction. This will work only if foreign keys have been exported as deferrable and you are not using direct import to Postgres Pro (PG_DSN is not defined). Constraints will then be checked at the end of the transaction.

This directive can also be enabled if you want to force all foreign keys to be created as deferrable and initially deferred during schema export (TABLE export type).

If deferring foreign keys is not possible due to the amount of data in a single transaction, you have not exported foreign keys as deferrable or you are using direct import to Postgres Pro, you can use the DROP_FKEY directive. It will drop all foreign keys before all data import and recreate them at the end of the import.

This directive allows you to gain lot of speed improvement during data import by removing all indexes that are not an automatic index (indexes of primary keys) and recreate them at the end of data import. It is far better to not import indexes and constraints before having imported all data.

This directive is used to disable triggers on all tables in COPY or INSERT export modes. Available values are USER (disable user-defined triggers only) and ALL (includes RI system triggers). Default is 0: do not add SQL statements to disable trigger before data import.

If you want to disable triggers during data migration, set the value to USER if you are connected as non-superuser, and ALL if you are connected as Postgres Pro superuser. A value of 1 is equal to USER.

If set to 1, it disables alter of sequences on all tables during COPY or INSERT export mode. This is used to prevent the update of sequence during data migration. Default is 0, alter sequences.

By default all data that are not of type date or time are escaped. If you experience any problem with that you can set it to 1 to disable character escaping during data export. This directive is only used during a COPY export. See STANDARD_CONFORMING_STRINGS for enabling/disabling escape with INSERT statements.

This controls whether ordinary string literals ('...') treat backslashes literally, as specified in SQL standard. This is on by default, causing ora2pgpro to use the escape string syntax (E'...') if this parameter is not set to 0. This is the exact behavior of the same option in Postgres Pro. This directive is only used during data export to build INSERT statements. See NOESCAPE for enabling/disabling escape in COPY statements.

If you want to convert CHAR(n) from Oracle into varchar(n) or text in Postgres Pro using directive DATA_TYPE, you might want to do some trimming on the data. By default, ora2pgpro will auto-detect this conversion and remove any whitespace at both leading and trailing position. If you just want to remove the leadings character set the value to LEADING. If you just want to remove the trailing character, set the value to TRAILING. Default value is BOTH.

The default trimming character is space, use this directive if you need to change the character that will be removed. For example, set it to - if you have a leading - in the char(n) field. To use space as trimming charger, comment this directive, this is the default value.

If you want to preserve the case of Oracle object names, set this directive to 1. By default, ora2pgpro will convert all Oracle object names to lower case. It is not recommended to enable this, unless you will always have to double-quote object names on all your SQL scripts.

Allow escaping of column name using Oracle reserved words. Value is a list of comma-separated reserved words. Default: audit,comment,references.

Enable this directive if you have table or column names that are a reserved word for Postgres Pro. ora2pgpro will double quote the name of the object.

Set this directive to 1 to replace default password by a random password for all extracted user during a GRANT export.

In Postgres Pro, materialized views are supported with the SQL syntax CREATE MATERIALIZED VIEW. To force ora2pgpro to use the native Postgres Pro support you must enable this configuration - enable by default. If you want to use the old style with table and a set of function, you should disable it.

Set the Postgres Pro major version number of the target database. For example: 11 or 16. Default is the current major version at the time of a new release.

Use btree_gin extension to create bitmap like indexes. You will need to create the extension. Default is to create GIN index, when disabled, a B-tree index will be created.

Use this directive to set how the database handles the LongReadLen attribute to a value that will be larger than the expected size of the LOBs. The default is 1MB which may not be enough to extract BLOBs or CLOBs. If the size of the LOB exceeds the LONGREADLEN DBD::Oracle will return a “ORA-24345: A Truncation” error. Default: 1023*1024 bytes.

	Important
	If you increase the value of this directive, take care that DATA_LIMIT probably needs to be reduced. Even if you only have a 1MB blob, trying to read 10000 of them (the default DATA_LIMIT) all at once will require 10GB of memory. You may extract data from those tables separately and set a DATA_LIMIT to 500 or lower, otherwise you may experience out of memory.

If you want to bypass the “ORA-24345: A Truncation” error, set this directive to 1, it will truncate the data extracted to the LONGREADLEN value. Disabled by default so that you will be warned if your LONGREADLEN value is not high enough.

Disable this if you want to load full contents of BLOB and CLOB and not use LOB locators. In this case you will have to set LONGREADLEN to the right value. Note that this will not improve speed of BLOB export as most of the time is always consumed by the bytea escaping and in this case export is done line-by-line and not by chunk of DATA_LIMIT rows. Default is enabled, it uses LOB locators.

Oracle recommends reading from and writing to a LOB in batches using a multiple of the LOB chunk size. This chunk size defaults to 8k (8192). Recent tests shown that the best performances can be reach with higher value like 512K or 4Mb.

A quick benchmark with 30120 rows with different size of BLOB (200x5Mb, 19800x212k, 10000x942K, 100x17Mb, 20x156Mb), with DATA_LIMIT=100, LONGREADLEN=170Mb and a total table size of 20GB gives:

no lob locator  : 22m46,218s (1365 sec., avg: 22 recs/sec)
chunk size 8k   : 15m50,886s (951 sec., avg: 31 recs/sec)
chunk size 512k : 1m28,161s (88 sec., avg: 342 recs/sec)
chunk size 4Mb  : 1m23,717s (83 sec., avg: 362 recs/sec)

In conclusion it can be more than 10 time faster with LOB_CHUNK_SIZE set to 4Mb. Depending on the size of most BLOB you may want to adjust the value here. For example, if you have a majority of small lobs bellow 8K, using 8192 is better to not waste space. Default value for LOB_CHUNK_SIZE is 512000.

Force the use getStringVal() instead of getClobVal() for XML data export. Default is 1, enabled for backward compatibility. Set it to 0 to use extract method as CLOB. Note that XML value extracted with getStringVal() must not exceed VARCHAR2 size limit (4000), otherwise it will return an error.

Set it to O if you want to disable export of millisecond from Oracle timestamp columns. By default milliseconds are exported with the use of the following format:

'YYYY-MM-DD HH24:MI:SS.FF'

Disabling will force the use of the following Oracle format:

to_char(..., 'YYYY-MM-DD HH24:MI:SS')

By default milliseconds are exported.

Set this to 1 if you do not want to export comment associated to tables and columns definition. Default is enabled.

By default, ora2pgpro will set NLS_LANG to AMERICAN_AMERICA.AL32UTF8 and NLS_NCHAR to AL32UTF8. It is not recommended to change those settings but in some case it could be useful. Using your own settings with those configuration directive will change the client encoding at Oracle side by setting the environment variables $ENV{NLS_LANG} and $ENV{NLS_NCHAR}.

By default, ora2pgpro will force Perl to use UTF8 I/O encoding. This is done through a call to the Perl pragma:

use open ':utf8';

You can override this encoding by using the BINMODE directive, for example you can set it to :locale to use your locale or iso-8859-7.

use open ':locale';
use open ':encoding(iso-8859-7)';

If you have changed the NLS_LANG to non-UTF8 encoding, you might want to set this directive. Most of the time, leave this directive commented.

By default, Postgres Pro client encoding is automatically set to UTF8 to avoid encoding issues. If you have changed the value of NLS_LANG, you might have to change the encoding of the Postgres Pro client.

For the Postgres Pro supported character sets, see Character Set Support.

To force UTF8 encoding of the PL/SQL code exported, enable this directive. Could be helpful in some rare condition.

Enable/disable PL/SQL to PL/pgSQL conversion. Enabled by default.

ora2pgpro can replace all conditions with a test on NULL by a call to the coalesce() function to mimic the Oracle behavior where empty string are considered equal to NULL.

(field1 IS NULL) is replaced by (coalesce(field1::text, '') = '')
(field2 IS NOT NULL) is replaced by (field2 IS NOT NULL AND field2::text <> '')

You might want this replacement to be sure that your application will have the same behavior but if you have control on you application a better way is to change it to transform empty string into NULL because Postgres Pro makes the difference.

Force empty_clob() and empty_blob() to be exported as NULL instead as empty string for the first one and \x for the second. If NULL is allowed in your column, this might improve data export speed if you have a lot of empty lobs. Default is to preserve the exact data from Oracle.

If you do not want to export package as schema but as simple functions you might also want to replace all calls to package_name.function_name. If you disable the PACKAGE_AS_SCHEMA directive, then ora2pgpro will replace all call to package_name.function_name() by package_name_function_name(). Default is to use a schema to emulate package.

The replacement will be done in all kind of DDL or code that is parsed by the PL/SQL to PL/pgSQL converter. PLSQL_PGSQL must be enabled or -p used in command line.

Enable this directive if the rewrite of Oracle native syntax (+) of OUTER JOIN is broken. This will force ora2pgpro to not rewrite such code, default is to try to rewrite simple form of the right outer join for the moment.

By default ora2pgpro will convert call to SYS_GUID() Oracle function with a call to uuid_generate_v4 from uuid-ossp extension. You can redefined it to use the gen_random_uuid function from pgcrypto extension by changing the function name. Default to uuid_generate_v4.

Note that when a RAW(16) and RAW(32) columns is found or that the RAW column has "SYS_GUID()" as default value ora2pgpro will automatically translate the type of the column into uuid which might be the right translation in most of the case. In this case data will be automatically migrated as Postgres Pro uuid data type provided by the "uuid-ossp" extension.

By default Oracle functions are marked as STABLE as they can not modify data unless when used in PL/SQL with variable assignment or as conditional expression. You can force ora2pgpro to create these function as VOLATILE by disabling this configuration directive.

By default call to COMMIT/ROLLBACK are kept untouched by ora2pgpro to force the user to review the logic of the function. Once it is fixed in Oracle source code or you want to comment these calls, enable the following directive.

It is common to see SAVEPOINT calls inside PL/SQL procedure together with a ROLLBACK TO savepoint_name. When COMMENT_COMMIT_ROLLBACK is enabled, you may want to also comment SAVEPOINT calls, in this case enable it.

ora2pgpro replaces all string constants during the PL/SQL to PL/pgSQL translation, string constants are all text included between single quote. If you have a string placeholder used in dynamic call to queries, you can set a list of regexp to be temporary replaced to not break the parser. For example:

STRING_CONSTANT_REGEXP         <placeholder value=".*">

The list of regexp must use the semicolon as separator.

To support the Alternative Quoting Mechanism ('Q' or 'q') for String Literals set the regexp with the text capture to use to extract the text part. For example with a variable declared as

c_sample VARCHAR2(100 CHAR) := q'{This doesn't work.}';

the regexp to use must be:

ALTERNATIVE_QUOTING_REGEXP     q'{(.*)}'

ora2pgpro will use the $$ delimiter, with the example the result will be:

c_sample varchar(100) := $$This doesn't work.$$;

The value of this configuration directive can be a list of regexp separated by a semicolon. The capture part (in parenthesis) is mandatory in each regexp if you want to restore the string constant.

If you want to use functions defined in the orafce library and prevent ora2pgpro to translate calls to these functions, enable this directive.

By default ora2pgpro rewrite add_month(), add_year(), date_trunc() and to_char() functions, but you may prefer to use the orafce version of these function that do not need any code transformation.

Contains the comma-separated list of packages to allow to be exported. Used only with the PACKAGE export type. Only the last occurrence found in the file will be used.

Contains the comma-separated list of packages to exclude from export. Used only with the PACKAGE export type. Only the last occurrence found in the file will be used.

If set to 1, enables export of autonomous transactions directly as Postgres Pro autonomous transactions.

Setting it to 1 enables verbose output.

You can define common ora2pgpro configuration directives into a single file that can be imported into other configuration files with the IMPORT configuration directive as follows:

IMPORT  commonfile.conf

This will import all configuration directives defined into commonfile.conf into the current configuration file.