Ora2Pg: Configuration

Ora2Pg usage

By default Ora2Pg will look to ora2pg.conf configuration file into /etc/ora2pg/ora2pg.conf, so if the file exist you can simply execute:

	/usr/local/bin/ora2pg

If you want to call an other configuration file, just give the path as command line argument:

	/usr/local/bin/ora2pg --config /etc/ora2pg/new_ora2pg.conf

Here are all command line parameters available since version 6.0:

Usage: ora2pg [-dhvp] [--option value]

	-d | --debug      : Enable verbose output.
	-h | --help       : Print this short help.
	-v | --version    : Show Ora2Pg Version and exit.
	-c | --conf file  : Used to set an alternate configuration file than the default /etc/or2pg/ora2pg.conf.
	-l | --log   file   : Used to set a log file. Default is stdout.
	-o | --out   file   : Used to set the path to the output file where SQL will be written. Default: output.sql in running directory.
	-t | --type   export: Used to set the export type. It will override the one given in the configuration file (TYPE).
	-p | --plsql      : Enable PLSQL to PLPSQL code conversion.
	-s | --source dsn : Allow to set the Oracle DBI datasource.
	-u | --user user  : Used to set the Oracle database connection user.
	-w | --password pass: Used to set the password of the Oracle database user.
	-n | --namespace schema: Used to set the Oracle schema to extract from.
	-b | --basedir dir: Used to set the default output directory, where files resulting from exports will be stored.
	-x | --xtable relname: Used to display columns names of the given table, could be used with SHOW_COLUMN type only.
	-f | --forceowner: if set to 1 force ora2pg to set tables and sequences
			   owner. If the value is set to a username this one will
			   be set as the objects owner.
	--nls_lang code: use this to set the Oracle NLS_LANG client encoding.
        --client_encoding code: Use this to set the PostgreSQL client encoding.
	-i | --input_file file: File containing Oracle PL/SQL code to convert with
			    no Oracle database connection initiated.

Previous version do not accept any command line parameter than the path to the configuration file (/usr/local/bin/ora2pg.pl ora2pg.conf).

Oracle database connection

There's 5 configuration directives to control the access to the Oracle database.

ORACLE_HOME

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

ORACLE_DSN

This directive is used to set the data source name in the form standard DBI DSN. For example:
	dbi:Oracle:host=oradb_host.mydom.dom;sid=TEST
or
	dbi:Oracle:DB_SID
The SID must be declared in the $ORACLE_HOME/network/admin/tnsnames.ora file.

ORACLE_USER et ORACLE_PWD

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 login as Oracle super admin to avoid grants problem during the database scan and be sure that nothing is missing.

USER_GRANTS

Set this directive to 1 if you connect the Oracle database as 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 export type GRANT, you must set this configuration option to 0 or it will not works.

TRANSACTION

This directive may be used if you want to change the default isolation level of the data export transaction. Default is now to set the level to a serializable transaction to ensure data consistency. The allowed values for this directive are:
	readonly: 'SET TRANSACTION READ ONLY',
	readwrite: 'SET TRANSACTION READ WRITE',
	serializable: 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'
	committed: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED',
Releases before 6.2 used to set the isolation level to READ ONLY transaction but in some case this was breaking data consistency so now default is set to SERIALIZABLE.

Testing

Once you have set the Oracle database DSN you can execute ora2pg to see if it works. By default the configuration file will export the database schema to a file called 'output.sql'. Take a look in it to see if the schema has been exported.

Take some time here to test your installation as most of the problem take place here, the other configuration step are more technical.

Trouble shooting

If the output.sql file has not exported anything else than the Pg transaction header and footer there's two possible reasons. The perl script ora2pg dump an ORA-XXX error, that mean that you DSN or login information are wrong, check the error and your settings and try again. The perl script says nothing and the output file is empty: the user has not enough right to extract something from the database. Try to connect Oracle as super user or take a look at directive USER_GRANTS above and at next section, especiallly the SCHEMA directive.

Oracle schema to export

The Oracle database export can be limited to a specific Schema or Namespace, this can be mandatory following the database connection user.

SCHEMA

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

EXPORT_SCHEMA

