new HASH_OPTIONS¶

Creates a new Ora2Pg object.

The only required option is:

    - config : Path to the configuration file (required).

All directives found in the configuration file can be overwritten in the instance call by passing them in lowercase as arguments. These supported options are (See ora2pg.conf for more details):

    - datasource : Oracle DBD datasource (required)
    - user : Oracle DBD user (optional with public access)
    - password : Oracle DBD password (optional with public access)
    - schema : Oracle internal schema to extract (optional)
    - type : Type of data to extract, can be TABLE,VIEW,GRANT,SEQUENCE,
      TRIGGER,FUNCTION,PROCEDURE,DATA,COPY,PACKAGE,TABLESPACE,PARTTION
      or a combinaison of these keywords separated by blanks.
    - debug : verbose mode.
    - export_schema : Export Oracle schema to PostgreSQL >7.3 schema
    - tables : Extract only the specified tables (arrayref) and set the
      extracting order
    - exclude : Exclude the specified tables from extraction (arrayref)
    - data_limit : bulk size of tuples to return at once during data extraction
      (defaults to 10000).
    - case_sensitive: Allow to preserve Oracle object names as they are
      written. Default is not.
    - skip_fkeys : Skip foreign key constraints extraction. Defaults to 0
      (extraction)
    - skip_pkeys : Skip primary keys extraction. Defaults to 0 (extraction)
    - skip_ukeys : Skip unique column constraints extraction. Defaults to 0
      (extraction)
    - skip_indexes : Skip all other index types extraction. Defaults to 0
      (extraction)
    - skip_checks : Skip check constraints extraction. Defaults to 0
      (extraction)
    - keep_pkey_names : By default, primary key names in the source database
      are ignored, and default key names are created in the target database.
      If this is set to true, primary key names are preserved.
    - bzip2: Path to the Bzip2 program to compress data export. Default
      /usr/bin/bzip2
    - gen_user_pwd : When set to 1 this will replace the default password
      'change_my_secret' with a random string.
    - fkey_deferrable: Force foreign key constraints to be exported as
      deferrable. Defaults to 0: export as is.
    - defer_fkey : Force all foreign key constraints to be deferred before
      data import, this require that all fkeys are deferrable and that all
      datas can be imported in a single transaction. Defaults to 0, as is.
    - drop_fkey  : Force all foreign key constraints to be dropped before
      data import and recreated at the end. Defaults to 0: export as is.
    - drop_indexes: Force deletion of non automatic index on tables before
      data import and recreate them at end of the import. Default 0, disabled.
    - pg_numeric_type: Convert the Oracle NUMBER data type to adequate PG data
      types instead of using the slow numeric(p,s) data type.
    - default_numeric: By default the NUMBER(x) type without precision is
      converted to bigint. You can overwrite this data type by any PG type.
    - keep_pkey_names: Preserve oracle primary key names. The default is to
      ignore and use PostgreSQl defaults.
      Must be used with PostgreSQL > 8.1. Defaults to none support (backward
      compatibility).
    - disable_triggers: Disable triggers on all tables in COPY and
      DATA mode.
    - disable_sequence: Disables alter sequence on all tables in COPY or
      DATA mode.
    - noescape: Disable character escaping during data export.
    - datatype: Redefine Oracle to PostgreSQl data type conversion.
    - binmode: Force Perl to use the specified binary mode for output. The
      default is ':raw';
    - sysusers: Add other system users to the default exclusion list
      (SYS,SYSTEM,DBSNMP,OUTLN,PERFSTAT,CTXSYS,XDB,WMSYS,SYSMAN,SQLTXPLAIN,
      MDSYS,EXFSYS,ORDSYS,DMSYS,OLAPSYS,FLOWS_020100,FLOWS_FILES,TSMSYS).
    - ora_sensitive: Force the use of Oracle case sensitive table/view names.
    - plsql_pgsql: Enable plsql to plpgsql conversion.
    - pg_schema: Allow to specify a coma delimited list of PostgreSQL schema.
    - file_per_constraint: Allow to create one output file containing all
      constraints during schema or table export.
    - file_per_index: Allow to create one output file containing all indexes
      during schema or table export.
    - file_per_table: Allow to create one output file per table loaded from
      output.sql. The table's files will be named ${table}_$output.
    - pg_supports_when: allow WHEN clause on trigger definition (Pg >= 9.0). 
    - pg_supports_insteadof: allow INSTEAD OF usage on trigger definition
      (Pg >= 9.1).
    - file_per_function: Allow to create one output file per function loaded
      from output.sql. The function's files will be named ${funcname}_$output.
    - truncate_table: adds trunctate table instruction before loading data.
    - xtable : used to specify a table name from which SHOW_COLUMN will work.
      Default is to show the column name of all table.
    - force_owner: force to set the table and sequences owner. If set to 1 it
      will use the oracle owner else it will use the name given as value.
    - input_file: force to use a given file as datasoure instead of an Oracle
      database connection. This file must contain either PL/SQL function or
      procedure or a package body source code. Ora2Pg will parse this code
      and outputed converted plpgsql.
    - standard_conforming_strings: same as the PostgreSQL configuration option,
      string constants should be written as E'...'. Default is on, take care for
      backward compatibility, before v8.5 Ora2Pg do not use this syntax. 
    - compile_schema: used to force Oracle to compile all PL/SQL code before
      code extraction.
    - export_invalid: force Ora2Pg to export all PL/SQL code event if it is
      maked as invalid by Oracle.
    - allow_code_break: allow plsql to pgplsql conversion that could break the
      original code if they include complex subqueries. See decode() and substr()
      replacement.

