Talk at PgConf EU, Wednesday October 19 2011
Here are the slides of the talk I've given at this conference "Ora2Pg best practices" in french and a translation in english
Version 8.8 is out, Thuesday October 13 2011
This release fixes the remaining case sensitivity issues on Oracle export.
- Before that release when you upgraded Ora2Pg using Makefile, the old ora2pg.conf was renamed as ora2pg.old. This can lead to lost previous configuration, the old ora2pg.conf is now untouched and the new one is installed as ora2pg.conf.new. - Renamed ora2pg.pl into ora2pg_pl in the package before installation to avoid the copy of the perl script into the site Perl install dir. It is still installed as ora2pg in /usr/local/bin by default. - Fix errors that appeared to be due to no quoting on the field names when ORA_SENSITIVE is enabled. Thank to Sam Nelson for the patch. - Limit case sensitivity on check constraints to column names only, before that if there was a value between double quote into the check constraint, it was wrongly changed to lower case. - Fix broken case sensitivity at data export when disabling/enabling triggers and truncating tables with copy or insert statement. - Change Ora2Pg version in packaging files that was still in 8.5. UPGRADE: only Ora2Pg.pm have changed so you can just override it.
Version 8.7 is out, Wednesday September 07 2011
This release adds multi-threading and speed improvement on LOBs export, there's also some major bug fixes and enhancements.
- The escape_bytea() function has been rewritten using a prebuild array to gain twice of performances. Thanks to Marc Cousin from Dalibo for the patch. - Improve speed of bulkload data by disabling autocommit by issuing a BEGIN at the start and COMMIT at the end. - Add multi-threading support. It is only used to do the escaping to convert LOBs to byteas, as it is very cpu hungry. There's a lot of CPU-waste here. The threads number is controled by a new configuration directive: THREAD_COUNT. Putting 6 threads will only triple your throughput, if your machine has enough cores. If zero (default value), do not use threads, do not waste CPU, but be slower with bytea. Performance seems to peak at 5 threads, if you have enough cores, and triples throughput on tables having LOB. Another important thing: because of the way threading works in perl, threads consume a lot of memory. Put a low (5000 for instance) DATA_LIMIT if you activate threading. Many thanks to Marc Cousin for this great patch. - Fix standard_conforming_string usage on export as INSERT statement. - Fix an issue with importing Oracle NULL character (\0 or char(0)) with bytea and character data with UTF8 encoding. Now whatever is the data type or the encoding, this character is simply removed to prevent the well known 'ERROR: invalid byte sequence for encoding "UTF8": 0x00.'. Thanks to Jean-Paul Argudo from Dalibo for the report. - Fix an incorrect syntax for "for each statement" triggers. Thanks to Henk Enting for the report. - Add comment at end of line to signal on which cursor the replacement on " EXIT WHEN (...)%NOTFOUND " is done. This will return something like "IF NOT FOUND THEN EXIT; END IF; -- apply on $1". Thanks to jehan Guillaume De Rorthais from Dalibo for the report this help a lot during Pl/Pgsql code review. - Fix table/column name replacement on constraint creation and dropping when REPLACE_TABLES/REPLACE_COLS is set during DATA/COPY export. - Fix table/column name replacement on indexes creation and dropping when REPLACE_TABLES/REPLACE_COLS is set during DATA/COPY export. - Remove unused table name parameter in _drop_indexes() function. - Add support to REPLACE_TABLES/REPLACE_COLS during schema export. Before this release those replacements were only applied to DATA or COPY export. You can now use it in schema export, it will replace table and/or column names in the TABLE/INDEX/CONSTRAINT schema export. MODIFY_STRUCT is still limited to DATA or COPY export as it have no sense outside this export. Unfortunately those replacements can not be done easilly in other export type like TRIGGER, FUNCTION, etc. so you must still edit this code by hand. - Use the bundled Perl Config module to detect if Perl is compiled with useithread. This mean that the old local defined %Config hash has been replaced by %AConfig. - SKIP indices is now obsolete and must be replaced with SKIP indexes. backward compatibility is preserved. - The file generated when FILE_PER_INDEX is activated has been renamed into INDEXES_... instead of INDICES_... - Add a warning on tablespace export when Oracle user is not a dba. - Fix fatal error when dumping to one file per function with double directory output. - Fix double print of FATAL messages and dirty database disconnect on fatal errors. - Add setting of client_encoding into each export type as defined in the configuration file. UPGRADE: Ora2Pg.pm, Ora2Pg/PGSQL.pm and ora2pg have changed so they must be overwritten. There's also changes in the configuration file and documentation has changed as well. Backward compatibility is fully preserved.
Version 8.6 has been released, Sunday July 10 2011
An issue that breaks view export and sadly introduced in v8.5 has been fixed. There's also more PL/SQL to PL/PGSQL conversion capabilities and two more configuration directives.
- Remove "use strict" from head of Ora2Pg.pm that breaks view export. This is usually removed before public release, but not this time. Thanks to Jehan Guillaume de Rorthais from Dalibo for the report. - Add a new configuration directive called COMPILE_SCHEMA that this force Oracle to compile the PL/SQL before code extraction to validate code that was invalidate for any reason before. If you set it to 0, you will force compiling of the user session schema, but you can specify the name of the schema to compile as the value too. Thanks to Jean-Paul Argudo from Dalibo for the solution. - Add new configuration directive EXPORT_INVALID to allow export of all PL/SQL code even if it is marked as invalid status. The 'VALID' or 'INVALID' status applies to functions, procedures, packages and user defined types. - Excluded from export all tables, types, views, functions and packages that contains a $ character. Most of the time they don't need to be exported. - PLSQL: add automatic conversion of Oracle SYS_REFURSOR as PostgreSQL REFCURSOR. - Rewrite entirely the parser of DBMS_OUTPUT du to concatenation errors. - PLSQL: add automatic replacement of some Oracle exception errors: INVALID_CURSOR=>INVALID_CURSOR_STATE, ZERO_DIVIDE=>DIVISION_BY_ZERO, STORAGE_ERROR=>OUT_OF_MEMORY. UPGRADE: Ora2Pg.pm, Ora2Pg/PGSQL.pm and ora2pg have changed so they must be overwritten. There's also changes in the configuration file and documentation has changed as well. Backward compatibility is fully preserved.
Version 8.5 available for download, Friday July 1 2011
This release allow to process PL/SQL Oracle code from file instead of a database to apply Ora2Pg code conversion and add three new configuration directives.
- When FILE_PER_FUNCTION is activated and export type is PACKAGE, Ora2Pg will now save all functions/procedures of a package body into a directory named as the package name and into different files. This will allow to load each function separatly or load them all with the OUTPUT SQL script generated by Ora2Pg. - Fix Oracle package body parsing failure when a procedure is declared inside an other. - Add new configuration options FILE_PER_CONSTRAINT and FILE_PER_INDEX to generate three files during the schema extraction. One for the 'CREATE TABLE' statements, one for the constraints (primary keys, foreign keys, etc.) and the last one for indices. Thanks to Daniel Scott for the feature request. - Allow to process PL/SQL Oracle code from file instead of a database to apply Ora2Pg code conversion. Thank to Mindy Markowitz for the feature request. See -i or --input_file command line option to ora2pg perl script or INPUT_FILE new configuration option. - Add new configuration directive STANDARD_CONFORMING_STRINGS that is used only during DATA export type to build INSERT statements. This should avoid 'WARNING: nonstandard use of \\ in a string literal'. Please check that this behavior is backward compatible with your PostgreSQL usage as this is the default now.
Version 8.4 available for download, Tuesday June 7 2011
This is a bug fix only release and Ora2Pg sources has move to SourceForge.net.
- Moves Ora2Pg to SourceForge.net. - Fix an issue on setting owner in "ALTER SEQUENCE ... SET OWNER TO". Thanks to Herve Girres for the report. - Bugfix on lower case convertion for check constraints extraction. Thanks to Alexander Korotkov for the patch.
Version 8.3 available for download, Sunday May 15 2011
There's no new functionality, this is a bug fix only release and every one should upgrade to it as it fixes some issues introduced in release 8.2.
- Fix issue on inherited user defined types converted to inherited tables. Add comment on unsupported inherited type in PostgreSQL too. Thanks to Mathieu Wingel for the report. - Fix issue on column default values. Oracle all this kind of strange syntax: counter NUMBER(4) default '' not null, that was translated to counter smallint DEFAULT '' by Ora2Pg. Thanks to Mathieu Wingel this is now rewritten as DEFAULT NOT NULL. - Fix case sensitivity on create view when there was double quote on the column name statement part. Thanks to Mathieu Wingel or the report. - Fix bad patch applied on column name case sensitivity issue during check constraint export. Thanks to Philippe Rimbault for the report. - Fix bug on package export introduced into version v8.2. The issue was related to end of package procedure detection. hanks to Mathieu Wingel or the report.
Version 8.2 available for download, Sunday May 01 2011
This release fixes some more major Oracle package body and user defined type parsing problems. There's also more PL/SQL to PL/PGSQL replacement. Please make a full upgrade asap.
- PLSQL: automatic replacement of EXIT WHEN cursor%NOTFOUND; by Pg synthax: IF NOT FOUND THEN EXIT; END IF;. Works with additionals condition too. - PSQL: Automatic replacement of SQL%NOTFOUND by NOT FOUND. - PLSQL: Add detection of TOO_MANY_ROW to NO_DATA_FOUND to add STRICT. - Completely rewrite the parsing of Oracle package body to handle all cases and especially prodedure declared into an other procedure. Those procedure are renamed INTERNAL_FUNCTION and must be rewritten. - Fix type usage of ora2pg Perl script. - Add a new directive FORCE_OWNER. By default the owner of the database objects is the one you're using to connect to PostgreSQL. If you use an other user (postgres for exemple) you can force Ora2Pg to set the object owner to be the one used in the Oracle database by setting the directive to 1, or to a completely different username by setting the directive value to that username. Thanks to Herve Girres from Meteo France for the suggestion and patch. - Add --forceowner or -f command line option to ora2pg program. - Add SHOW_ENCODING extract type to return the Oracle session encoding. For example: NLS_LANG AMERICAN_AMERICA.AL32UTF8 - Remove SYS_EXTRACT_UTC from index creation as Pg always stores them in UTC. Thanks to Daniel Scott for the patch. - In PLSQL code SYS_EXTRACT_UTC is replaced by the Pg syntaxe: field AT TIME ZONE 'UTC'. - Fix a pending problem with "Wide character in print at" on COPY mode. Thanks to Bernd Helmle from Credativ GmbH for the patch. - PSQL: Add automatic rewrite of FOR ... IN REVERSE ... into Pg synthax. - Fix column name case sensitivity issue during check constraint export. Thanks to Daniel Berger for the report. - Remove the possibility to add comment after a configuration directive it may not be used and it was generating an issue with the passwords configuration directives for examples. Thanks to Daniel Berger for the report. - Complete rewrite of user defined type extraction. Add support of inherited type using Oracle UNDER keyword as well as better support to custom type with BODY. Thanks to Mathieu Wingel for the report. - Fix case sensitivity on user defined types. Thanks to Mathieu Wingel for the report. UPGRADE: All files have changed so you need a fresh install/upgrade. Previous release used to remove any string starting from a # in the config file, this was to allow comments after a configuration directive. This possibility have been removed in this release so you can no more add comments after a configuration directive.
Version 8.1 available for download, Monday March 28 2011
This release fixes some major Oracle "package body" parsing problems and adds some useful changes to allow more flexibility in the export/import process. There's also more PL/SQL to PL/PGSQL replacement. Please make a full upgrade asap.
- Prevent Ora2PG to export twice datas when using FILE_PER_TABLE and the data output file exist. This is useful in case of export failure and you don't want to export all data again. This also mean that if you want to get new data you have to remove the old files before. - Fix parsing of procedure/function into pl/sql Oracle package. - Fix bug in IS NULL/IS NOT NULL replacement. Thanks to Jean-Paul Argudo from Dalibo for the report. - Add CREATE OR REPLACE on RULE creation. - Add DROP TRIGGER IF EXISTS before trigger creation. - Replace Oracle date "0000-00-00" by NULL. - Fix infinite loop in package/fonction type replacement. - Add one file per package creation if FILE_PER_FUNCTION is enabled. - Fix double quote in name of custom type extraction. - Add extraction of custom type IS VARRAY as an custom type of table array. Thank to Jean-Paul Argudo from Dalibo for the patch. - Fix multiple double quote in name of create index definition. - Apply excluded and limited table to tablespace extraction. - Fix function and procedure detection/parsing on package content. - Fix schema prefix in function name declaration in package export. - PLSQL: Replace some way of extracting date part of a date : TO_NUMBER(TO_CHAR(...)) rewritten into TO_CHAR(...)::integer when TO_NUMBER just have one argument. - Fix Makefile.pl error when trying to modify file ora2pg now renamed into ora2pg.pl - Add 3 new export types SHOW_SCHEMA, SHOW_TABLE and SHOW_COLUMN. Those new extraction keyword are use to only display the requested information and exit. This allow you to quickly know on what you are going to work. The SHOW_COLUMN allow a new ora2pg command line option: '--xtable relname' or '-x relname' to limit the displayed information to the given table. - Add type replacement for BINARY_INTEGER and PLS_INTEGER as integer.
Version 8.0 available for download, Tuesday March 15 2011
This major release simplify and improve Oracle to PostgreSQL export. Ora2Pg v8.x now assume that you have a modern PostgreSQL release to take full advantage of the Oracle compatibility effort of the PostgreSQL development team. Ora2Pg since v8.x release will only be compatible with Pg >= 8.4.
- Remove addition of AS for alias as with modern PG version this can be optional (Pg >= 8.4). - Fix CREATE with missing USER/ROLE for grant extraction. Thanks to Herve Girres for the report. - Apply missing psql_pgsql converter to view definition. - PLSQL : Normalize HAVING ... GROUP BY into GROUP BY ... HAVING clause - PLSQL : Convert call to Oracle function add_months() and add_years() into Pg syntax - Apply missing psql_pgsql converter to triggers WHEN clause. - Fix DECLARE CURSOR rewrite. - Allow one file per function / procedure / package exported with a new configuration option FILE_PER_FUNCTION. Useful to editing and testing. Thank to Jean-Paul Argudo from DALIBO for the feature request. - The FILE_PER_TABLE configuration option is now also applied to views. - Remove obsolete PG_SUPPORTS_INOUT and PG_SUPPORTS_DEFAULT as they are now supported by with modern PG version. - Allow to adjust PostgreSQL client encoding with a new configuration directive: CLIENT_ENCODING. - Add TRUNCATE_TABLE configuration directive to add TRUNCATE TABLE instruction before loading data. - Add type conversion of Oracle XMLTYPE into PostgreSQL xml type. - PLSQL: SYSDATE is now replaced by LOCALTIMESTAMP to not use timezone. Thanks to Jean-Paul Argudo from DALIBO for the report. - Use 'CREATE OR REPLACE' on create trigger function instruction. - Fix prefixing by OUTPUT_DIR when file per table/function is enabled. - Use 'CREATE OR REPLACE' on create view. - PLSQL_PGSQL is now enabled by default. If you want to export Oracle original function/procedure/package code, disable it. - PLSQL: WHERE|AND ROWNUM = N; is automatically replaced by LIMIT N; - PLSQL: Rewrite comment in CASE between WHEN and THEN that makes parser unhappy. - PLSQL: Replace SQLCODE by SQLSTATE UPGRADE: You must reinstall all and review your configuration file
Version 7.3 available for download, Monday Febuary 21 2011
This release fixes partitioning and grant issues. I also remove some deprecated configuration directive and adds more PL/SQL to PL/PGSQL conversion works.
- Remove PG_SUPPORTS_INOUT, now Ora2Pg assumes the PostgreSQL database destination support it (Pg > v8.1). - Remove PG_SUPPORT_ROLES, now Ora2Pg assumes the PostgreSQL database destination support it (Pg > v8.1). - Complete rewrite of the GRANT (user/role/grant) export type. It now should be only related to the current Oracle database. Note that do not try to import rights asis as you may import have errors or worse miss handling of the rights! Just remember that for example in Oracle a schema is nothing else than a user so it must not be imported like this. - Fix multiple errors in partitionning definition. Thank to Reto Buchli for the report. - PLSQL: reordering cursor Oracle declaration "DECLARE CURSOR name" into "DECLARE name CURSOR". Thank to Reto Buchli (WSL IT) for the report. - Fix miss handling of DEFAULT parameters value in convert_function(). Thanks to Leonardo Cezar for the patch. - Fix Oracle tablespace export where Pg tablespace location was based on Oracle filename. This fix extract the path and replace the filename with tablespace name. Thank to Reto Buchli (WSL IT) for the report. - Fix parsing of ending function code. Thanks to Leonardo Cezar for the patch. - Fix call to _convert_procedure() that is in fact the same function as _convert_function(). Thanks to Leonardo Cezar for the report. - Fix multiple call on tablespace alter index on the same object. Thank to Reto Buchli (WSL IT) for the report. - PSQL: Rewrite RAISE EXCEPTION concatenations. Double pipe (||) are replaced by % and value is set as parameter a la sprintf. Thank to Reto Buchli (WSL IT) for the report. - Add missing comment of PARTITION export type into configutation file. - Complete rewrite of the table partition export part has it was not handling all case and was really buggy. - PLSQL: add normalisation of the to_date() function. - Ora2Pg now warns during grant export when it is not connected as an Oracle DBA user. GRANT export need rights of Oracle DBA or it fail. - Fix install of changelog into Makefile.PL, name was wrong. Thanks to Julian Moreno Patino for the patch.
Version 7.2 available for download, Friday January 14 2011
This new release fix all long pending issues with export of Oracle BLOB to PostgreSQL ByteA. It also include 3 new configuration directives to extend Oracle to PostgreSQL export capabilities.
- Fix export of BLOB/RAW to bytea data import causing import to crash. Thanks to Ozmen Emre Demirkol for the report. - Add support to default value into function parameters (PG >= 8.4). Can be activated with a new configuration directive: PG_SUPPORTS_DEFAULT. Default is 1, activated. - Fix bad ending of exported function: remove trailing chars after END. - Add support to WHEN clause on triggers (PG >= 9.0), activated with a new configuration directive: PG_SUPPORTS_WHEN. - Add support to INSTEAD OF usage on triggers (fo incoming PG >= 9.1). Can be activated with a new configuration directive: PG_SUPPORTS_INSTEADOF. - Fix error using SKIP directive. Thanks to Laurent Renard from Cap Gemini for the report. - Fix missing perl object instance in format_data() function. - Fix duplicate procedure or function when export type use both FUNCTION and PROCEDURE.
Version 7.1 is available for download, Saturday December 04 2010
This release improve speed by 10 for direct data export using DBD::Pg and fixes bugs introduced in last one. Here is the full changelog for this release.
- Improve direct DBD::Pg data export/import speed by 10. - Add --section=3 in pod2man call into Makefile.PL. Thanks to Julian Moreno Patino for the report. - Renamed Changelog into changelog to avoid upstream warning with Debian package. Thanks to Julian Moreno Patino for the suggestion. - Fix some spelling mistakes in doc/Ora2Pg.pod. Thanks to Julian Moreno Patino for the fix. - Fix release version into Ora2Pg.pm and PLSQL.pm, was still in 6.5. - Fix direct data export/import using DBD::Pg. Thanks to Laurent Renard from Cap Gemini for the report. - Fix drop/create contraints and index during direct data export/import using DBD::Pg. Thanks to Thierry Grasland from Cap Gemini for the report.
Version 7.0 is out!, Wednesday November 24 2010
This is a major release with lot of speed improvement on data export. It also add new configuration directives and bug fixes.
- Improve data export speed by 6! The data export code has been entierly rewritten and the speed gain is really fun. - Rename ora2pg perl script into ora2pg.pl in sources because Windows users can't extract the tarball. During install it is renamed into ora2pg. Thanks to Andrew Marlow for the report. - Fix doinst.sh for SlackWare Slackbuid packaging. - The DEFER_FKEY configuration directive has been fixed as it only works in a transaction. Note that foreign keys must have been created as DEFERRABLE or it also will not works. Thanks to Igor Gelman for the report. - Add DROP_FKEY configuration directive to force deletion of foreign keys before the import and recreate them and the end of the import. This may help if DEFER_FKEY not works for you. - Add DROP_INDEX configuration directive to force deletion of all indexes except the automatic index (primary keys) before data import and to recreate them at end. This can be used to gain speed during import. - Add TSMSYS, FLOWS_020100 and FLOWS_FILES to the owners exclude list. This concern the SRS$ table and all tables begining with 'WWV_FLOW_' - Change the way DATA_LIMIT is working. It must be used now to set the bulk size of tuples return at once. Default is 10000. - Add OUTPUT_DIR configuration directive to set a base directory where all dumped files must be written. Default: current directory. - Change value of default numeric(x) type from float to bigint and change default numeric(x,y) type to double precision. - Change conversion type for BFILE from text to bytea.
This is a major release because there's lots of changes. First of all data export speed has been increased by 6 when dumping to file (I still have work on improving speed with direct PostgreSQL import). There's also new configuration directives.
Concerning the DEFER_FKEY issue, Ora2Pg will now try to order automatically the table data export to respect the foreign keys constraintes. If it fail it will display the following message:
WARNING: ordering table export to respect foreign keys is not possible. Please consider using DEFER_FKEY or DROP_FKEY configuration directives.
Type Ctrl+c and here you have 2 solutions, if your Oracle database is not so big and you have exported your schema with FKEY_DEFERRABLE set to 1, activating DEFER_FKEY may do the job. Else activate the DROP_FKEY configuration directive to force Ora2Pg to drop all concerned foreign keys before data import and recreate them at end of the import.
Ora2Pg Web site, Monday Febuary 22 2010
Ora2Pg Web site has been released today, I hope you will love it.