By default the Oracle schema is not exported into the PostgreSQL database and all objects are created under the default Pg Namespace. If you want to also 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 top of 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.

COMPILE_SCHEMA
By default Ora2Pg 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. This will ask to Oracle to validate the PL/SQL that could have been invalidate after a export/import for example. If you set the value to 1 it will exec: DBMS_UTILITY.compile_schema(schema => sys_context('USERENV', 'SESSION_USER')); but if you provide the name of a particular schema it will use the following command: DBMS_UTILITY.compile_schema(schema => 'schemaname'); The 'VALID' or 'INVALID' status applies to functions, procedures, packages and user defined types.
EXPORT_INVALID
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.
PG_SCHEMA

Allow you to defined/force the PostgreSQL schema to use. The value can be a coma delimited list of schema name. By default if you set EXPORT_SCHEMA to 1, the PostgreSQL schema search_path will be set to the schema name set as value of the SCHEMA directive plus the default pg_catalog schema as follow:
        SET search_path = $SCHEMA, pg_catalog;
If you set PG_SCHEMA to something like "user_schema, public" for example the search path will be set like this:
	SET search_path = $PG_SCHEMA;
	-- SET search_path = user_schema, public;
This will force to not use the Oracle schema set in the SCHEMA directive.

SYSUSERS

Without explicit schema, Ora2Pg will export all objects that not belongs to system schema or role: SYS, SYSTEM, DBSNMP, OUTLN, PERFSTAT, CTXSYS, XDB, WMSYS, SYSMAN, SQLTXPLAIN, MDSYS, EXFSYS, ORDSYS, DMSYS, OLAPSYS, FLOWS_020100, FLOWS_FILES, TSMSYS. Following your Oracle installation you may have several other system role defined. To append these users to the schema exclusion list, just set the SYSUSERS configuration directive to a coma separated list of system user to exclude. For example:
	SYSUSERS	INTERNAL,SYSDBA
will add users INTERNAL and SYSDBA to the schema exclusion list.

FORCE_OWNER

By default the owner of the database objects is the one you're using to connect to PostgreSQL using the psql command. If you use an other user (postgres for exemple) you can force Ora2Pg 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.

Export type

The export action is perform following a single configuration directive 'TYPE', some other add more control on what should be really exported.

TYPE

Here are the different type of export allowed, default is TABLE:

TABLE: Extract all tables with indexes, primary keys, unique keys, foreign keys and check constraints.

VIEW: Extract only views.

GRANT: Extract roles converted to Pg groups, users and grants on all objects.

SEQUENCE: Extract all sequence and their last position.

TABLESPACE: Extract storage space, need PostgreSQL >= v8.

TRIGGER: Extract triggers defined following actions.

FUNCTION: Extract functions.

PROCEDURES: Extract procedures.

PACKAGE: Extract packages and package bodies.

DATA: Extract datas as INSERT statement.

COPY: Extract datas as COPY statement.

PARTITION: Extract range and list partitioning.

TYPE: Extract Oracle user defined type.

Only one type of export can be perform at the same time so the TYPE directive must be unique. If you have more than one only the last found in the configuration file will be registered.

Some export type can not or should not be load directly into the PostgreSQL database and still require little manual editing. This is the case for GRANT, TABLESPACE, TRIGGER, FUNCTION, PROCEDURE, TYPE and PACKAGE export types especially if you have PLSQL code or Oracle specific SQL in it.

For TABLESPACE you must ensure that file path exist on the system.

Note that you can chained multiple export by giving to the TYPE directive a coma separated list of export type.

The PARTITION export is a work in progress as table partition support is not yet implemented into PostgreSQL. Ora2Pg will convert Oracle partition using the table inheritence, trigger and function workaround. See document 5.9. Partitioning at Pg site for more information. This new feature in Ora2Pg has not been widly tested so feel free to report any bug, comment and patch.

The TYPE export allow export of user defined Oracle type. If you don't use the --plsql command line parameter it simply dump Oracle user type asis else Ora2Pg will try to convert it to PostgreSQL syntax.

Since Ora2Pg v8.1 there's three new export types:
        SHOW_SCHEMA  : display the list of schema available in the database.
	SHOW_TABLE   : display the list of tables available.
	SHOW_COLUMN  : display the list of tables columns available.

