Type Control

Column type control

• PG_NUMERIC_TYPE

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

• PG_INTEGER_TYPE

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

• DEFAULT_NUMERIC

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

• DATA_TYPE

  If you're experiencing any problems in data type schema conversion, with this directive you can take full control of the correspondence between Oracle and PostgreSQL types to redefine data type translation used in Ora2pg. The syntax is a comma-separated list of "Oracle datatype:PostgreSQL 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) or RAW(32) columns are found or when the RAW column has "SYS_GUID()" as default value, Ora2Pg will automatically translate the type of the column into uuid which might be the right translation in most cases. In this case data will be automatically migrated as PostgreSQL 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) with bigint instead of numeric(38) add the following:

DATA_TYPE       NUMBER(*\,0):bigint

You don't have to repeat all default type conversions. Instead just specify the ones you want to rewrite.

There's 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), Ora2Pg will export the content of the BFILE as bytea. The third case is when you set the destination type to EFILE - in this case, Ora2Pg will export it as an EFILE record: (DIRECTORY, FILENAME). Use the DIRECTORY export type to export the existing directories as well as privileges on those directories.

There's no SQL function available to retrieve the path to the BFILE. Ora2Pg 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 Ora2Pg found a table with a BFILE column and that the destination type is TEXT. The function is dropped at the end of the export. This concern both, COPY and INSERT export type.

There's no SQL function available to retrieve BFILE as an EFILE record, therefore Ora2Pg needs 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 Ora2Pg finds 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

for example.

The EFILE type is a user defined type created by the PostgreSQL extension external_file that can be found here: https://github.com/darold/external\_file This is a port of the BFILE Oracle type to PostgreSQL.

There's no SQL function available to retrieve the content of a BFILE. Ora2Pg needs 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 Ora2Pg finds 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 applies to both COPY and INSERT export types.

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

• MODIFY_TYPE

  Sometimes you need to force the destination type. For example, a column exported as timestamp by Ora2Pg can be forced into type date. Value is a comma-separated list of TABLE:COLUMN:TYPE structures. If you need to use commas or spaces inside type definitions, you will have to escape them with backslashes.

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

The type of table1.col3 will be replaced by varchar and table1.col4 by decimal with precision and scale.

If the column's type is a user-defined type, Ora2Pg will autodetect the composite type and will export its data using ROW(). Some Oracle user-defined types are just arrays of a native types. In this case, you may want to transform this column into a simple array of a PostgreSQL native type. To do so, just redefine the destination type as wanted, and Ora2Pg 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
);

custom type "mem_type" is just a string array and can be translated into the following in PostgreSQL:

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 follows:

MODIFY_TYPE     CLUB:MEMBERS:text[]

Ora2Pg will take care to transform all data of this column into the correct format. Only arrays of characters and numeric types are supported.

• TO_NUMBER_CONVERSION

  By default, Oracle's call to function TO_NUMBER will be translated as a cast into numeric. For example, TO_NUMBER('10.1234') is converted into PostgreSQL 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.

• VARCHAR_TO_TEXT

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

• FORCE_IDENTITY_BIGINT

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

• TO_CHAR_NOTIMEZONE

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