Beware that this list may grow longer because all initialization is performed this way.

Special configuration 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.

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.

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 automatically 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.

Case sensitive table names in Oracle: -------------------------------------

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

export_data FILENAME¶

OBSOLETE: you must use export_schema instead. Still here for backward compatibility. It simply callback export_schema().

export_schema FILENAME¶

Print SQL data output to a file name or to STDOUT if no file name is specified.

export_file FILENAME¶

Open a file handle to a given filename.

close_export_file FILEHANDLE¶

Close a file handle.

modify_struct TABLE_NAME ARRAYOF_FIELDNAME¶

Modify the table structure during the export. Only the specified columns will be exported.

replace_tables HASH¶

Modify table names during the export.

replace_cols HASH¶

Modify column names during the export.

set_where_clause HASH¶

Add a WHERE clause during data export on specific tables or on all tables

_init HASH_OPTIONS¶

Initialize an Ora2Pg object instance with a connexion to the Oracle database.

_send_to_pgdb DEST_DATASRC DEST_USER DEST_PASSWD¶

Open a DB handle to a PostgreSQL database

_grants¶

This function is used to retrieve all privilege information.

It extracts all Oracle's ROLES to convert them to Postgres groups (or roles) and searches all users associated to these roles.

_sequences¶

This function is used to retrieve all sequences information.

_triggers¶

This function is used to retrieve all triggers information.

_functions¶

This function is used to retrieve all functions information.

_procedures¶

This function is used to retrieve all procedures information.

_packages¶

This function is used to retrieve all packages information.

_types¶

This function is used to retrieve all custom types information.

_tables¶

This function is used to retrieve all table information.

Sets the main hash of the database structure $self->{tables}. Keys are the names of all tables retrieved from the current database. Each table information is composed of an array associated to the table_info key as array reference. In other way:

    $self->{tables}{$class_name}{table_info} = [(OWNER,TYPE)];

DBI TYPE can be TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM or a data source specific type identifier. This only extracts the TABLE type.

It also gets the following information in the DBI object to affect the main hash of the database structure :

    $self->{tables}{$class_name}{field_name} = $sth->{NAME};
    $self->{tables}{$class_name}{field_type} = $sth->{TYPE};

It also calls these other private subroutines to affect the main hash of the database structure :

    @{$self->{tables}{$class_name}{column_info}} = $self->_column_info($class_name, $owner);
    %{$self->{tables}{$class_name}{unique_key}}  = $self->_unique_key($class_name, $owner);
    @{$self->{tables}{$class_name}{foreign_key}} = $self->_foreign_key($class_name, $owner);
    %{$self->{tables}{$class_name}{check_constraint}}  = $self->_check_constraint($class_name, $owner);

_views¶

This function is used to retrieve all views information.

Sets the main hash of the views definition $self->{views}. Keys are the names of all views retrieved from the current database and values are the text definitions of the views.

It then sets the main hash as follows:

    # Definition of the view
    $self->{views}{$table}{text} = $view_infos{$table};

_tablespaces¶

This function is used to retrieve all Oracle Tablespaces information.

Sets the main hash $self->{tablespaces}.

_partitions¶

This function is used to retrieve all Oracle partition information.

Sets the main hash $self->{partition}.

_get_sql_data¶

Returns a string containing the entire PostgreSQL compatible SQL Schema definition.

_create_indexes¶

This function return SQL code to create indexes of a table

_drop_indexes¶

This function return SQL code to drop indexes of a table

_create_unique_keys¶

This function return SQL code to create unique and primary keys of a table

_create_check_constraint¶

This function return SQL code to create the check constraints of a table

_create_foreign_keys¶

This function return SQL code to create the foreign keys of a table

_drop_foreign_keys¶

This function return SQL code to the foreign keys of a table

_extract_sequence_info¶

This function retrieves the last value returned from the sequences in the Oracle database. The result is a SQL script assigning the new start values to the sequences found in the Oracle database.

_get_data TABLE¶

This function implements an Oracle-native data extraction.

Returns a list of array references containing the data

