Version 19.1 of Ora2Pg, a free and reliable tool used to migrate an Oracle database to PostgreSQL, has been officially released and is publicly available for download.
This release fix several issues reported during the last month and add support to PostgreSQL 11 HASH partitioning.
It also adds some new features and configuration directives:
* Add export of default partition and default sub partition. * Add export of HASH partition type. * Add support of stored procedure object. * Add replacement of NLSORT in indexes or queries. For example: CREATE INDEX test_idx ON emp (NLSSORT(emp_name, 'NLS_SORT=GERMAN')); is translated into CREATE INDEX test_idx ON emp ((emp_name collate "german")); The collation still need to be adapted, here probably "de_DE". NLSSORT() in ORDER BY clause are also translated. * Prevent duplicate index with primary key on partition to be exported. * PostgreSQL native partitioning does not allow direct import of data into already attached partitions. We now force direct import into main table but we keep Oracle export of data from individual
This release also adds two new command line options:
* --oracle_speed: use to know at which speed Oracle is able to send data. No data will be processed or written written * --ora2pg_speed: use to know at which speed Ora2Pg is able to send transformed data. Nothing will be written
Use it for debugging purpose. They are useful to see Oracle speed to send data and at what speed Ora2Pg is processing the data without reaching disk or direct import into PostgreSQL.
Two new configuration directive has been added:
* PG_SUPPORTS_PROCEDURE : PostgreSQL v11 adds support to stored procedure objects. Disabled by default. * PARALLEL_MIN_ROWS: set the minimum number of tuples in a table before calling Oracle's parallel mode during data export. Default to 100000 rows.
Note that PG_SUPPORTS_PARTITION and PG_SUPPORTS_IDENTITY are now enabled by default to use PostgreSQL declarative partionning and identity column instead of serial data type.
For a complete list of change see https://github.com/darold/ora2pg/blob/master/changelog
This release fix several issues reported by users during last year. It also adds several new features and configuration directives.
- Add export of Oracle HASH partitioning when PG_SUPPORTS_PARTITION is enabled. This is a PostgreSQL 11 feature. - Add SUBTYPE translation into DOMAIN with TYPE and PACKAGE export. - Add automatic translation of KEEP (DENSE_RANK FIRST|LAST ORDER BY ...) OVER (PARTITION BY ...) into FIRST|LAST_VALUE(...) OVER (PARTITION BY ... ORDER BY ...). - Add PCTFREE to FILLFACTOR conversion when PCTFREE is upper than the default value: 10. - Replace DELETE clause not followed with FROM (optional in Oracle). - Remove Oracle extra clauses in TRUNCATE command. - Allow use of NUMBER(*) in DATA_TYPE directive to convert all NUMBER(*) into the given type whatever is the length. Ex: DATA_TYPE NUMBER(*):bigint. - Add a PARALLEL hint to all Oracle queries used to migrate data. - Add export of Identity Columns from Oracle Database 12c. - Add translation of UROWID datatype and information in documentation about why default corresponding type OID will fail at data import. - Remove unwanted and unused keywords from CREATE TABLE statements: PARALLEL and COMPRESS. - Remove TEMPORARY in DROP statements. - Improve speed of escape_copy() function used for data export. - Add translation of Oracle functions NUMTOYMINTERVAL() and NUMTODSINTERVAL(). - Add counting of jobs defined in Oracle scheduler in the migration assessment feature. - Add CSMIG in the list of Oracle default system schema - Fully rewrite data export for table with nested user defined types DBD::Oracle fetchall_arrayref() is not able to associate complex custom types to the returned arrays, changed this call to use fetchrow_array() also used to export BLOB. - QUERY export will now output translated queries as well as untranslated ones. This break backward compatibility, previously only translated query was dumped. - Auto detect UTF-8 input files to automatically use utf8 encoding. - Support translation of MySQL global variables. - Add translation of preprocessor in Oracle external table into program in foreign table definition. Allow translation of external table from file. - Add translation to NVL2() Oracle function. - Translate CONVERT() MySQL function. - Translate some form of GROUP_CONCAT() that was not translated. - Remove call to CHARSET in cast() function, replace it by COLLATE every where else. This must cover most of the cases but some specials use might not, so please reports any issue with this behavior. - Add -c | --config command line option to ora2pg_scanner to set custom configuration file to be used instead of ora2pg default: /etc/ora2pg/ora2pg.conf - Improve CONNECT BY and OUTER JOIN translation. - And lot of MySQL to PostgreSQL improvements.
Several new configuration directives have been added:
- Add DEFAULT_PARALLELISM_DEGREE to control PARALLEL hint use when exporting data from Oracle. Default is disabled. - Make documentation about KEEP_PKEY_NAMES more explicit about kind of constraints affected by this directive. - Add PG_SUPPORTS_IDENTITY configuration directive to enable export of Oracle identity columns into PostgreSQL 10 feature. If PG_SUPPORTS_IDENTITY is disabled and there is IDENTITY column in the Oracle table, they are exported as serial or bigserial columns. When it is enabled they are exported as IDENTITY columns like: CREATE TABLE identity_test_tab ( id bigint GENERATED ALWAYS AS IDENTITY, description varchar(30) ) ; If there is non default sequence option set in Oracle, they will be appended after the IDENTITY keyword. Additionally in both cases Ora2Pg will create a file AUTOINCREMENT_output.sql with a function to update the associated sequences with the restart value set to "SELECT max(colname)+1 FROM tablename". Of course this file must be imported after data import otherwise sequence will be kept to start value. - Add DISABLE_UNLOGGED configuration directive. By default Ora2Pg export Oracle tables with the NOLOGGING attribute into UNLOGGED tables. You may want to fully disable this feature because you will lost all data from unlogged table in case of PostgreSQL crash. Set it to 1 to export all tables as normal table. When creating a new migration project using --init_project, this directive is activated by default. This is not the case in the default configuration file for backward compatibility. - Add FORCE_SECURITY_INVOKER configuration directive. Ora2Pg use the function's security privileges set in Oracle and it is often defined as SECURITY DEFINER. To override those security privileges for all functions and use SECURITY DEFINER instead, enable this directive. - Add AUTONOMOUS_TRANSACTION in configuration to enable translation of autonomous transactions into a wrapper function using dblink or pg_background extension. If you don't want to use this feature and just want to export the function as a normal one without the pragma call, disable this directive. - Add documentation about COMMENT_SAVEPOINT configuration directive. - Major rewrite in PACKAGE parser to better support global variables detection. Global variable that have no default values are now always initialized to empty string in file global_variables.conf so that we see that they exists. This might not change the global behavior.
I especially want to thank Pavel Stehule and Eric Delanoe who spent lot of time this year to help me to improve the PL/SQL to plpgsql translation and also Krasiyan Andreev who help a lot to finalize the MySQL to PostgreSQL migration features.
See Release note for full list of changes.
This release fix several issues reported during the last six months. It also adds lot of new features and configuration directives:
- Lot of improvement in (+) translation even if there is still some case not or wrongly covered. - Add translation of SUBSTRB into substr. - Allow use of array in MODIFY_TYPE to export Oracle user defined type that are just array of some data type. For example: CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15); can be directly translated into text or varchar. In this case use the directive as follow: MODIFY_TYPE CLUB:MEMBERS:text Ora2Pg will take care to transform all data of this column into the correct format. Only arrays of characters and numerics types are supported. - Add translation of Oracle function LISTAGG() into string_agg(). - Add TEST_VIEW action to perform a simple count of rows returned by views on both database. - Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT and add translation of SQL%FOUND. - Add translation of column in trigger event test with IS DISTINCT, for example: IF updating('ID') THEN ... will be translated into: IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then... - Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein. - Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL will respect the new list of columns and all indexes or foreign key pointing to or from a column removed will not be exported. - Add export of partition and subpartition using PostgreSQL native partitioning. - Auto detect encrypted columns and report them into the assessment. SHOW_COLUMN will also mark columns as encrypted. - Add information to global temporary tables in migration assessment. - Add experimental DATADIFF functionality. - Allow use of multiprocess with -j option or JOBS to FUNCTION and PROCEDURE export. Useful if you have thousands of these objects. - Force RAW(N) type with default value set to sys_guid() as UUID on PostgreSQL. - Replace function with out parameter using select into. For example a call to: get_item_attr( attr_name, p_value ); where p_value is an INOUT parameter, will be rewritten as p_value := get_item_attr( attr_name, p_value ); If there is multiple OUT parameters, Ora2Pg will use syntax: SELECT get_item_attr( attr_name, p_value ) INTO (attr_name, p_value); - Add translation of CONNECT BY using PostgreSQL CTE equivalent. This translation also include a replacement of LEVEL and SYS_CONNECT_BY_PATH native Oracle features. On complex queries there could still be manual editing but all the main work is done. - Add support to user defined exception, errcode affected to each custom exception start from 50001. - Translate call to to_char() with a single parameter into a cast to varchar. Can be disabled using USE_ORAFCE directive. - Improve ora2pg_scanner to automatically generates migration assessment reports for all schema on an Oracle instance. Before the schema name to audit was mandatory, now, when the schema is not set Ora2Pg will scan all schema. The connexion user need to have DBA privilege. Ora2Pg will also add the hostname and SID as prefix in the filename of the report. This last changee forbids ora2pg_scanner to overwrite a report if the same schema name is found in several databases.
Several new configuration directives have been added:
- Add USE_ORAFCE configuration directive that can be enabled if you want to use functions defined in the Orafce library and prevent Ora2Pg to translate call to these functions. The Orafce library can be found here: https://github.com/orafce/orafce By default Ora2pg rewrite add_month(), add_year(), date_trunc() and to_char() functions, but you may prefer to use the Orafce functions that do not need any code transformation. Directive DATE_FUNCTION_REWRITE has been removed as it was also used to disable replacement of add_month(), add_year() and date_trunc() when Orafce is used, useless now. - Add FILE_PER_FKEYS configuration directive to allow foreign key declaration to be saved in a separate file during schema export. By default foreign keys are exported into the main output file or in the CONSTRAINT_output.sql file. If enabled foreign keys will be exported into a file named FKEYS_output.sql - Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to force the user to review the logic of the function. Once it is fixed in Oracle source code or you want to comment this calls enable the directive. - Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg use CREATE OR REPLACE in function DDL, if you need not to override existing functions disable this configuration directive, DDL will not include OR REPLACE. - Add FUNCTION_CHECK configuration directive. Disable this directive if you want to disable check_function_bodies. SET check_function_bodies = false; It disables validation of the function body string during CREATE FUNCTION. Default is to use de postgresql.conf setting that enable it by default. - Add PG_SUPPORTS_PARTITION directive, disabled by default. PostgreSQL version prior to 10.0 do not have native partitioning. Enable this directive if you want to use PostgreSQL declarative partitioning instead of the old style check constraint and trigger. - Add PG_SUPPORTS_SUBSTR configuration directive to replace substr() call with substring() on old PostgreSQL versions or some fork like Redshift. - Add PG_INITIAL_COMMAND to send some statements at session startup. This directive is the equivalent used for Oracle connection, ORA_INITIAL_COMMAND. Both can now be used multiple time now. - Add DBLINK_CONN configuration directive. By default if you have an autonomous transaction translated using dblink extension the connection is defined using the values set with PG_DSN, PG_USER and PG_PWD. If you want to fully override the connection string use this directive to set the connection in the autonomous transaction wrapper function. For example: DBLINK_CONN port=5432 dbname=pgdb host=localhost user=pguser password=pgpass - Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace all string constant during the pl/sql to plpgsql translation, string constant are all text include between single quote. If you have some string placeholder used in dynamic call to queries you can set a list of regexp to be temporary replaced to not break the parser. For example: STRING_CONSTANT_REGEXP
The list of regexp must use the semi colon as separator. - Add FUNCTION_STABLE configuration directive. By default Oracle functions are marked as STABLE as they can not modify data unless when used in PL/SQL with variable assignment or as conditional expression. You can force Ora2Pg to create these function as VOLATILE by disabling this configuration directive. - Add new TO_NUMBER_CONVERSION configuration directive to control TO_NUMBER translation behavior. By default Oracle call to function TO_NUMBER will be translated as a cast into numeric. For example, TO_NUMBER('10.1234') is converted into PostgreSQL call: to_number('10.1234')::numeric. If you want you can cast the call to integer or bigint by changing the value of the configuration directive. If you need better control of the format, just set it as value, for example: TO_NUMBER_CONVERSION 99999999999999999999D9999999999 will convert the code above as: TO_NUMBER('10.1234', '99999999999999999999D9999999999') Any value of the directive that it is not numeric, integer or bigint will be taken as a mask format. If set to none, then no conversion will be done. - Add LOOK_FORWARD_FUNCTION configuration directive which takes a list of schema to get functions/procedures meta information that are used in the current schema export. When replacing call to function with OUT or INOUT parameters, if a function is declared in an other package then the function call rewriting can not be done because Ora2Pg only knows about functions declared in the current schema. By setting a comma separated list of schema as value of the directive, Ora2Pg will look forward in these packages for all functions, procedures and packages declaration before proceeding to current schema export. - Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the PL/SQL operator used in named parameter => with the PostgreSQL proprietary operator := Disable this directive if you are using PG < 9.5 - Add a warning when Ora2Pg reorder the parameters of a function following the PostgreSQL rule that all input parameters following a parameter with a default value must have default values as well. In this case, Ora2Pg extracts all parameters with default values and put them at end of the parameter list. This is to warn you that a manual rewrite is required on calls to this function.
New command line options have been added:
- Add -N | --pg_schema command line option to be able to override the PG_SCHEMA configuration directive. When this option is set at command line, EXPORT_SCHEMA is automatically activated. - Add --no_header option with equivalent NO_HEADER configuration directive to output the Ora2Pg header but just the translated code.
There is also some behavior changes from previous release:
- Remove SysTimestamp() from the list of not translated function, it is replaced with CURRENT_TIMESTAMP for a long time now. - Change migration assessment cost to 84 units (1 day) for type TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic. - Set minimum assessment unit to 1 when an object exists. Improve PL/SQL code translation speed. - Change behavior of COMPILE_SCHEMA directive used to force Oracle to compile schema before exporting code. When this directive is enabled and SCHEMA is set to a specific schema name, only invalid objects in this schema will be recompiled. When SCHEMA is not set then all schema will be recompiled. To force recompile invalid object in a specific schema, set COMPILE_SCHEMA to the schema name you want to recompile. This will ask to Oracle to validate the PL/SQL that could have been invalidate after a export/import for example. The 'VALID' or 'INVALID' status applies to functions, procedures, packages and user defined types. - Default transaction isolation level is now set to READ COMMITTED for all action excluding data export. - Oracle doesn't allow the use of lookahead expression but you may want to exclude some objects that match the ALLOW regexp you have defined. For example if you want to export all table starting with E but not those starting with EXP it is not possible to do that in a single expression. Now you can start a regular expression with the ! character to exclude all objects matching the regexp given just after. Our previous example can be written as follow: ALLOW E.* !EXP.* it will be translated into REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$') in the object search expression. - Fix quoting of PG_SCHEMA with multiple schema in search path. The definition of the search path now follow the following behavior: * when PG_SCHEMA is define, always set search_path to its value. * when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path is set the name of the schema. - Remove forcing of export_schema when pg_schema is set at command line. This could change the behavior of some previous use of these variables and the resulting value of the search_path but it seems much better understandable. - Rewrite translation of raise_application_error to use RAISE EXCEPTION with a message and the SQLSTATE code. Oracle user defined code -20000 to -20999 are translated to PostgreSQL user define code from 45000 to 45999. Call to raise_application_error(mySQLCODE, myErrmsg); will be translated into RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE; - Remove migration assessment cost for TG_OP and NOT_FOUND they might be fully covered now.
See Release note for full list of changes.
This release fix several issues reported on outer join translation thanks to the help of Pavel Stehule and reapply the commit on virtual column export that was accidentally removed from v18.1. It also adds several new features:
- Remove CHECK constraints for columns converted into boolean using REPLACE_AS_BOOLEAN column. - Oracle function are now marked as stable by default as they can not modify data.
Two new configuration directives have been added:
- DATE_FUNCTION_REWRITE: by default Ora2pg rewrite add_month(), add_year() and date_trunc() functions set it to 0 to force Ora2Pg to not translate those functions if translated code is broken. - GRANT_OBJECT: when exporting GRANT you can now specify a comma separated list of objects which privileges must be exported. Default is to export privileges for all objects. For example set it to TABLE if you just want to export privilege on tables.
and a new command line option:
- Add -g | --grant_object command line option to ora2pg to be able to extract privilege from the given object type. See possible values with GRANT_OBJECT configuration directive.
Here is the complete list of changes:
- Remove empty output.sql file in current directory with direct data import. Thanks to kuzmaka for the report. - Fix shell replacement of $$ in function definition in Makefile.PL embedded configuration file. Thanks to kuzmaka for the report. - Fix shell replacement of backslash in Makefile.PL embedded configuration file. Thanks to kuzmaka for the report. - Add warning level to virtual column notice. - Fix comment in where clause breaking the outer join association. Thanks to Pavel Stehule for the report. - Add parsing and support of virtual column from DDL file. - Reapply commit on virtual column export that was accidentally removed in commit d5866c9. Thanks to Alexey for the report. - Fix mix of inner join and outer join not translated correctly. Thanks to Pavel Stehule for the help to solve this issue. - Fix additional comma in column DEFAULT value from DDL input file. Thanks to Cynthia Shang for the report. - Fix comments inside FROM clause breaking translation to ANSI outer joins. Thanks to Pavel Stehule for the report. - Fix replacement of sdo_geometry type into function. Thanks to Saber Chaabane for the report. - Fix subquery in outer join clause. Thanks to Saber Chaabane for the report. - Fix duplicated subqueries placeholder in the from clause. Thanks to Saber Chaabane for the report. - Fix replacement of subquery place older during outer join rewrite. Thanks to Saber Chaabane for the report. - Add DATE_FUNCTION_REWRITE configuration directive. By default Ora2pg rewrite add_month(), add_year() and date_trunc() functions set it to 0 to force Ora2Pg to not translate those functions if translated code is broken. Thanks to Pavel Stehule for the feature request. - Do not report error when -g is used but action is not GRANT. Thanks to Shane Jimmerson for the report. - Oracle function can not modify data, only procedure can do that, so mark them as stable. Thanks to Pavel Stehule for the report. - Missed some obvious combination like upper/lower case or no space after AND/OR on outer join parsing and some other issues. - Add missing call to extract_subqueries() recursively. Thanks to Pavel Stehule for the report. - Add full support of outer join translation in sub queries. - Add translation of mixed inner join and Oracle outer join. Thanks to Pavel Stehule for the report. - Fix missing space between keyword AS and END from the decode() transformation. Thanks to Pavel Stehule for the report. - Fix parsing of outer join with UNION and translation to left join. Thanks to Pavel Stehule for the report. - Remove CHECK constraints for columns converted into boolean using REPLACE_AS_BOOLEAN column. Thanks to Shane Jimmerson for the feature request. - Fix regression on SQL and PLSQL rewrite when a text constant contained a semi-comma. - Add the GRANT_OBJECT configuration directive. When exporting GRANT you can specify a comma separated list of objects for which the privileges will be exported. Default is export for all objects. Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM and DIRECTORY. Only one object type is allowed at a time. For example set it to TABLE if you just want to export privilege on tables. You can use the -g option to overwrite it. When used this directive prevent the export of users unless it is set to USER. In this case only users definitions are exported. - Add the -g | --grant_object command line option to ora2pg to be able to extract privilege from the given object type. See possible values with GRANT_OBJECT configuration directive. - Improve replacement of ROWNUM by LIMIT+OFFSET clause. - Fix extra semi-colon at end of statement. - Override ora2pg.spec with Devrim's one but with String::Random removing as it is no more used.
This new major release adds several new useful features and lot of improvements.
* Automatic rewrite of simple form of (+) outer join Oracle's syntax. This major feature makes Ora2Pg become the first free tool that is able to rewrite automatically (+) outer join in command line mode. This works with simple form of outer join but this is a beginning. * Add export of Oracle's virtual column using a real column and a trigger. * Allow conversion of RAW/CHAR/VARCHAR2 type with precision in DATA_TYPE directive. Useful for example to transform all RAW(32) or VARCHAR2(32) columns into PostgreSQL special type uuid. * Add export NOT VALIDATED state from Oracle foreign keys and check constraints into NOT VALID constraints in PostgreSQL. * Replace call to SYS_GUID() with uuid_generate_v4() by default. * Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;". * Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function calls and other PL/SQL to plpgsql replacement. There should not be any limitation in rewriting when a function contains a sub query or an other function call inside his parameters. * Refactoring of ora2pg to not requires any dependency other than the Perl DBI module by default. All DBD drivers are now optionals and ora2pg will expect an Oracle DDL file as input by default. * Add export of Oracle's global variables defined in package. They are exported as user defined custom variables and available in a session. If the variable is a constant or have a default value assigned at declaration, ora2pg will create a new file with the declaration (global_variables.conf) to be included in the main configuration file postgresql.conf file. * Create full text search configuration when USE_UNACCENT directive is enabled using the auto detected stemmer or the one defined in FTS_CONFIG. For example: CREATE TEXT SEARCH CONFIGURATION fr (COPY = french); ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem; CREATE INDEX place_notes_cidx ON places USING gin(to_tsvector('fr', place_notes));
Changes and incompatibilities from previous release:
* FTS_INDEX_ONLY is now enabled by default because the addition of a column is not always possible and not always necessary where a simple function-based index is enough. * Remove use to setweigth() on single column FTS based indexes. * Change default behaviour of Ora2Pg in Full Text Search index export.
A new command line option and some configuration directive have been added:
* Option -D | --data_type to allow custom data type replacement at command line like in configuration file with DATA_TYPE. * UUID_FUNCTION to be able to redefined the function called to replace SYS_GUID() Oracle function. Default to uuid_generate_v4. * REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle native syntax (+) into OUTER JOIN if rewritten code is broken. * USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to use the unaccent extension with pg_trgm with the FTS indexes. * FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column with a dedicated triggers for FTS indexes. Enable this directive if you just want a function-based index like: CREATE INDEX ON t_document USING gin(to_tsvector('pg_catalog.english', title)); * FTS_CONFIG, use this directive to force the text search stemmer used with the to_tsvector() function. Default is to auto detect the Oracle FTS stemmer. For example, setting FTS_CONFIG to pg_catalog.english or pg_catalog.french will override the auto detected stemmer.See changelog for the complete list changes.
This release adds several new features:
* Adds export of Oracle Text Indexes into FTS or pg_trgm based indexes. * Add export of indexes defined on materialized views. * Allow export of materialized views as foreign tables when export type is FDW. * Add replacement of trim() by btrim().
Two new configuration directives have been added:
* USE_INDEX_OPCLASS: when value is set to 1, this will force Ora2Pg to export all indexes defined on varchar2() and char() columns using *_pattern_ops operators. If you set it to a value greater than 1 it will only change indexes on columns where the character limit is greater or equal than this value. * CONTEXT_AS_TRGM: when enabled it forces Ora2Pg to translate Oracle Text indexes into PostgreSQL indexes using pg_trgm extension. Default is to translate CONTEXT indexes into FTS indexes and CTXCAT indexes using pg_trgm. Some time using pg_trgm based indexes is enough.
There's also some fix of issues reported by users, here is the complete list of changes:
- Fixed non-use of custom temp_dir (-T). Thanks to Sebastian Albert for the patch. - Make export of FTS indexes from materialized view work as for tables. - Fix drop of indexes during export of data when DROP_INDEXES is enabled. - Remove double quote in function and procedure name from an input file to avoid creating a file with double quote in its name. - Fix export of unique index associated to a primary key. - Move OPTION (key "yes") of FDW table before NOT NUL constraint and default clause. - Fix some encoding issue during data export into file. - Rename FTS indexes prefix output file into FTS_INDEXES and export CTXCAT Oracle indexes as GIN pg_trgm indexes instead of FTS indexes. - Add export of indexes of type CTXCAT as FTS indexes. - Export triggers and update order for FTS indexes to separate file prefixed with FTS_INDEXES. - Exclude from export synonyms starting with a slash that correspond to incomplete deleted synonyms. Thanks to Nouredine Mallem for the report. - Add export of indexes defined on materialized views. Thanks to Nouredine Mallem for the report. - Fix export of foreign key and FTS indexes when looking at dba_* tables and multiple different schemas have the same fk or context indexes definition. Thanks to Nouredine Mallemfor the patch. - Fix export of CONTEXT or FULLTEXT Oracle index into PostgreSQL FTS with trigger and initial update statement. - Add configuration directive USE_INDEX_OPCLASS to force Ora2Pg to export all indexes defined on varchar2() and char() columns using those operators. A value greater than 1 will only change indexes on columns where the character limit is greater or equal than this value. - Fix FDW export of mysql tables. Thanks to yafeishi for the report. - Fix decode() rewrite. Thanks to Jean-Yves Julliot for the report. - Fix regression introduced into the export of NUMBER to integer like PG types. - Show partition name in progress bar instead of main table name.
This is a maintenance release to fix several issues reported by users. There is also some major improvements and new features.
There is a new configuration directive or change default behavior:
* Fix export of CLOBs and NCLOB that was truncated to 64 Kb. * PG_BACKGROUND : when enabled autonomous transactions will be built using Robert Haas extension pg_background instead of dblink. Default is to still used dblink as pg_background is available only for PostgreSQL >= 9.5. * All Perl I/O now use the open pragma instead of calling method binmode(). This will force input and output to utf8 using the Perl pragma: use open ':encoding(utf8)'; when configuration directive BINMODE is not set or NLS_LANG is set to UTF8. * Ora2Pg will now export empty lob as empty string instead of NULL when the source column has NOT NULL constraint and that directive EMPTY_LOB_NULL is not activated. * Improve and fix progress bar especially when using JOBS/-J option. * Allow LOAD action to apply all settings defined in the input file on each opened session, this allow to use LOAD with export schema enabled. If settings are not set in the input file encoding and search_path is set from the ora2pg configuration settings. * NUMBER(*,0) is now exported as numeric(38) as well as a NUMBER with DATA_SCALE set to 0, no DATA_PRECISION and a DATA_LENGTH of 22. The last correspond to Oracle type INTEGER or INT. * Allow conversion of type with precision in DATA_TYPE directive. For example it is possible to transform all NUMBER(12,2) only into numeric(12,2) by escaping the comma. Example: DATA_TYPE NUMBER(12\,2):numeric(12\,2);... * Write data exported into temporary files (prefixed by tmp_) and renamed them at end of the export to be able to detect incomplete export and override it at next export. * Add export of type created in package declaration. * Export foreign key when the referenced table is not in the same schema. * Enabled by default PG_SUPPORTS_CHECKOPTION assuming that your Pg destination database is at least a 9.4 version. * Add 12 units to migration assessment report per table/column conflicting with a reserved word in PostgreSQL to reflect the need of code rewriting. * Output a warning when a column has the same name than a system column (xmin,xmax,ctid,etc.) * Replace SYSDATE by a call to clock_timestamp() instead of a call to LOCALTIMESTAMP in plpgsql code. * Add missing documentation about DISABLE_PARTITION directive used to not reproduce partitioning into PostgreSQL and only export partitioned data into the main table.
Here is the complete list of other changes:
- Fix broken parallel table export (-P option). - Fix export of CLOBs and NCLOB that was truncated to 64Kb. Thanks to Paul Mzko for the patch. - Fix database handle in error report. - Fix use of wrong database handle to set search_path. Thanks to Paul Mzko for the report. - Ora2pg doesn't export schema ForeignKey constraint when connected as different DBA user. Thanks to Paul Mzko for the patch. - Fix Perl I/O encoding using open pragma instead of calling method binmode(). Thanks to Gary Evans for the report. - Force input to utf8 using Perl pragma: use open ':encoding(utf8)'; when BINMODE is not set or NLS_LANG is UTF8. - Force ora2pg to export empty lob as empty string instead of NULL when the source column has a NOT NULL constraint and directive EMPTY_LOB_NULL is not activated. Thanks to Valeriy for the report. - Fix missing CASCADE attribute on fkey creation during data export when DROP_FKEY was enabled. Thanks to ilya makarov for the report. - Fix issue on converting NUMBER(*,0) to numeric, should be ported to numeric(38). Thanks to ilya makarov for the report. - Correct query for ForeignKey export from oracle. Thanks to ilya makarov for the patch. - Fix schema change in direct import of data to PostgreSQL. - Change query for foreign key extraction to keep the column order. Thanks to ilya makarov for the report. - Write data exported into temporary files (prefixed by tmp_) and renamed them at end of the export to be able to detect incomplete export and override it at next export. Thanks to Paul Mkzo for the feature request. - Fix infinite loop in blob extraction when error ORA-25408 occurs during ora_lob_read() call. Thanks to Paul Mzko for the report. - Fix order of columns in foreign keys definition. Thanks to ilya makarov for the report. - Fix export of partition by range on multicolumn. Thanks to Rupesh Admane for the report. - Update reserved keywords list. Thanks to Nicolas Gollet for the report. - Add ON DELETE NO ACTION on foreign key creation (DROP_FKEY) to obtain the same output than during constraints export. - Fix export of foreign key that was duplicating the columns in both part, local and foreign. Thanks to ilya makarov for the report. - Remove call to to_char(datecol, format) when exporting date and timestamp. This formating is no more needed as we are now forcing NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT when opening a connection to Oracle using: ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS and ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS This may result on some speed improvment during data export. - Fix parsing of packages from input file. - Add export of type created in package declaration. Thanks to dezdechado for the report. - Fix converting of procedures with out arguments. Thanks to dezdechado for the report. - Update documentation about project management. - Fix replacement of = NULL by IS NULL in update statement. Thanks to dezdechado for the report. - Fix parsing of trigger from file that was broken and new line removed. Thanks to dezdechado for the report. - Fix erasing of quotes from text in triggers. Thanks to dezdechado for the report. - Fix "return new" on trigger function when there is exception. Thanks to dezdechado for the report and solution. - Fix conversion of INTEGER and INT into numeric(38) instead of numeric without precision. Thanks to dezdechado for the report. - Fix export of foreign key when the referenced table is not in the same schema. Thanks to Juju for the report. - Fix ddl create schema when EXPORT_SCHEMA and CREATE_SCHEMA are enabled but no schema is specified. - Fix export of NCHAR that was converted as char but was loosing its length definition. Thanks to lgerlandsen for the report. - Fix parsing of views using WITH statements. Thank to dezdechado for the report. - Fix case that breaks views/triggers definition when a semicolon is encountered in a string value when reading definition from file. Thanks to dezdechado for the report. - Fix included/excluded of sequences when using ALLOW/EXCLUDE directives. Thanks to Roman Sindelar for the report. - prepare options modified with some escaping improvements. Thanks to ioxgrey for the patch. - It seems that for a NUMBER with a DATA_SCALE set to 0, no DATA_PRECISION and a DATA_LENGTH of 22 in ALL_TAB_COLUMNS, Oracle use a NUMBER(38) instead. This correspond to Oracle type INTEGER or INT. I don't really understand the reason of this behavior, why not just using a data length of 38? ALL_TAB_COLUMNS and Ora2Pg reports a data length of 22, now Ora2Pg will report NUMBER(38) like the resulting type of the DESC command. The following Oracle table: CREATE TABLE TEST_TABLE ( FIELD_1 INTEGER, FIELD_2 NUMBER ); will be exported as follow by Ora2Pg:  TABLE TEST_TABLE (owner: HR, 0 rows) FIELD_1 : NUMBER(38) => numeric FIELD_2 : NUMBER(22) => bigint Oracle data type INTEGER and INT will be exported as numeric by default instead of an integer. - Fix parsing of function/procedure from file with comments after BEGIN statement. - Remove DEFERRABLE INITIALLY DEFERRED from CHECK constraints when parsed from file. Thanks to Felipe Lavoura for the report. - Fix double parenthesis in index definition when parsing index creation from file. Thanks to Felipe Lavoura for the report. - Fix parsing of COMMENT from file. - Fix undetected native Oracle type bug. Thanks to kvnema for the report. - Fix unwanted text formatting with bind value in INSERT action with direct import to PostgreSQL. Thanks to Oleg for the report. - Fix inversion of UPDATE_RULE and DELETE_RULE in foreign key creation for MySQL export. Thanks to Sebastian Albert for the report. - Update documentation about DEFER_FKEY and DROP_FKEY to report the new behavior. - Remove call to SET CONSTRAINTS ALL DEFERRED with direct import. - Fix use of NULL value in bind parameter that should be undefined (INSERT export mode only). Thanks to Oleg barabaka for the report. - Remove replacement of direct call to functions with PERFORM, there is too much false positive. Thanks to dezdechado for the reports. - Fix a typo in SYSDATE replacement. Thank to dezdechado for report. - Remove rewrite of concatenation in RAISE EXCEPTION. Thanks to dezdechado for the report. - Fix replacement of raise_application_error() when first argument is a variable. Thanks to dezdechado for the report. - Fix wrong insertion of PERFORM before some function calls. Thanks to dezdechado for the report. - Replace SYSDATE by a call to clock_timestamp() instead of call to LOCALTIMESTAMP in plpgsql code. Thanks to aleksaan for the report. - Allow use of comma for object name list separator instead of space as workaround on Window OS. - Fix documentation about MODIFY_TYPE. Thanks to Nicolas Gollet for the report. - Add missing documentation about DISABLE_PARTITION directive used to not reproduce partitioning into PostgreSQL and only export partitioned data into the main table. Thanks to Nicolas Gollet for the report. - Add information about how to export LONG RAW data type. They need to be exported as BLOB before into Oracle to be exported as BYTEA. - Fix case where select was wrongly replaced by perform in INSERT INTO with SELECT statement. Thanks to dezdechado for the report. - Fix links to ora2pg presentation. Thanks to Daniel Lenski for the patch. - Fix input parameters after one with a default value must also have defaults. Thanks to v.agapov fot the patch. - Fix debug mode that was interromping the last running table dump. Thanks to calbiston for the report.
This is a maintenance release to fix several issues reported by users. There is also some major data export speed improvement thanks to the work of PostgreSQL Pro and a new RPM spec file provided by Devrim Gündüz to be able to build RPM package for Ora2Pg.
There is a new configuration directive:
- EMPTY_LOB_NULL: when enabled force empty_clob() and empty_blob() to be exported as NULL instead as empty string.
Here is the complete list of other changes:
- Add EMPTY_LOB_NULL directive to force empty_clob() and empty_blob() to be exported as NULL instead as empty string. This might improve data export speed if you have lot of empty lob. Thanks to Alex Ignatov for the report. - Fix import_all.sh script to import grant and tablespace separately as postgres user and just after indexes and constraints creation. - Add parsing of tablespace from "alter table ... add constraint" with DDL input file. Thanks to Felipe Lavoura. - Remove --single-transaction in import_all.sh script with TABLESPACE import. Thanks to Guillaume Lelarge for the report. - Fix Makefile.PL to used with latest spec file from Devrim Gündüz and following the advice of calbiston. - Update spec file to v17.4 and latest change to Makefile.PL - Replace ora2pg.spec by postgressql.org spec file by Devrim Gunduz. - Generate man page to avoids rpmbuild error. - Fix Windows install. Thanks to Lorena Figueredo for the report. - Remove "deferrability" call for mysql foreign keys. Thanks to Jean-Eric Cuendet for the report. - Fix issue in restoring foreign key for mysql data export. Thanks to Jean-Eric Cuendet for the report. - Remove connection test to PostgreSQL instance as postgres or any superuser in import_all.sh - Fix creation of configuration directory. - Fix Makefile to dissociate CONFDIR and DOCDIR from PREFIX or DESTDIR. Thanks to Stephane Schildknecht for the report. - Fix date_trunc+add_month replacement issue. Thanks to Lorena Figueredo for the report. - Do not replace configuration directory in scripts/ora2pg if this is a RPM build. Thanks to calbiston for the report. - Return empty bytea when a LOB is empty and not NULL. - Regular expressions and conditions checks improvement in method format_data_type() to make it a bit faster on huge tables. Thanks to Svetlana Shorina for the patch. - Fix INSERT using on the fly data import with boolean values. Thanks to jecuendet for the report. - Allow MySQL data type to be converted into boolean. Thanks to jecuendet for the report. - Fix export of BIT mysql data type into bit bit varying. Thanks to jecuendet for the report. - Fix call to escape_copy/escape_insert function call.
This release fix two regressions introduced in latest release.
* Fix major bug in data export. Thanks to Frederic Guiet for the report. * Fix another regression with character data that was not escaped. Thanks to Frederic Guiet for the report.
This is a maintenance release to fix several issues reported in new LOB extraction method. There is also some feature improvement:
* Allow NUMBER with precision to be replaced as boolean. * Allow full relocation of Ora2Pg installation using for example: perl Makefile.PL DESTDIR=/opt/ora2pg
Here is the complete list of other changes:
- Allow NUMBER with precision to be replaced as boolean. Thanks to Silvan Auer for the report. - Force empty LOB to be exported as NULL when NO_LOB_LOCATOR is activated to have the same behavior. - Fix case where a LOB is NULL and ora2pg reports error : DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr LOB initialised with EMPTY_CLOB() are also exported as NULL instead of \\x - Fix replacement with PERFORM after MINUS. Thanks to Stephane Tachoires for the report. - Comment DBMS_OUTPUT.ENABLE calls. Thanks to Stephane Tachoire for the report. - Fix wrong replacement of SELECT by PERFORM after EXCEPT. Thanks to Stephane Tachoire for the report. - Apply ORACLE_COPIES automatic predicate on custom queries set with REPLACE_QUERY if possible. Thanks to pawelbs for the report. - Fix install of ora2pg.conf file in /etc/ instead of /etc/ora2pg/. Thanks to pawelbs for the report. - Add debug information before searching for custom type. - Attempt to fix error "ORA-01002: fetch out of sequence" when exporting data from a table with user defined types and ORACLE_COPIES. Thanks to pawelbs and Alex Ignatov fir the report. - Fix replacement of path to configuration file in scripts/ora2pg - Remove report sample from documentation about migration assessment report and replace it with a href link. Fix comment about export of domain index. - Always prefix table name with schema in Oracle row count, to prevent failure when the schema is not the connexion default. - Add pattern TOAD_PLAN_.* to the internal table exclusion list. - Fix modification of database owner search_path in import_all.sh auto generated script. Thanks to Stephane Tachoire for the report.
This is a maintenance release to fix several issues reported in new TEST action. There is also some feature improvement:
* Add OPTIONS (key 'true') on table FDW export when a column is detected as a primary key. * Add DELETE configuration directive that allow a similar feature than the WHERE clause to replace TRUNCATE call by a "DELETE FROM table WHERE condition". This feature can be useful with regular "updates". Thanks to Sebastien Albert for the feature request.
Here is the complete list of other changes:
- Fix the counter of user defined types and sequences in TEST action - Fix COPY import of data from column with user defined type with NULL value. - Fix DBD::Pg segmentation fault with direct INSERT import from column with user defined type. - Fix TEST action with multiple PG_SCHEMA export. Thanks to Michael Vitale for the report. - Fix documentation about PG_SCHEMA
This new major release adds a new action type TEST to obtain a count of all objects, primary keys, constraints, etc. at both sides, Oracle and PostgreSQL, to perform a diff between the two database and verify that everything have been well imported. It also fixes several issues reported by users.
A new ora2pg command line option have been added to ora2pg script:
* Add --count_rows command line option to perform a real row count on both side, Oracle and PostgreSQL, in TEST report.
See Release note for full list of changes.