Since Ora2Pg v8.2 there's a new export type:

	SHOW_ENCODING: display the Oracle session encoding, useful to set NSL_LANG.
Those extraction keyword are use to only display the requested information and exit. This allow you to quickly know on what you are going to work. The SHOW_COLUMN allow a new ora2pg command line option: '--xtable relname' or '-x relname' to limit the displayed information to the given table.

THREAD_COUNT

This configuration directive adds multi-threading support to data export type, the value is the number of threads to use. Default to zero, disabled multi-threading.

It is only used to do the escaping to convert LOBs to byteas, as it is very CPU hungry. Putting 6 threads will only triple your throughput, if your machine has enough cores. If zero do not use threads, do not waste CPU, but be slower with bytea.

Performance seems to peak at 5 threads, if you have enough cores, and triples throughput on tables having LOB. Another important thing: because of the way threading works in perl, threads consume a lot of memory. Put a low (5000 for instance) DATA_LIMIT if you activate threading.

If your Perl installation do not support threads, multi-threading will not be enabled.
This configuration directive is available since Ora2Pg v8.7 thanks to the work of Marc Cousin.

Limiting object to export

You may want to export only a part of an Oracle database, here are a set of configuration directives that will allow you to control what parts of the database should be exported.

TABLES

This directive allow you to set a list of tables on witch the export must be limited, excluding all other tables. The value is a space separated list of table name to export.

EXCLUDE

This directive is the opposite of the previous, it allow you to define a space separated list of table name to exclude from the export.

WHERE

This directive allow you to specify a WHERE clause filter when dumping the contents of tables. Value is construct as follow: TABLE_NAME[WHERE_CLAUSE], or if you have only one where clause for each table just put the where clause as value. Both are possible too. Here are some examples:
	# Global where clause applying to all tables included in the export
	WHERE  1=1
	# Apply the where clause only on table TABLE_NAME
	WHERE  TABLE_NAME[ID1='001']
	# Applies two different where clause on tables TABLE_NAME and OTHER_TABLE and a generic
	# where clause on DATE_CREATE to all other tables
	WHERE  TABLE_NAME[ID1='001' AND ID1='002] DATE_CREATE > '2001-01-01' OTHER_TABLE[NAME='test']
Any where clause not included into a table name bracket clause will be applied to all exported table including the tables defined in the where clause. These WHERE clauses are very usefull if you want to archive some datas or at the opposite only export some recent data.

Modifying object structure

One of the great usage of Ora2Pg is its flexibility to replicate Oracle database into PostgreSQL database with a different structure or schema. There's 3 configuration directives that allow you to map those differences.

MODIFY_STRUCT

This directive allow you to limit the columns to extract for a given table. The value consist in a space separated list of table name with a set of column between parenthesis as follow:
	MODIFY_STRUCT	NOM_TABLE(nomcol1,nomcol2,...) ...

for example:

	MODIFY_STRUCT	T_TEST1(id,dossier) T_TEST2(id,fichier)
This will only extract columns 'id' and 'dossier' from table T_TEST1 and columns 'id' and 'fichier' from the T_TEST2 table.

REPLACE_TABLES

This directive allow you to remap a list of Oracle table name to a PostgreSQL table name during export. The value is a list of space separated values with the following structure:
	REPLACE_TABLES	ORIG_TBNAME1:DEST_TBNAME1 ORIG_TBNAME2:DEST_TBNAME2
Oracle tables ORIG_TBNAME1 and ORIG_TBNAME2 will be respectively renamed into DEST_TBNAME1 and DEST_TBNAME2

REPLACE_COLS

Like table name, the name of the column can be remapped to a different name using the following syntaxe:
	REPLACE_COLS	ORIG_TBNAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2)

For example:

	REPLACE_COLS	T_TEST(dico:dictionary,dossier:folder)
will rename Oracle columns 'dico' and 'dossier' from table T_TEST into new name 'dictionary' and 'folder'.

PostgreSQL Import

By default conversion to PostgreSQL format is written to file 'output.sql'. The command:

	psql mydb < output.sql

will import content of file output.sql into PostgreSQL mydb database.

DATA_LIMIT

