dL8Ap5DD

1
2####################  Ora2Pg Configuration file   #####################
3
4# Support for including a common config file that may contain any
5# of the following configuration directives.
6#IMPORT	common.conf
7
8#------------------------------------------------------------------------------
9# INPUT SECTION (Oracle connection or input file)
10#------------------------------------------------------------------------------
11
12# Set this directive to a file containing PL/SQL Oracle Code like function,
13# procedure or a full package body to prevent Ora2Pg from connecting to an
14# Oracle database end just apply his conversion tool to the content of the
15# file. This can only be used with the following export type: PROCEDURE,
16# FUNCTION or PACKAGE. If you don't know what you do don't use this directive.
17#INPUT_FILE	ora_plsql_src.sql
18
19# Set the Oracle home directory
20ORACLE_HOME	/usr/lib/oracle/11.2/client64
21
22# Set Oracle database connection (datasource, user, password)
23ORACLE_DSN	dbi:Oracle:host=*;sid=*;port=1521
24ORACLE_USER	telmed
25ORACLE_PWD	*
26
27# Set this to 1 if you connect as simple user and can not extract things
28# from the DBA_... tables. It will use tables ALL_... This will not works
29# with GRANT export, you should use an Oracle DBA username at ORACLE_USER
30USER_GRANTS     1
31
32# Trace all to stderr
33DEBUG		0
34
35# This directive can be used to send an initial command to Oracle, just after
36# the connection. For example to unlock a policy before reading objects or
37# to set some session parameters. This directive can be used multiple time.
38#ORA_INITIAL_COMMAND
39
40
41#------------------------------------------------------------------------------
42# SCHEMA SECTION (Oracle schema to export and use of schema in PostgreSQL)
43#------------------------------------------------------------------------------
44
45# Export Oracle schema to PostgreSQL schema
46EXPORT_SCHEMA	1
47
48# Oracle schema/owner to use
49SCHEMA		TELMED
50
51# Enable/disable the CREATE SCHEMA SQL order at starting of the output file.
52# It is enable by default and concern on TABLE export type.
53CREATE_SCHEMA	1
54
55# Enable this directive to force Oracle to compile schema before exporting code.
56# When this directive is enabled and SCHEMA is set to a specific schema name, 
57# only invalid objects in this schema will be recompiled. If SCHEMA is not set
58# then all schema will be recompiled. To force recompile invalid object in a
59# specific schema, set COMPILE_SCHEMA to the schema name you want to recompile.
60# This will ask to Oracle to validate the PL/SQL that could have been invalidate
61# after a export/import for example. The 'VALID' or 'INVALID' status applies to
62# functions, procedures, packages and user defined types.
63COMPILE_SCHEMA	0
64
65# By default if you set EXPORT_SCHEMA to 1 the PostgreSQL search_path will be
66# set to the schema name exported set as value of the SCHEMA directive. You can
67# defined/force the PostgreSQL schema to use by using this directive.
68#
69# The value can be a comma delimited list of schema but not when using TABLE
70# export type because in this case it will generate the CREATE SCHEMA statement
71# and it doesn't support multiple schema name. For example, if you set PG_SCHEMA	
72# to something like "user_schema, public", the search path will be set like this
73#        SET search_path = user_schema, public;
74# forcing the use of an other schema (here user_schema) than the one from Oracle
75# schema set in the SCHEMA directive. You can also set the default search_path
76# for the PostgreSQL user you are using to connect to the destination database
77# by using:
78#        ALTER ROLE username SET search_path TO user_schema, public;
79#in this case you don't have to set PG_SCHEMA.
80PG_SCHEMA	telmed
81
82# Use this directive to add a specific schema to the search path to look
83# for PostGis functions.
84#POSTGIS_SCHEMA		
85
86# Allow to add a comma separated list of system user to exclude from 
87# Oracle extraction. Oracle have many of them following the modules
88# installed. By default it will suppress all object owned by the following
89# system users:
90#        CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,OLAPSYS,ORDDATA,OWBSYS,
91#        ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SYS,SYSMAN,SYSTEM,WK_TEST,
92#        WKSYS,WKPROXY,WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
93#        FLOWS_040100,FLOWS_010600,FLOWS_FILES,MDDATA,ORACLE_OCM,SPATIAL_CSW_ADMIN_USR,
94#        SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,
95#        APEX_040200,DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS
96# Other list of users set to this directive will be added to this list.
97#SYSUSERS	OE,HR
98
99
100# List of schema to get functions/procedures meta information that are used
101# in the current schema export. When replacing call to function with OUT
102# parameters, if a function is declared in an other package then the function
103# call rewriting can not be done because Ora2Pg only know about functions
104# declared in the current schema. By setting a comma separated list of schema
105# as value of this directive, Ora2Pg will look forward in these packages for
106# all functions/procedures/packages declaration before proceeding to current
107# schema export.
108#LOOK_FORWARD_FUNCTION	SCOTT,OE
109
110
111#------------------------------------------------------------------------------
112# ENCODING SECTION (Define client encoding at Oracle and PostgreSQL side)
113#------------------------------------------------------------------------------
114
115# Enforce default language setting following the Oracle database encoding. This
116# may be used with multibyte characters like UTF8. Here are the default values
117# used by Ora2Pg, you may not change them unless you have problem with this
118# encoding. This will set $ENV{NLS_LANG} to the given value.
119#NLS_LANG	AMERICAN_AMERICA.AL32UTF8
120# This will set $ENV{NLS_NCHAR} to the given value.
121#NLS_NCHAR	AL32UTF8
122
123# By default PostgreSQL client encoding is automatically set to UTF8 to avoid
124# encoding issue. If you have changed the value of NLS_LANG you might have to
125# change  the encoding of the PostgreSQL client.
126#CLIENT_ENCODING	UTF8
127
128
129#------------------------------------------------------------------------------
130# EXPORT SECTION (Export type and filters)
131#------------------------------------------------------------------------------
132
133# Type of export. Values can be the following keyword:
134#	TABLE		Export tables, constraints, indexes, ...
135#	PACKAGE		Export packages
136#	INSERT		Export data from table as INSERT statement
137#	COPY		Export data from table as COPY statement
138#	VIEW		Export views
139#	GRANT		Export grants
140#	SEQUENCE	Export sequences
141#	TRIGGER		Export triggers
142#	FUNCTION	Export functions
143#	PROCEDURE	Export procedures
144#	TABLESPACE	Export tablespace (PostgreSQL >= 8 only)
145#	TYPE		Export user defined Oracle types
146#	PARTITION	Export range or list partition (PostgreSQL >= v8.4)
147#	FDW		Export table as foreign data wrapper tables
148#	MVIEW		Export materialized view as snapshot refresh view
149#       QUERY		Convert Oracle SQL queries from a file.
150#       KETTLE		Generate XML ktr template files to be used by Kettle.
151#       DBLINK		Generate oracle foreign data wrapper server to use as dblink.
152#       SYNONYM		Export Oracle's synonyms as views on other schema's objects.
153#       DIRECTORY	Export Oracle's directories as external_file extension objects.
154#       LOAD		Dispatch a list of queries over multiple PostgreSQl connections.
155#       TEST		perform a diff between Oracle and PostgreSQL database.
156#       TEST_VIEW	perform a count on both side of rows returned by views
157
158TYPE	TABLE,COPY,VIEW,SEQUENCE,TRIGGER
159
160# Set this to 1 if you don't want to export comments associated to tables and
161# column definitions. Default is enabled.
162DISABLE_COMMENT         0
163
164# Set which object to export from. By default Ora2Pg export all objects.
165# Value must be a list of object name or regex separated by space. Note
166# that regex will not works with 8i database, use % placeholder instead
167# Ora2Pg will use the LIKE operator. There is also some extended use of
168# this directive, see chapter "Limiting object to export" in documentation.
169#ALLOW		TABLE_TEST
170
171# Set which object to exclude from export process. By default none. Value
172# must be a list of object name or regexp separated by space. Note that regex
173# will not works with 8i database, use % placeholder instead Ora2Pg will use
174# the NOT LIKE operator. There is also some extended use of this directive,
175# see chapter "Limiting object to export" in documentation.
176#EXCLUDE	OTHER_TABLES
177
178# Set which view to export as table. By default none. Value must be a list of
179# view name or regexp separated by space. If the object name is a view and the
180# export type is TABLE, the view will be exported as a create table statement.
181# If export type is COPY or INSERT, the corresponding data will be exported.
182#VIEW_AS_TABLE	VIEW_NAME
183
184# When exporting GRANTs you can specify a comma separated  list of objects
185# for which privilege will be exported. Default is export for all objects.
186# Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW, SEQUENCE,
187# PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM, DIRECTORY. Only one object
188# type is allowed at a time. For example set it to TABLE if you just want to
189# export privilege on tables. You can use the -g option to overwrite it.
190# When used this directive prevent the export of users unless it is set to
191# USER. In this case only users definitions are exported.
192#GRANT_OBJECT    TABLE
193
194# By default Ora2Pg will export your external table as file_fdw tables. If
195# you don't want to export those tables at all, set the directive to 0.
196EXTERNAL_TO_FDW		1
197
198# Add a TRUNCATE TABLE instruction before loading data on COPY and INSERT
199# export. When activated, the instruction will be added only if there's no
200# global DELETE clause or one specific to the current table (see bellow).
201TRUNCATE_TABLE	0
202
203# Support for include a DELETE FROM ... WHERE clause filter before importing
204# data and perform a delete of some lines instead of truncatinf tables.
205# Value is construct as follow: TABLE_NAME[DELETE_WHERE_CLAUSE], or
206# if you have only one where clause for all tables just put the delete
207# clause as single value. Both are possible too. Here are some examples:
208#DELETE  1=1	# Apply to all tables and delete all tuples
209#DELETE	TABLE_TEST[ID1='001']	# Apply only on table TABLE_TEST
210#DELETE	TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']
211# The last applies two different delete where clause on tables TABLE_TEST and
212# TABLE_INFO and a generic delete where clause on DATE_CREATE to all other tables.
213# If TRUNCATE_TABLE is enabled it will be applied to all tables not covered by
214# the DELETE definition.
215
216# When enabled this directive forces ora2pg to export all tables, index
217# constraints, and indexes using the tablespace name defined in Oracle database.
218# This works only with tablespaces that are not TEMP, USERS and SYSTEM.
219USE_TABLESPACE		0
220
221# Enable this directive to reorder columns and minimized the footprint
222# on disk, so that more rows fit on a data page, which is the most important
223# factor for speed. Default is same order than in Oracle table definition, 
224# that should be enough for most usage.
225REORDERING_COLUMNS	0
226
227# Support for include a WHERE clause filter when dumping the contents
228# of tables. Value is construct as follow: TABLE_NAME[WHERE_CLAUSE], or
229# if you have only one where clause for each table just put the where
230# clause as value. Both are possible too. Here are some examples:
231#WHERE  1=1	# Apply to all tables
232#WHERE	TABLE_TEST[ID1='001']	# Apply only on table TABLE_TEST
233#WHERE	TABLE_TEST[ID1='001' OR ID1='002] DATE_CREATE > '2001-01-01' TABLE_INFO[NAME='test']
234# The last applies two different where clause on tables TABLE_TEST and
235# TABLE_INFO and a generic where clause on DATE_CREATE to all other tables
236
237# Sometime you may want to extract data from an Oracle table but you need a
238# a custom query for that. Not just a "SELECT * FROM table" like Ora2Pg does
239# but a more complex query. This directive allows you to override the query
240# used by Ora2Pg to extract data. The format is TABLENAME[SQL_QUERY].
241# If you have multiple tables to extract by replacing the Ora2Pg query, you can
242# define multiple REPLACE_QUERY lines.
243#REPLACE_QUERY	EMPLOYEES[SELECT e.id,e.fisrtname,lastname FROM EMPLOYEES e JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]
244
245#------------------------------------------------------------------------------
246# FULL TEXT SEARCH SECTION (Control full text search export behaviors)
247#------------------------------------------------------------------------------
248
249# Force Ora2Pg to translate Oracle Text indexes into PostgreSQL indexes using
250# pg_trgm extension. Default is to translate CONTEXT indexes into FTS indexes
251# and CTXCAT indexes using pg_trgm. Most of the time using pg_trgm is enough,
252# this is why this directive stand for.
253#
254CONTEXT_AS_TRGM		0
255
256# By default Ora2Pg creates a function-based index to translate Oracle Text
257# indexes. 
258#    CREATE INDEX ON t_document
259#                 USING gin(to_tsvector('french', title));
260# You will have to rewrite the CONTAIN() clause using to_tsvector(), example:
261#    SELECT id,title FROM t_document
262#                    WHERE to_tsvector(title)) @@ to_tsquery('search_word');
263#
264# To force Ora2Pg to create an extra tsvector column with a dedicated triggers
265# for FTS indexes, disable this directive. In this case, Ora2Pg will add the
266# column as follow: ALTER TABLE t_document ADD COLUMN tsv_title tsvector;
267# Then update the column to compute FTS vectors if data have been loaded before
268#     UPDATE t_document SET tsv_title =
269#                       to_tsvector('french', coalesce(title,''));
270# To automatically update the column when a modification in the title column
271# appears, Ora2Pg adds the following trigger:
272#
273# CREATE FUNCTION tsv_t_document_title() RETURNS trigger AS $$
274# BEGIN
275#        IF TG_OP = 'INSERT' OR new.title != old.title THEN
276#                new.tsv_title :=
277#                to_tsvector('french', coalesce(new.title,''));
278#        END IF;
279#        return new;
280# END
281# $$ LANGUAGE plpgsql;
282# CREATE TRIGGER trig_tsv_t_document_title BEFORE INSERT OR UPDATE
283#  ON t_document
284#  FOR EACH ROW EXECUTE PROCEDURE tsv_t_document_title();
285#
286# When the Oracle text index is defined over multiple column, Ora2Pg will use
287# setweight() to set a weight in the order of the column declaration.
288#
289FTS_INDEX_ONLY	1
290
291# Use this directive to force text search configuration to use. When it is not
292# set, Ora2Pg will autodetect the stemmer used by Oracle for each index and
293# pg_catalog.english if nothing is found. 
294#
295#FTS_CONFIG	pg_catalog.french
296
297# If you want to perform your text search in an accent insensitive way, enable
298# this directive. Ora2Pg will create an helper function over unaccent() and
299# creates the pg_trgm indexes using this function. With FTS Ora2Pg will
300# redefine your text search configuration, for example:
301#
302#	CREATE TEXT SEARCH CONFIGURATION fr (COPY = pg_catalog.french); 
303#	ALTER TEXT SEARCH CONFIGURATION fr
304#		ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;
305#
306# When enabled, Ora2pg will create the wrapper function:
307#
308#	CREATE OR REPLACE FUNCTION unaccent_immutable(text)
309#	RETURNS text AS
310#	$$
311#		SELECT public.unaccent('public.unaccent', )
312#	$$  LANGUAGE sql IMMUTABLE
313#	    COST 1;
314#
315# indexes are exported as follow:
316#
317#	CREATE INDEX t_document_title_unaccent_trgm_idx ON t_document 
318#        	USING gin (unaccent_immutable(title) gin_trgm_ops);
319#
320# In your queries you will need to use the same function in the search to
321# be able to use the function-based index. Example:
322#
323#	SELECT * FROM t_document
324#		WHERE unaccent_immutable(title) LIKE '%donnees%';
325#
326USE_UNACCENT		0
327
328# Same as above but call lower() in the unaccent_immutable() function:
329#
330#      CREATE OR REPLACE FUNCTION unaccent_immutable(text)
331#      RETURNS text AS
332#      $$
333#          SELECT lower(public.unaccent('public.unaccent', ));
334#      $$ LANGUAGE sql IMMUTABLE;
335#
336USE_LOWER_UNACCENT	0
337
338
339#------------------------------------------------------------------------------
340# DATA DIFF SECTION (only delete and insert actually changed rows)
341#------------------------------------------------------------------------------
342
343# EXPERIMENTAL! Not yet working correctly with partitioned tables, parallelism,
344# and direct Postgres connection! Test before using in production!
345# This feature affects SQL output for data (INSERT or COPY).
346# The deletion and (re-)importing of data is redirected to temporary tables
347# (with configurable suffix) and matching entries (i.e. quasi-unchanged rows)
348# eliminated before actual application of the DELETE, UPDATE and INSERT.
349# Optional functions can be specified that are called before or after the
350# actual DELETE, UPDATE and INSERT per table, or after all tables have been
351# processed.
352#
353# Enable DATADIFF functionality
354DATADIFF	0
355# Use UPDATE where changed columns can be matched by the primary key
356# (otherwise rows are DELETEd and re-INSERTed, which may interfere with
357# inverse foreign keys relationships!)
358DATADIFF_UPDATE_BY_PKEY	0
359# Suffix for temporary tables holding rows to be deleted and to be inserted.
360# Pay attention to your tables names:
361# 1) There better be no two tables with names such that name1 + suffix = name2
362# 2) length(suffix) + length(tablename) < NAMEDATALEN (usually 64)
363DATADIFF_DEL_SUFFIX _del
364DATADIFF_UPD_SUFFIX _upd
365DATADIFF_INS_SUFFIX _ins
366# Allow setting the work_mem and temp_buffers parameters
367# to keep temp tables in memory and have efficient sorting, etc.
368DATADIFF_WORK_MEM	256 MB
369DATADIFF_TEMP_BUFFERS	512 MB
370
371# The following are names of functions that will be called (via SELECT)
372# after the temporary tables have been reduced (by removing matching rows)
373# and right before or right after the actual DELETE and INSERT are performed.
374# They must take four arguments, which should ideally be of type "regclass",
375# representing the real table, the "deletions", the "updates", and the
376# "insertions" temp table names, respectively. They are called before
377# re-activation of triggers, indexes, etc. (if configured).
378#DATADIFF_BEFORE   my_datadiff_handler_function
379#DATADIFF_AFTER    my_datadiff_handler_function
380
381# Another function can be called (via SELECT) right before the entire COMMIT
382# (i.e., after re-activation of indexes, triggers, etc.), which will be
383# passed in Postgres ARRAYs of the table names of the real tables, the
384# "deletions", the "updates" and the "insertions" temp tables, respectively,
385# with same array index positions belonging together. So this function should
386# take four arguments of type regclass[]
387#DATADIFF_AFTER_ALL    my_datadiff_bunch_handler_function
388# If in doubt, use schema-qualified function names here.
389# The search_path will have been set to PG_SCHEMA if EXPORT_SCHEMA == 1
390# (as defined by you in those config parameters, see above),
391# i.e., the "public" schema is not contained if EXPORT_SCHEMA == 1
392
393
394#------------------------------------------------------------------------------
395# CONSTRAINT SECTION (Control constraints export and import behaviors)
396#------------------------------------------------------------------------------
397
398# Support for turning off certain schema features in the postgres side
399# during schema export. Values can be : fkeys, pkeys, ukeys, indexes, checks
400# separated by a space character.
401# fkeys		: turn off foreign key constraints
402# pkeys		: turn off primary keys
403# ukeys		: turn off unique column constraints
404# indexes	: turn off all other index types
405# checks	: turn off check constraints
406#SKIP	fkeys pkeys ukeys indexes checks
407
408# By default names of the primary and unique key in the source Oracle database
409# are ignored and key names are autogenerated in the target PostgreSQL database
410# with the PostgreSQL internal default naming rules. If you want to preserve
411# Oracle primary and unique key names set this option to 1.
412# Please note if value of USE_TABLESPACE is set to 1 the value of this option is
413# enforced to 1 to preserve correct primary and uniqie key allocation to tablespace.
414KEEP_PKEY_NAMES		0
415
416# Enable this directive if you want to add primary key definitions inside the
417# create table statements. If disabled (the default) primary key definition
418# will be added with an alter table statement. Enable it if you are exporting
419# to GreenPlum PostgreSQL database.
420PKEY_IN_CREATE		0
421
422# This directive allow you to add an ON UPDATE CASCADE option to a foreign
423# key when a ON DELETE CASCADE is defined or always. Oracle do not support
424# this feature, you have to use trigger to operate the ON UPDATE CASCADE.
425# As PostgreSQL has this feature, you can choose how to add the foreign
426# key option. There is three value to this directive: never, the default
427# that mean that foreign keys will be declared exactly like in Oracle.
428# The second value is delete, that mean that the ON UPDATE CASCADE option
429# will be added only if the ON DELETE CASCADE is already defined on the
430# foreign Keys. The last value, always, will force all foreign keys to be
431# defined using the update option.
432FKEY_ADD_UPDATE		never
433
434# When exporting tables, Ora2Pg normally exports constraints as they are;
435# if they are non-deferrable they are exported as non-deferrable.
436# However, non-deferrable constraints will probably cause problems when
437# attempting to import data to PostgreSQL. The following option set to 1
438# will cause all foreign key constraints to be exported as deferrable
439FKEY_DEFERRABLE	0
440
441# In addition when exporting data the DEFER_FKEY option set to 1 will add
442# a command to defer all foreign key constraints during data export and
443# the import will be done in a single transaction. This will work only if
444# foreign keys have been exported as deferrable and you are not using direct
445# import to PostgreSQL (PG_DSN is not defined). Constraints will then be
446# checked at the end of the transaction. This directive can also be enabled 
447# if you want to force all foreign keys to be created as deferrable and
448# initially deferred during schema export (TABLE export type).
449DEFER_FKEY	0
450
451# If deferring foreign keys is not possible du to the amount of data in a
452# single transaction, you've not exported foreign keys as deferrable or you
453# are using direct import to PostgreSQL, you can use the DROP_FKEY directive.
454# It will drop all foreign keys before all data import and recreate them at
455# the end of the import.
456DROP_FKEY	0
457
458
459#------------------------------------------------------------------------------
460# TRIGGERS AND SEQUENCES SECTION (Control triggers and sequences behaviors)
461#------------------------------------------------------------------------------
462
463# Disables alter of sequences on all tables in COPY or INSERT mode.
464# Set to 1 if you want to disable update of sequence during data migration.
465DISABLE_SEQUENCE	0
466
467# Disables triggers on all tables in COPY or INSERT mode. Available modes 
468# are USER (user defined triggers) and ALL (includes RI system 
469# triggers). Default is 0 do not add SQL statement to disable trigger.
470# If you want to disable triggers during data migration, set the value to
471# USER if your are connected as non superuser and ALL if you are connected
472# as PostgreSQL superuser. A value of 1 is equal to USER.
473DISABLE_TRIGGERS 0
474
475
476#------------------------------------------------------------------------------
477# OBJECT MODIFICATION SECTION (Control objects structure or name modifications)
478#------------------------------------------------------------------------------
479
480# You may wish to just extract data from some fields, the following directives
481# will help you to do that. Works only with export type INSERT or COPY
482# Modify output from the following tables(fields separate by space or comma)
483#MODIFY_STRUCT	TABLE_TEST(dico,dossier)
484
485# You may wish to change table names during data extraction, especally for
486# replication use. Give a list of tables separate by space as follow.
487#REPLACE_TABLES	ORIG_TB_NAME1:NEW_TB_NAME1 ORIG_TB_NAME2:NEW_TB_NAME2 
488
489# You may wish to change column names during export. Give a list of tables
490# and columns separate by space as follow.
491#REPLACE_COLS	TB_NAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2)
492
493# By default all object names are converted to lower case, if you
494# want to preserve Oracle object name as-is set this to 1. Not recommended
495# unless you always quote all tables and columns on all your scripts.
496PRESERVE_CASE	0
497
498# Add the given value as suffix to index names. Useful if you have indexes
499# with same name as tables. Not so common but it can help.
500#INDEXES_SUFFIX		_idx
501
502# Enable this directive to rename all indexes using tablename_columns_names.
503# Could be very useful for database that have multiple time the same index name
504# or that use the same name than a table, which is not allowed by PostgreSQL
505# Disabled by default.
506INDEXES_RENAMING	0
507
508# Operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops
509# support B-tree indexes on the corresponding types. The difference from the
510# default operator classes is that the values are compared strictly character by
511# character rather than according to the locale-specific collation rules. This
512# makes these operator classes suitable for use by queries involving pattern
513# matching expressions (LIKE or POSIX regular expressions) when the database
514# does not use the standard "C" locale. If you enable, with value 1, this will
515# force Ora2Pg to export all indexes defined on varchar2() and char() columns
516# using those operators. If you set it to a value greater than 1 it will only
517# change indexes on columns where the charactere limit is greater or equal than
518# this value. For example, set it to 128 to create these kind of indexes on
519# columns of type varchar2(N) where N >= 128.
520USE_INDEX_OPCLASS	0
521
522# Enable this directive if you want that your partition table name will be
523# exported using the parent table name. Disabled by default. If you have
524# multiple partitioned table, when exported to PostgreSQL some partitions
525# could have the same name but different parent tables. This is not allowed,
526# table name must be unique. 
527PREFIX_PARTITION	0
528
529# If you don't want to reproduce the partitioning like in Oracle and want to
530# export all partitionned Oracle data into the main single table in PostgreSQL
531# enable this directive. Ora2Pg will export all data into the main table name.
532# Default is to use partitionning, Ora2Pg will export data from each partition
533# and import them into the PostgreSQL dedicated partition table.
534DISABLE_PARTITION	0
535
536# Activating this directive will force Ora2Pg to add WITH (OIDS) when creating
537# tables or views as tables. Default is same as PostgreSQL, disabled.
538WITH_OID		0
539
540# Allow escaping of column name using Oracle reserved words.
541ORA_RESERVED_WORDS	audit,comment
542
543# Enable this directive if you have tables or column names that are a reserved
544# word for PostgreSQL. Ora2Pg will double quote the name of the object. 
545USE_RESERVED_WORDS	0
546
547# By default Ora2Pg export Oracle tables with the NOLOGGING attribute as
548# UNLOGGED tables. You may want to fully disable this feature because
549# you will lost all data from unlogged table in case of PostgreSQL crash.
550# Set it to 1 to export all tables as normal table.
551DISABLE_UNLOGGED	0
552
553#------------------------------------------------------------------------------
554# OUTPUT SECTION (Control output to file or PostgreSQL database)
555#------------------------------------------------------------------------------
556
557# Define the following directive to send export directly to a PostgreSQL
558# database. This will disable file output.
559PG_DSN		dbi:Pg:dbname=*;host=127.0.0.1;port=5432
560PG_USER		test_owner
561PG_PWD		*
562
563# By default all output is dump to STDOUT if not send directly to postgresql
564# database (see above). Give a filename to save export to it. If you want
565# a Gzip'd compressed file just add the extension .gz to the filename (you
566# need perl module Compress::Zlib from CPAN). Add extension .bz2 to use Bzip2
567# compression.
568OUTPUT		output.sql
569
570# Base directory where all dumped files must be written
571#OUTPUT_DIR	/var/tmp
572
573# Path to the bzip2 program. See OUTPUT directive above.
574BZIP2	
575
576# Allow object constraints to be saved in a separate file during schema export.
577# The file will be named CONSTRAINTS_OUTPUT. Where OUTPUT is the value of the
578# corresponding configuration directive. You can use .gz xor .bz2 extension to
579# enable compression. Default is to save all data in the OUTPUT file. This 
580# directive is usable only with TABLE export type.
581FILE_PER_CONSTRAINT	0
582
583# Allow indexes to be saved in a separate file during schema export. The file
584# will be named INDEXES_OUTPUT. Where OUTPUT is the value of the corresponding
585# configuration directive. You can use the .gz, .xor, or .bz2 file extension to 
586# enable compression. Default is to save all data in the OUTPUT file. This 
587# directive is usable only with TABLE or TABLESPACE export type.  With the 
588# TABLESPACE export, it is used to write "ALTER INDEX ... TABLESPACE ..." into 
589# a separate file named TBSP_INDEXES_OUTPUT that can be loaded at end of the 
590# migration after the indexes creation to move the indexes.
591FILE_PER_INDEX		0
592
593# Allow foreign key declaration to be saved in a separate file during
594# schema export. By default foreign keys are exported into the main
595# output file or in the CONSTRAINT_output.sql file. When enabled foreign
596# keys will be exported into a file named FKEYS_output.sql
597FILE_PER_FKEYS		0
598
599# Allow data export to be saved in one file per table/view. The files
600# will be named as tablename_OUTPUT. Where OUTPUT is the value of the
601# corresponding configuration directive. You can use .gz xor .bz2
602# extension to enable compression. Default is to save all data in one
603# file. This is usable only during INSERT or COPY export type.
604FILE_PER_TABLE	0
605
606# Allow function export to be saved in one file per function/procedure.
607# The files will be named as funcname_OUTPUT. Where OUTPUT is the value
608# of the corresponding configuration directive. You can use .gz xor .bz2
609# extension to enable compression. Default is to save all data in one
610# file. It is usable during FUNCTION, PROCEDURE, TRIGGER and PACKAGE
611# export type.
612FILE_PER_FUNCTION	0
613
614# By default Ora2Pg will force Perl to use utf8 I/O encoding. This is done through
615# a call to the Perl pragma:
616#
617#    use open ':utf8';
618#
619# You can override this encoding by using the BINMODE directive, for example you
620# can set it to :locale to use your locale or iso-8859-7, it will respectively use
621# 
622#    use open ':locale';
623#    use open ':encoding(iso-8859-7)';
624# 
625# If you have change the NLS_LANG in non UTF8 encoding, you might want to set this
626# directive. See http://perldoc.perl.org/5.14.2/open.html for more information.
627# Most of the time, you might leave this directive commented.
628#BINMODE		utf8
629
630# Set it to 0 to not include the call to \set ON_ERROR_STOP ON in all SQL
631# scripts. By default this order is always present.
632STOP_ON_ERROR		1
633
634# Enable this directive to use COPY FREEZE instead of a simple COPY to
635# export data with rows already frozen. This is intended as a performance
636# option for initial data loading. Rows will be frozen only if the table
637# being loaded has been created or truncated in the current subtransaction.
638# This will only works with export to file and when -J or ORACLE_COPIES is
639# not set or default to 1. It can be used with direct import into PostgreSQL
640# under the same condition but -j or JOBS must also be unset or default to 1.
641COPY_FREEZE		0
642
643# By default Ora2Pg use CREATE OR REPLACE in function DDL, if you need not
644# to override existing functions disable this configuration directive,
645# DDL will not include OR REPLACE.
646CREATE_OR_REPLACE	1
647
648# This directive can be used to send an initial command to PostgreSQL, just
649# after the connection. For example to set some session parameters. This
650# directive can be used multiple time.
651#PG_INITIAL_COMMAND
652
653
654
655#------------------------------------------------------------------------------
656# TYPE SECTION (Control type behaviors and redefinitions)
657#------------------------------------------------------------------------------
658
659# If you're experiencing problems in data type export, the following directive
660# will help you to redefine data type translation used in Ora2pg. The syntax is
661# a comma separated list of "Oracle datatype:Postgresql data type". Here are the
662# data type that can be redefined and their default value. If you want to
663# replace a type with a precision and scale you need to escape the coma with
664# a backslash. For example, if you want to replace all NUMBER(*,0) into bigint
665# instead of numeric(38)add the following:
666# 	DATA_TYPE	NUMBER(*\,0):bigint
667# Here is the default replacement for all Oracle's types. You don't have to
668# recopy all type conversion but just the one you want to rewrite.
669#DATA_TYPE	DATE:timestamp,LONG:text,LONG RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW:bytea,UROWID:oid,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:numeric,INTEGER:numeric,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
670
671# If set to 1 replace portable numeric type into PostgreSQL internal type.
672# Oracle data type NUMBER(p,s) is approximatively converted to real and
673# float PostgreSQL data type. If you have monetary fields or don't want
674# rounding issues with the extra decimals you should preserve the same
675# numeric(p,s) PostgreSQL data type. Do that only if you need very good
676# precision because using numeric(p,s) is slower than using real or double.
677PG_NUMERIC_TYPE	1
678
679# If set to 1 replace portable numeric type into PostgreSQL internal type.
680# Oracle data type NUMBER(p) or NUMBER are converted to smallint, integer
681# or bigint PostgreSQL data type following the length of the precision. If
682# NUMBER without precision are set to DEFAULT_NUMERIC (see bellow).
683PG_INTEGER_TYPE	1
684
685# NUMBER() without precision are converted by default to bigint only if
686# PG_INTEGER_TYPE is true. You can overwrite this value to any PG type,
687# like integer or float.
688DEFAULT_NUMERIC numeric
689
690# Set it to 0 if you don't want to export milliseconds from Oracle timestamp
691# columns. Timestamp will be formated with to_char(..., 'YYYY-MM-DD HH24:MI:SS')
692# Enabling this directive, the default, format is 'YYYY-MM-DD HH24:MI:SS.FF'.
693ENABLE_MICROSECOND      1
694
695# If you want to replace some columns as PostgreSQL boolean define here a list
696# of tables and column separated by space as follows. You can also give a type
697# and a precision to automatically convert all fields of that type as a boolean.
698# For example: NUMBER:1 or CHAR:1 will replace any field of type number(1) or
699# char(1) as a boolean in all exported tables.
700#REPLACE_AS_BOOLEAN	TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2
701
702# Use this to add additional definitions of the possible boolean values in Oracle
703# field. You must set a space separated list of TRUE:FALSE values. BY default:
704#BOOLEAN_VALUES	yes:no y:n 1:0 true:false enabled:disabled
705
706# When Ora2Pg find a "zero" date: 0000-00-00 00:00:00 it is replaced by a NULL.
707# This could be a problem if your column is defined with NOT NULL constraint.
708# If you can not remove the constraint, use this directive to set an arbitral
709# date that will be used instead. You can also use -INFINITY if you don't want
710# to use a fake date.
711#REPLACE_ZERO_DATE	1970-01-01 00:00:00
712
713# Some time you need to force the destination type, for example a column
714# exported as timestamp by Ora2Pg can be forced into type date. Value is
715# a comma-separated list of TABLE:COLUMN:TYPE structure. If you need to use
716# comma or space inside type definition you will have to backslash them.
717# 
718#         MODIFY_TYPE     TABLE1:COL3:varchar,TABLE1:COL4:decimal(9,6)
719# 
720# Type of table1.col3 will be replaced by a varchar and table1.col4 by
721# a decimal with precision.
722# 
723# If the column's type is a user defined type Ora2Pg will autodetect the
724# composite type and will export its data using ROW(). Some Oracle user
725# defined types are just array of a native type, in this case you may want
726# to transform this column in simple array of a PostgreSQL native type.
727# To do so, just redefine the destination type as wanted and Ora2Pg will
728# also transform the data as an array. For example, with the following
729# definition in Oracle:
730# 
731#         CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
732#         CREATE TABLE club (Name VARCHAR2(10),
733#                 Address VARCHAR2(20),
734#                 City VARCHAR2(20),
735#                 Phone VARCHAR2(8),
736#                 Members mem_type
737#         );
738# 
739# custom type "mem_type" is just a string array and can be translated into
740# the following in PostgreSQL:
741# 
742#         CREATE TABLE club (
743#                 name varchar(10),
744#                 address varchar(20),
745#                 city varchar(20),
746#                 phone varchar(8),
747#                 members text[]
748#         ) ;
749# 
750# To do so, just use the directive as follow:
751# 
752#         MODIFY_TYPE     CLUB:MEMBERS:text[]
753# 
754# Ora2Pg will take care to transform all data of this column in the correct
755# format. Only arrays of characters and numerics types are supported.
756#MODIFY_TYPE     
757
758# By default Oracle call to function TO_NUMBER will be translated as a cast
759# into numeric. For example, TO_NUMBER('10.1234') is converted into PostgreSQL
760# call to_number('10.1234')::numeric. If you want you can cast the call to integer
761# or bigint by changing the value of the configuration directive. If you need
762# better control of the format, just set it as value, for example:
763#	TO_NUMBER_CONVERSION	99999999999999999999.9999999999
764# will convert the code above as:
765#	TO_NUMBER('10.1234', '99999999999999999999.9999999999')
766# Any value of the directive that it is not numeric, integer or bigint will
767# be taken as a mask format. If set to none, no conversion will be done.
768TO_NUMBER_CONVERSION	numeric
769
770#------------------------------------------------------------------------------
771# GRANT SECTION (Control priviledge and owner export)
772#------------------------------------------------------------------------------
773
774# Set this to 1 to replace default password for all extracted user
775# during GRANT export
776GEN_USER_PWD	0
777
778# By default the owner of database objects is the one you're using to connect
779# to PostgreSQL. If you use an other user (e.g. postgres) you can force
780# Ora2Pg to set the object owner to be the one used in the Oracle database by
781# setting the directive to 1, or to a completely different username by setting
782# the directive value # to that username. 
783FORCE_OWNER	0
784
785# Ora2Pg use the function's security privileges set in Oracle and it is often
786# defined as SECURITY DEFINER. If you want to override those security privileges
787# for all functions and use SECURITY DEFINER instead, enable this directive.
788FORCE_SECURITY_INVOKER	0
789
790#------------------------------------------------------------------------------
791# DATA SECTION (Control data export behaviors)
792#------------------------------------------------------------------------------
793
794# Extract data by bulk of DATA_LIMIT tuples at once. Default 10000. If you set
795# a high value be sure to have enough memory if you have million of rows.
796DATA_LIMIT	10000
797
798
799# When Ora2Pg detect a table with some BLOB it will automatically reduce the
800# value of this directive by dividing it by 10 until his value is below 1000.
801# You can control this value by setting BLOB_LIMIT. Exporting BLOB use lot of
802# ressources, setting it to a too high value can produce OOM.
803#BLOB_LIMIT	500
804
805# By default all data that are not of type date or time are escaped. If you
806# experience any problem with that you can set it to 1 to disable it. This
807# directive is only used during a COPY export type.
808# See STANDARD_CONFORMING_STRINGS for enabling/disabling escape with INSERT
809# statements.
810NOESCAPE	0
811
812# This directive may be used if you want to change the default isolation
813# level of the data export transaction. Default is now to set the level
814# to a serializable transaction to ensure data consistency. Here are the
815# allowed value of this directive: readonly, readwrite, serializable and
816# committed (read committed).
817TRANSACTION	serializable
818
819# This controls whether ordinary string literals ('...') treat backslashes
820# literally, as specified in SQL standard. This was the default before Ora2Pg
821# v8.5 so that all strings was escaped first, now this is currently on, causing
822# Ora2Pg to use the escape string syntax (E'...') if this parameter is not
823# set to 0. This is the exact behavior of the same option in PostgreSQL.
824# This directive is only used during INSERT export to build INSERT statements.
825# See NOESCAPE for enabling/disabling escape in COPY statements.
826STANDARD_CONFORMING_STRINGS	1
827
828# Use this directive to set the database handle's 'LongReadLen' attribute to
829# a value that will be the larger than the expected size of the LOB. The default
830# is 1MB witch may not be enough to extract BLOB objects. If the size of the LOB
831# exceeds the 'LongReadLen' DBD::Oracle will return a 'ORA-24345: A Truncation'
832# error.  Default: 1023*1024 bytes. Take a look at this page to learn more:
833# http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data_Interface_for_Persistent_LOBs
834#
835# Important note: If you increase the value of this directive take care that 
836# DATA_LIMIT will probably needs to be reduced. Even if you only have a 1MB blob
837# trying to read 10000 of them (the default DATA_LIMIT) all at once will require
838# 10GB of memory. You may extract data from those table separately and set a
839# DATA_LIMIT to 500 or lower, otherwise you may experience some out of memory.
840#LONGREADLEN	1047552
841
842# If you want to bypass the 'ORA-24345: A Truncation' error, set this directive
843# to 1, it will truncate the data extracted to the LongReadLen value.
844#LONGTRUNCOK	0
845
846# Disable this if you don't want to load full content of BLOB and CLOB and use
847# LOB locators instead. This is usefull to not having to set LONGREADLEN. Note
848# that this will not improve speed of BLOB export as most of the time is always
849# consumed by the bytea escaping and in this case export is done line by line
850# and not by chunk of DATA_LIMIT rows. For more information on how it works, see
851# http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#Data_Interface_for_LOB_Locators
852# Default is enabled, it will not use LOB locators for backward compatibility.
853NO_LOB_LOCATOR  1
854
855# Force the use of getStringVal() instead of getClobVal() for XML data export.
856# Default is 1, enabled for backward compatibility. Set here to 0 to use extract
857# method a la CLOB and export the XML code as it was stored.
858XML_PRETTY	0
859
860# Enable this directive if you want to continue direct data import on error.
861# When Ora2Pg receives an error in the COPY or INSERT statement from PostgreSQL
862# it will log the statement to a file called TABLENAME_error.log in the output
863# directory and continue to next bulk of data. Like this you can try to fix the
864# statement and manually reload the error log file. Default is disabled: abort
865# import on error.
866LOG_ON_ERROR		0
867
868# If you want to convert CHAR(n) from Oracle into varchar(n) or text under
869# PostgreSQL, you might want to do some triming on the data. By default
870# Ora2Pg will auto-detect this conversion and remove any withspace at both
871# leading and trailing position. If you just want to remove the leadings
872# character, set the value to LEADING. If you just want to remove the trailing
873# character, set the value to TRAILING. Default value is BOTH.
874TRIM_TYPE		BOTH
875
876# The default triming character is space, use the directive bellow if you need
877# to change the character that will be removed. For example, set it to - if you
878# have leading - in the char(n) field. To use space as triming charger, comment
879# this directive, this is the default value.
880#TRIM_CHAR		-
881
882# Internal timestamp retrieves from custom type are extracted in the following
883# format: 01-JAN-77 12.00.00.000000 AM. It is impossible to know the exact century
884# that must be used, so by default any year below 49 will be added to 2000
885# and others to 1900. You can use this directive to change this default value.
886# this is only relevant if you have user defined type with a column timestamp.
887INTERNAL_DATE_MAX	49
888
889# Disable this directive if you want to disable check_function_bodies.
890#
891#        SET check_function_bodies = false;
892#
893# It disables validation of the function body string during CREATE FUNCTION.
894# Default is to use de postgresql.conf setting that enable it by default.
895FUNCTION_CHECK		1
896
897#------------------------------------------------------------------------------
898# PERFORMANCES SECTION (Control export/import performances)
899#------------------------------------------------------------------------------
900
901# This configuration directive adds multiprocess support to COPY, FUNCTION 
902# and PROCEDURE export type, the value is the number of process to use.
903# Default is to not use multiprocess. This directive is used to set the number
904# of cores to used to parallelize data import into PostgreSQL. During FUNCTION
905# or PROCEDURE export type each function will be translated to plpgsql using a
906# new process, the performances gain can be very important when you have tons
907# of function to convert. There's no more limitation in parallel processing
908# than the number of cores and the PostgreSQL I/O performance capabilities.
909# Doesn't works under Windows Operating System, it is simply disabled.
910JOBS		1
911
912# Multiprocess support. This directive should defined the number of parallel
913# connection to Oracle when extracting data. The limit is the number of cores
914# on your machine. This is useful if Oracle is the bottleneck. Take care that
915# this directive can only be used if there is a column defined in DEFINED_PK.
916ORACLE_COPIES	1
917
918# Multiprocess support. This directive should defined the number of tables
919# in parallel data extraction. The limit is the number of cores on your machine.
920# Ora2Pg will open one database connection for each parallel table extraction.
921# This directive, when upper than 1, will invalidate ORACLE_COPIES but not JOBS.
922# Note that this directive when set upper that 1 will also automatically enable
923# the FILE_PER_TABLE directive if your are exporting to files.
924PARALLEL_TABLES	1
925
926# You can force Ora2Pg to use /*+ PARALLEL(tbname, degree) */ hint in each
927# query used to export data from Oracle by setting a value upper than 1 to
928# this directive. A value of 0 or 1 disable the use of parallel hint.
929# Default is disabled.
930DEFAULT_PARALLELISM_DEGREE	0
931
932# Parallel mode will not be activated if the table have less rows than
933# this directive. This prevent fork of Oracle process when it is not
934# necessary. Default is 100K rows.
935PARALLEL_MIN_ROWS		100000
936
937# Multiprocess support. This directive is used to split the select queries
938# between the different connections to Oracle if ORA_COPIES is used. Ora2Pg
939# will extract data with the following prepare statement:
940# 	SELECT * FROM TABLE WHERE MOD(COLUMN, $ORA_COPIES) = ?
941# Where $ORA_COPIES is the total number of cores used to extract data and set
942# with ORA_COPIES directive, and ? is the current core used at execution time.
943# This means that Ora2Pg needs to know the numeric column to use in this query.
944# If this column is a real, float, numeric or decimal, you must add the ROUND()
945# function with the column to round the value to the nearest integer.
946#DEFINED_PK	TABLE:COLUMN TABLE:ROUND(COLUMN)
947
948# Enabling this directive force Ora2Pg to drop all indexes on data import
949# tables, except automatic index on primary key, and recreate them at end
950# of data import. This may improve speed a lot during a fresh import.
951DROP_INDEXES	0
952
953# Specifies whether transaction commit will wait for WAL records to be written
954# to disk before the command returns a "success" indication to the client. This
955# is the equivalent to set synchronous_commit directive of postgresql.conf file.
956# This is only used when you load data directly to PostgreSQL, the default is
957# off to disable synchronous commit to gain speed at writing data. Some modified
958# versions of PostgreSQL, like Greenplum, do not have this setting, so in this
959# case set this directive to 1, ora2pg will not try to change the setting.
960SYNCHRONOUS_COMMIT	0
961
962#------------------------------------------------------------------------------
963# PLSQL SECTION (Control SQL and PL/SQL to PLPGSQL rewriting behaviors)
964#------------------------------------------------------------------------------
965
966# If the above configuration directive is not enough to validate your PL/SQL code
967# enable this configuration directive to allow export of all PL/SQL code even if
968# it is marked as invalid. The 'VALID' or 'INVALID' status applies to functions,
969# procedures, packages and user defined types.
970EXPORT_INVALID	0
971
972# Enable PLSQL to PLPSQL conversion. This is a work in progress, feel
973# free modify/add you own code and send me patches. The code is under
974# function plsql_toplpgsql in Ora2PG/PLSQL.pm. Default enabled.
975PLSQL_PGSQL	1
976
977# Ora2Pg can replace all conditions with a test on NULL by a call to the
978# coalesce() function to mimic the Oracle behavior where empty field are
979# considered equal to NULL. Ex: (field1 IS NULL) and (field2 IS NOT NULL) will
980# be replaced by (coalesce(field1::text, '') = '') and (field2 IS NOT NULL AND
981# field2::text <> ''). You might want this replacement to be sure that your
982# application will have the same behavior but if you have control on you app
983# a better way is to change it to transform empty string into NULL because
984# PostgreSQL makes the difference.
985NULL_EQUAL_EMPTY	0
986
987# Force empty_clob() and empty_blob() to be exported as NULL instead as empty
988# string for the first one and \\x for the second. If NULL is allowed in your
989# column this might improve data export speed if you have lot of empty lob.
990EMPTY_LOB_NULL		0
991
992# If you don't want to export package as schema but as simple functions you
993# might also want to replace all call to package_name.function_name. If you
994# disable the PACKAGE_AS_SCHEMA directive then Ora2Pg will replace all call
995# to package_name.function_name() by package_name_function_name(). Default
996# is to use a schema to emulate package.
997PACKAGE_AS_SCHEMA	1
998
999# Enable this directive if the rewrite of Oracle native syntax (+) of
1000# OUTER JOIN is broken. This will force Ora2Pg to not rewrite such code,
1001# default is to try to rewrite simple form of rigth outer join for the
1002# moment.
1003REWRITE_OUTER_JOIN	1
1004
1005# By default Oracle functions are marked as STABLE as they can not modify data
1006# unless when used in PL/SQL with variable assignment or as conditional
1007# expression. You can force Ora2Pg to create these function as VOLATILE by
1008# disabling the following configuration directive.
1009FUNCTION_STABLE		1
1010
1011# By default call to COMMIT/ROLLBACK are kept untouched by Ora2Pg to force
1012# the user to review the logic of the function. Once it is fixed in Oracle
1013# source code or you want to comment this calls enable the following directive
1014COMMENT_COMMIT_ROLLBACK	0
1015
1016# It is common to see SAVEPOINT call inside PL/SQL procedure together with
1017# a ROLLBACK TO savepoint_name. When COMMENT_COMMIT_ROLLBACK is enabled you
1018# may want to also comment SAVEPOINT calls, in this case enable it.
1019COMMENT_SAVEPOINT	0
1020
1021# Ora2Pg replace all string constant during the pl/sql to plpgsql translation,
1022# string constant are all text include between single quote. If you have some
1023# string placeholder used in dynamic call to queries you can set a list of
1024# regexp to be temporary replaced to not break the parser.The list of regexp
1025# must use the semi colon as separator. For exemple:
1026#STRING_CONSTANT_REGEXP		<placeholder value=".*">
1027
1028# If you want to use functions defined in the Orafce library and prevent
1029# Ora2Pg to translate call to these function, enable this directive.
1030# The Orafce library can be found here: https://github.com/orafce/orafce
1031# By default Ora2pg rewrite add_month(), add_year(), date_trunc() and
1032# to_char() functions, but you may prefer to use the orafce version of
1033# these function that do not need any code transformation.
1034USE_ORAFCE	0
1035
1036# Enable translation of autonomous transactions into a wrapper function
1037# using dblink or pg_background extension. If you don't want to use this
1038# translation and just want the function to be exported as a normal one
1039# without the pragma call, disable this directive.
1040AUTONOMOUS_TRANSACTION	1
1041
1042#------------------------------------------------------------------------------
1043# ASSESSMENT SECTION (Control migration assessment behaviors)
1044#------------------------------------------------------------------------------
1045
1046# Activate the migration cost evaluation. Must only be used with SHOW_REPORT,
1047# FUNCTION, PROCEDURE, PACKAGE and QUERY export type. Default is disabled.
1048# Note that enabling this directive will force PLSQL_PGSQL activation.
1049ESTIMATE_COST		0
1050
1051# Set the value in minutes of the migration cost evaluation unit. Default
1052# is five minutes per unit.
1053COST_UNIT_VALUE		5
1054
1055# By default when using SHOW_REPORT the migration report is generated as
1056# simple text, enabling this directive will force ora2pg to create a report
1057# in HTML format.
1058DUMP_AS_HTML		0
1059
1060# Set the total number of tables to display in the Top N per row and size
1061# list in the SHOW_TABLE and SHOW_REPORT output. Default 10.
1062TOP_MAX			10
1063
1064# Use this directive to redefined the number of human-days limit where the
1065# migration assessment level must switch from B to C. Default is set to 10
1066# human-days.
1067HUMAN_DAYS_LIMIT	5
1068
1069# Set the comma separated list of username that must be used to filter
1070# queries from the DBA_AUDIT_TRAIL table. Default is to not scan this
1071# table and to never look for queries. This parameter is used only with
1072# SHOW_REPORT and QUERY export type with no input file for queries.
1073# Note that queries will be normalized before output unlike when a file
1074# is given at input using the -i option or INPUT directive.
1075#AUDIT_USER	USERNAME1,USERNAME2
1076
1077# By default Ora2Pg will convert call to SYS_GUID() Oracle function
1078# with a call to uuid_generate_v4() from uuid-ossp extension. You can
1079# redefined it to use the gen_random_uuid() function from pgcrypto
1080# extension by changing the function name below.
1081#UUID_FUNCTION	uuid_generate_v4
1082
1083#------------------------------------------------------------------------------
1084# POSTGRESQL FEATURE SECTION (Control which PostgreSQL features are available)
1085#------------------------------------------------------------------------------
1086
1087# Allow support of WHEN clause in trigger definition PG>=9.0
1088PG_SUPPORTS_WHEN	1
1089
1090# Allow support of INSTEAD OF in triggers definition PG>=9.1
1091PG_SUPPORTS_INSTEADOF	1
1092
1093# Allow support of native MATERIALIZED VIEW PG>=9.3. If disable Ora2Pg
1094# will use old behavior, a normal table with a set of function to refresh
1095# the view.
1096PG_SUPPORTS_MVIEW	1
1097
1098# If enabled, export view with CHECK OPTION. Disable it if you have PostgreSQL
1099# version prior 9.4. Default, enabled
1100PG_SUPPORTS_CHECKOPTION	1
1101
1102# PostgreSQL versions below 9.x do not support IF EXISTS in DDL statements.
1103# Disabling the directive with value 0 will prevent Ora2Pg to add those
1104# keywords in all generated statements.
1105PG_SUPPORTS_IFEXISTS	1
1106
1107# PostgreSQL versions prior to 9.3 do not support the LATERAL keyword.
1108# This is required for the DATADIFF feature.
1109PG_SUPPORTS_LATERAL	1
1110
1111# PostgreSQL version prior to 10.0 do not have native partitioning.
1112# Enable this directive if you want to use declarative partitioning.
1113PG_SUPPORTS_PARTITION	1
1114
1115# Use btree_gin extenstion to create bitmap like index with pg >= 9.4
1116# You will need to create the extension by yourself:
1117#	create extension btree_gin;
1118# Default is to create GIN index, when disabled, a btree index will be created
1119BITMAP_AS_GIN		1
1120
1121# Use pg_background extension to create an autonomous transaction instead
1122# of using a dblink wrapper. With pg >= 9.5 only, default is to use dblink.
1123PG_BACKGROUND		0
1124
1125# By default if you have an autonomous transaction translated using dblink
1126# extension instead of pg_background the connection is defined using the
1127# values set with PG_DSN, PG_USER and PG_PWD. If you want to fully override
1128# the connection string use this directive as follow to set the connection
1129# in the autonomous transaction wrapper function.
1130#DBLINK_CONN	port=5432 dbname=pgdb host=localhost user=pguser password=pgpass
1131
1132# Some versions of PostgreSQL like Redshift doesn't support substr()
1133# and it need to be replaced by a call to substring(). In this case,
1134# disable it.
1135PG_SUPPORTS_SUBSTR	1
1136
1137# Disable this directive if you are using PG < 9.5, PL/SQL operator used in
1138# named parameter => will be replaced by PostgreSQL proprietary operator :=
1139PG_SUPPORTS_NAMED_OPERATOR	1
1140
1141# Enable this directive if you have PostgreSQL >= 10 to use IDENTITY columns
1142# instead of serial or bigserial data type. If PG_SUPPORTS_IDENTITY is disabled
1143# and there is IDENTITY column in the Oracle table, they are exported as serial
1144# or bigserial columns. When it is enabled they are exported as IDENTITY columns
1145# like:
1146#	CREATE TABLE identity_test_tab (
1147#		id bigint GENERATED ALWAYS AS IDENTITY,
1148#		description varchar(30)
1149#	) ;
1150# If there is non default sequence options set in Oracle, they will be appended
1151# after the IDENTITY keyword.
1152# Additionally in both cases, Ora2Pg will create a file AUTOINCREMENT_output.sql
1153# with a embedded function to update the associated sequences with a restart
1154# value set to "SELECT max(colname)+1 FROM tablename". Of course this file must
1155# be imported after data import otherwise sequence will be kept to start value.
1156PG_SUPPORTS_IDENTITY	1
1157
1158# PostgreSQL v11 adds support of PROCEDURE, enable it if you use such version.
1159PG_SUPPORTS_PROCEDURE	0
1160
1161#------------------------------------------------------------------------------
1162# SPATIAL SECTION (Control spatial geometry export)
1163#------------------------------------------------------------------------------
1164
1165# Enable this directive if you want Ora2Pg to detect the real spatial type and
1166# dimensions used in a spatial column. By default Ora2Pg will look at spatial
1167# indexes to see if the layer_gtype and sdo_indx_dims constraint parameters have
1168# been set, otherwise column will be created with the non-constrained "geometry"
1169# type. Enabling this feature will force Ora2Pg to scan a sample of 50000 lines
1170# to look at the GTYPE used. You can increase or reduce the sample by setting
1171# the value of AUTODETECT_SPATIAL_TYPE to the desired number of line.
1172AUTODETECT_SPATIAL_TYPE	1
1173
1174# Disable this directive if you don't want to automatically convert SRID to
1175# EPSG using the sdo_cs.map_oracle_srid_to_epsg() function. Default: enabled
1176# If the SDO_SRID returned by Oracle is NULL, it will be replaced by the
1177# default value 8307 converted to its EPSG value: 4326 (see DEFAULT_SRID)
1178# If the value is upper than 1, all SRID will be forced to this value, in
1179# this case DEFAULT_SRID will not be used when Oracle returns a null value
1180# and the value will be forced to CONVERT_SRID.
1181# Note that it is also possible to set the EPSG value on Oracle side when
1182# sdo_cs.map_oracle_srid_to_epsg() return NULL if your want to force the value:
1183# Ex: system> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;
1184CONVERT_SRID		1
1185
1186# Use this directive to override the default EPSG SRID to used: 4326.
1187# Can be overwritten by CONVERT_SRID, see above.
1188DEFAULT_SRID		4326
1189
1190# This directive can take three values: WKT (default), WKB and INTERNAL.
1191# When it is set to WKT, Ora2Pg will use SDO_UTIL.TO_WKTGEOMETRY() to
1192# extract the geometry data. When it is set to WKB, Ora2Pg will use the
1193# binary output using SDO_UTIL.TO_WKBGEOMETRY(). If those two extract type
1194# are called at Oracle side, they are slow and you can easily reach Out Of
1195# Memory when you have lot of rows. Also WKB is not able to export 3D geometry
1196# and some geometries like CURVEPOLYGON. In this case you may use the INTERNAL
1197# extraction type. It will use a pure Perl library to convert the SDO_GEOMETRY
1198# data into a WKT representation, the translation is done on Ora2Pg side.
1199# This is a work in progress, please validate your exported data geometries
1200# before use.
1201GEOMETRY_EXTRACT_TYPE	INTERNAL
1202
1203
1204#------------------------------------------------------------------------------
1205# FDW SECTION (Control Foreign Data Wrapper export)
1206#------------------------------------------------------------------------------
1207
1208# This directive is used to set the name of the foreign data server that is used
1209# in the "CREATE SERVER name FOREIGN DATA WRAPPER oracle_fdw ..." command. This
1210# name will then be used in the "CREATE FOREIGN TABLE ..." SQL command. Default
1211# is arbitrary set to orcl. This only concerns export type FDW.
1212FDW_SERVER	orcl
1213
1214
1215#------------------------------------------------------------------------------
1216# MYSQL SECTION (Control MySQL export behavior)
1217#------------------------------------------------------------------------------
1218
1219# Enable this if double pipe and double ampersand (|| and &&) should not be
1220# taken as equivalent to OR and AND. It depend of the variable @sql_mode,
1221# Use it only if Ora2Pg fail on auto detecting this behavior.
1222MYSQL_PIPES_AS_CONCAT		0
1223
1224# Enable this directive if you want EXTRACT() replacement to use the internal
1225# format returned as an integer, for example DD HH24:MM:SS will be replaced
1226# with format; DDHH24MMSS::bigint, this depend of your apps usage.
1227MYSQL_INTERNAL_EXTRACT_FORMAT	0