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.