When you are performing DATA or COPY export Ora2Pg proceed by slices 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 grow this limit to an upper value for example: 100000 or 1000000. Before release 7.0 a value of 0 mean no limit so that all tuples are stored in memory before being flushed to disk. In 7.x branch this has been remove and chunk will be set to the default: 10000

For example, a DATA_LIMIT to 10000 on a table of 30000 tuples will create 3 slices of 10000 rows each.

OUTPUT

The Ora2Pg 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 require 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.

OUTPUT_DIR

Since release 7.0, you can define a base directory where wfile will be written. The directory must exists.

BZIP2

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

FILE_PER_CONSTRAINT

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 xor .bz2 extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE export type.

FILE_PER_INDEX

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 xor .bz2 file extension to enable compression. Default is to save all data in the OUTPUT file. This directive is usable only with TABLE export type.

FILE_PER_TABLE

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 xor .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 DATA or COPY export type.

FILE_PER_FUNCTION

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 xor .bz2 extension in the OUTPUT directive to enable compression. Default 0 will save all in one file, set it to 1 to enable this feature. This is usable only during the corresponding export type.

TRUNCATE_TABLE

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

If you want to import data on the fly to the PostgreSQL database you have three configuration directives to set the PostgreSQL database connection. This is only possible with 'COPY' or 'DATA' export type as for database schema there's no real interest to do that.

PG_DSN

Use this directive to set the PostgreSQL DSN using DBD::Pg Perl module as follow:
	dbi:Pg:dbname=pgdb;host=localhost;port=5432
will connect to database 'pgdb' on localhost at tcp port 5432.

PG_USER and PG_PWD

These two directives are used to set the login user and password.

Taking export under control

The following other configuration directives interact directly with the export process and give you fine granuality in database export control.

SKIP

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

fkeys: turn off foreign key constraints

pkeys: turn off primary keys

ukeys: turn off unique column constraints

indexes: turn off all other index types

checks: turn off check constraints
For example:

        SKIP    indexes,checks

will removed indexes ans check constraints from export.
KEEP_PKEY_NAMES

By default names of the primary key in the source Oracle database are ignored and key names are created in the target PostgreSQL database with the PostgreSQL internal default naming rules. If you want to preserve Oracle primary key names set this option to 1.

FKEY_DEFERRABLE

When exporting tables, Ora2Pg 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 PostgreSQL. The FKEY_DEFERRABLE option set to 1 will cause all foreign key constraints to be exported as deferrable.

DEFER_FKEY

In addition, when exporting data the DEFER_FKEY option set to 1 will add a command to defer all foreign key constraints during data export. Constraints will then be checked at the end of each transaction. Note that this will works only if foreign keys are deferrable and that all datas can stay in a single transaction.

Since release 7.0 Ora2Pg will first try to ordered data export following the tables foreign keys. If it fails (some cases can not be handle), Ora2Pg will set constraint all deferrable if DEFER_FKEY is activated and DROP_FKEY disabled.

DROP_FKEY

New since release 7.0 this directive enabled force the deletion of all foreign keys before data import and to recreate them at end of the import. Useful when DEFER_KFEY can't works because of keys non deferrable or huge data export transaction.

DROP_INDEXES

This direction is also introduce since version 7.0 and allow you to gain lot of speed improvement during data import by removing all indexes that are not an automatic index (ex: indexes of primary keys) and recreate them at the end of data import.

DISABLE_TABLE_TRIGGERS

This directive is used to disables triggers on all tables in COPY or DATA export modes during data migration. The possible values are 0 to enable triggers, USER to disable userdefined triggers and ALL to disable userdefined triggers as well as includes RI system triggers.

DISABLE_SEQUENCE

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

NOESCAPE

By default all datas exported as INSERT statement are escaped, if you experience any problem with that set it to 1 to disable character escaping during data export.

PG_NUMERIC_TYPE

This directive set to 1 replace portable numeric type into PostgreSQL internal type as numeric(p,s) type is much slower than the different PostgreSQL number types. Oracle data type NUMBER(p,s) is approximatively converted to smallint, integer, bigint, real and float PostgreSQL numeric type following the precision. If you have lot of monetary fields you should preserve the numeric(p,s) PostgreSQL data type if you need very good precision. NUMBER without precision are set to float unless you redefine it with the DEFAULT_NUMERIC configuration option.