_sql_type INTERNAL_TYPE LENGTH PRECISION SCALE¶

This function returns the PostgreSQL datatype corresponding to the Oracle data type.

_column_info TABLE OWNER¶

This function implements an Oracle-native column information.

Returns a list of array references containing the following information elements for each column the specified table

[(
column name,
column type,
column length,
nullable column,
default value )]

_unique_key TABLE OWNER¶

This function implements an Oracle-native unique (including primary) key column information.

Returns a hash of hashes in the following form:
( constraintname => (type => 'PRIMARY',
columns => ('a', 'b', 'c')),
constraintname => (type => 'UNIQUE',
columns => ('b', 'c', 'd')),
etc.
)

_check_constraint TABLE OWNER¶

This function implements an Oracle-native check constraint information.

Returns a hash of lists of all column names defined as check constraints for the specified table and constraint name.

_foreign_key TABLE OWNER¶

This function implements an Oracle-native foreign key reference information.

Returns a list of hash of hash of array references. Ouf! Nothing very difficult. The first hash is composed of all foreign key names. The second hash has just two keys known as 'local' and 'remote' corresponding to the local table where the foreign key is defined and the remote table referenced by the key.

The foreign key name is composed as follows:

    'local_table_name->remote_table_name'

Foreign key data consists in two arrays representing at the same index for the local field and the remote field where the first one refers to the second one. Just like this:

    @{$link{$fkey_name}{local}} = @local_columns;
    @{$link{$fkey_name}{remote}} = @remote_columns;

_get_privilege¶

This function implements an Oracle-native obkect priviledge information.

Returns a hash of all privilede.

_get_indexes TABLE OWNER¶

This function implements an Oracle-native indexes information.

Returns a hash of an array containing all unique indexes and a hash of array of all indexe names which are not primary keys for the specified table.

_get_sequences¶

This function implements an Oracle-native sequences information.

Returns a hash of an array of sequence names with MIN_VALUE, MAX_VALUE, INCREMENT and LAST_NUMBER for the specified table.

_get_views¶

This function implements an Oracle-native views information.

Returns a hash of view names with the SQL queries they are based on.

_alias_info¶

This function implements an Oracle-native column information.

Returns a list of array references containing the following information for each alias of the specified view:

[(
column name,
column id )]

_get_triggers¶

This function implements an Oracle-native triggers information.

Returns an array of refarray of all triggers information.

_get_functions¶

This function implements an Oracle-native functions information.

Returns a hash of all function names with their PLSQL code.

_get_procedures¶

This procedure implements an Oracle-native procedures information.

Returns a hash of all procedure names with their PLSQL code.

_get_packages¶

This function implements an Oracle-native packages information.

Returns a hash of all package names with their PLSQL code.

_get_types¶

This function implements an Oracle custom types information.

Returns a hash of all type names with their code.

_table_info¶

This function retrieves all Oracle-native tables information.

Returns a handle to a DB query statement.

_get_tablespaces¶

This function implements an Oracle-native tablespaces information.

Returns a hash of an array of tablespace names with their system file path.

_get_partitions¶

This function implements an Oracle-native partitions information. Return two hash ref with partition details and partition default.

dump¶

This function dump data to the right output (gzip file, file or stdout).

read_config¶

This function read the specified configuration file.

_convert_package¶

This function is used to rewrite Oracle PACKAGE code to PostgreSQL SCHEMA. Called only if PLSQL_PGSQL configuration directive is set to 1.

_restore_comments¶

This function is used to restore comments into SQL code previously remove for easy parsing

_remove_comments¶

This function is used to remove comments from SQL code to allow easy parsing

_convert_function¶

This function is used to rewrite Oracle FUNCTION code to PostgreSQL. Called only if PLSQL_PGSQL configuration directive is set to 1.

_convert_declare¶

This function is used to rewrite Oracle FUNCTION declaration code to PostgreSQL. Called only if PLSQL_PGSQL configuration directive is set to 1.

_format_view¶

This function is used to rewrite Oracle VIEW declaration code to PostgreSQL.

randpattern¶

This function is used to replace the use of perl module String::Random and is simply a cut & paste from this module.

logit¶

This function log information to STDOUT or to a logfile following a debug level. If critical is set, it dies after writing to log.

_convert_type¶

This function is used to rewrite Oracle PACKAGE code to PostgreSQL SCHEMA. Called only if PLSQL_PGSQL configuration directive is set to 1.

escape_bytea¶

This function return an escaped bytea entry for Pg.

_show_infos¶

This function display a list of schema, table or column only to stdout.

_schema_list¶

This function retrieves all Oracle-native user schema.

Returns a handle to a DB query statement.

_get_encoding¶

This function retrieves the Oracle database encoding

Returns a handle to a DB query statement.

_compile_schema¶

This function force Oracle database to compile a schema and validate or invalidate PL/SQL code