DEFAULT_NUMERIC

NUMBER(x) are converted by default to bigint if PG_NUMERIC_TYPE is true. You can overwrite this value to any PG numeric type, like smallint or integer. For example:
	DEFAULT_NUMERIC	integer
Note that before release 7.0 the value was wrongly set to float.

DATA_TYPE

If you're experiencing any problem 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 coma separated list of "Oracle datatype:Postgresql datatype". Here are the default list used:
	DATA_TYPE	DATE:timestamp,LONG:text,LONG RAW:text,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,
	RAW:bytea,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
Note that the directive and the list definition must be a single line.

CASE_SENSITIVE

By default Ora2P convert all object names to lower case as PostgreSQL is case insensitive. If you want to preserve the case of Oracle object name set this directive to 1. I do not recommand this unless you always quote object names on all your scripts.

ORA_SENSITIVE

Since version 4.10 you can export Oracle databases with case sensitive table/view names. This requires the use of quoted table/view names during Oracle querying. Set this configuration option to 1 to enable this feature. By default it is off.

ORA_RESERVED_WORDS

Allow escaping of column name using Oracle reserved words. Value is a list of coma separated reserved word. Default is audit,comment.

GEN_USER_PWD

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

PG_SUPPORTS_ROLE (Deprecated)

This option is deprecated since Ora2Pg release v7.3.

By default Oracle roles are translated into PostgreSQL groups. If you have PostgreSQL 8.1 or more consider the use of ROLES and set this directive to 1 to export roles.

PG_SUPPORTS_INOUT (Deprecated)

This option is deprecated since Ora2Pg release v7.3.

If set to 0, all IN, OUT or INOUT parameters will not be used into the generated PostgreSQL function declarations (disable it for PostgreSQL database version lower than 8.1), This is now enable by default, 1. Please note that things like default parameters aren't supported by PostgreSQL will not be exported.

PG_SUPPORTS_DEFAULT (Deprecated)

This option is deprecated since Ora2Pg release v8.0.

This directive enable or disable the use of default parameter value in function export. Until PostgreSQL 8.4 such a default value was not supported, this feature is now enable by default.

PG_SUPPORTS_WHEN

Add support to WHEN clause on triggers as PostgreSQL v9.0 now support it. This directive is disabled by default, set it to 1 enable this feature.

PG_SUPPORTS_INSTEADOF

Add support to INSTEAD OF usage on triggers (for incoming PG >= 9.1), if this directive is not enabled the INSTEAD OF triggers will be rewritten as Pg rules.

Special options for PLSQL to PLPGSQL conversion

Automatic code convertion from Orable PLSQL to PostgreSQL PLPGSQL is a work in progress in Ora2Pg and surely you will always have manual work. The Perl code used for automatic conversion is all stored in a specific Perl Module named Ora2Pg/PLSQL.pm feel free to modify/add you own code and send me patches. The main work in on trigger, function, procedure, package and package body headers and parameters rewrite.

PLSQL_PGSQL

Enable PLSQL to PLPSQL convertion. Default disabled.

ALLOW_CODE_BREAK

This directive is use to enable/disable the plsql to pgplsql conversion part that could break the original code if they include complex subqueries. Default is enabled, you must disabled if to preserve backward compatibility. This concern the following replacement: decode(), substr()

For example code like this:
        substr(decode("db_status",'active',"dbname",null),1,128)
can easily be replaced by the PostgreSQL equivalent:
	substring((CASE WHEN "db_status"='active' THEN "dbname" ELSE NULL END) from 1 for 128)) 
The problem could comes when you introduce subquery into one of the substr() or decode() parameter. For example the replacement of
	substr(decode("db_status",(select status from dbcluster where lbl=substr("dbname",1,3)),"dbname",null),1,128)
will break the code. You can still compare to the original Oracle code and solve the problem, but if you want you can disable this unsecure replacement.

Special options to handle character encoding

NLS_LANG

If you experience any issues where mutibyte characters are being substituted with replacement characters during the export try to set the NLS_LANG configuration directive to the Oracle encoding. This may help a lot especially with UTF8 encoding.
	NLS_LANG	AMERICAN_AMERICA.UTF8
This will set $ENV{NLS_LANG} to the given value.

BINMODE

If you experience the Perl warning: "Wide character in print", it means that you tried to write a Unicode string to a non-unicode file handle. You can force Perl to use binary mode for output by setting the BINMODE configuration option to the specified encoding. If you set it to 'utf8', it will force printing like this: binmode OUTFH, ":utf8"; By default Ora2Pg opens the output file in 'raw' binary mode.

CLIENT_ENCODING

If you experience ERROR: invalid byte sequence for encoding "UTF8": 0xe87472 when loading data you may want to set the encoding of the PostgreSQL client. By default it is not set and it will depend of you system client encoding.

For example, let's say you have an Oracle database with all data encoded in FRENCH_FRANCE.WE8ISO8859P15, your system use fr_FR.UTF-8 as console encoding and your PostgreSQL database is encoded in UTF8. What you have to do is set the NLS_LANG to FRENCH_FRANCE.WE8ISO8859P15 and the CLIENT_ENCODING to LATIN9.

You can take a look at the PostgreSQL supported character sets here: http://www.postgresql.org/docs/9.0/static/multibyte.html

Other configuration directives

DEBUG

Set it to 1 will enable verbose output.

IMPORT

You can define common Ora2Pg configuration directives into a single file that can be imported into other configuration files with the IMPORT configuration directive as follow:
	IMPORT	commonfile.conf
will import all configuration directives defined into commonfile.conf into the current configuration file.

Exporting Oracle views as PostgreSQL tables

Since version 4.10 you can export Oracle views as PostgreSQL tables simply by setting TYPE configuration option to TABLE and COPY or DATA and specifying your views in the TABLES configuration option. Then if Ora2Pg does not find the name in Oracle table names it automaticaly deduces that it must search for it in the view names, and if it finds the view it will extract its schema (if TYPE=TABLE) into a PG create table form, then it will extract the data (if TYPE=COPY or DATA) following the view schema.

Ora2Pg

Migrates Oracle databases to PostgreSQL

Configuration

Gold Sponsor

Ora2Pg usage

Oracle database connection

ORACLE_HOME

ORACLE_DSN

ORACLE_USER et ORACLE_PWD

USER_GRANTS

TRANSACTION

Testing

Trouble shooting

Oracle schema to export

SCHEMA

EXPORT_SCHEMA

COMPILE_SCHEMA

EXPORT_INVALID

PG_SCHEMA

SYSUSERS

FORCE_OWNER

Export type

TYPE

THREAD_COUNT

Limiting object to export

TABLES

EXCLUDE

WHERE

Modifying object structure

MODIFY_STRUCT

REPLACE_TABLES

REPLACE_COLS

PostgreSQL Import

DATA_LIMIT

OUTPUT

OUTPUT_DIR

BZIP2

FILE_PER_CONSTRAINT

FILE_PER_INDEX

FILE_PER_TABLE

FILE_PER_FUNCTION

TRUNCATE_TABLE

PG_DSN

PG_USER and PG_PWD

Taking export under control

SKIP

KEEP_PKEY_NAMES

FKEY_DEFERRABLE

DEFER_FKEY

DROP_FKEY

DROP_INDEXES

DISABLE_TABLE_TRIGGERS

DISABLE_SEQUENCE

NOESCAPE

PG_NUMERIC_TYPE

DEFAULT_NUMERIC

DATA_TYPE

CASE_SENSITIVE

ORA_SENSITIVE

ORA_RESERVED_WORDS

GEN_USER_PWD

PG_SUPPORTS_ROLE (Deprecated)

PG_SUPPORTS_INOUT (Deprecated)

PG_SUPPORTS_DEFAULT (Deprecated)

PG_SUPPORTS_WHEN

PG_SUPPORTS_INSTEADOF

Special options for PLSQL to PLPGSQL conversion

PLSQL_PGSQL

ALLOW_CODE_BREAK

Special options to handle character encoding

NLS_LANG

BINMODE

CLIENT_ENCODING

Other configuration directives

DEBUG

IMPORT

Exporting Oracle views as PostgreSQL tables