Moscow, Saturday Febuary 7 2015
Slides of the presentation I've given at PgConf.RU, Conference "Oracle to PostgreSQL migration: a hard way?" in english.
Version 15.1 released, Friday Febuary 6 2015
New minor release just to fix two annoying bugs in previous release.
- Fix replacement of function name which include SELECT in their name by PERFORM. Thanks to Frederic Bamiere for the report. - Fix creation of sources subdirectories when initializing a new migration project.
Version 15.0 released, Wednesday Febuary 4 2015
This major release improve PL/SQL code replacement, fixes several bugs and adds some new useful features:
- Add support to the PostgreSQL external_file extension to mimic BFILE type from Oracle. See https://github.com/darold/external_file for more information. - Allow export of Oracle's DIRECTORY as external_file extension objects This will also try to export read/write privilege on those directories. - Allow export of Oracle's DATABASE LINK as Oracle foreign data wrapper server using oracle_fdw. - Allow function with PRAGMA AUTONOMOUS_TRANSACTION to be exported through a dblink wrapper to achieve the autonomous transaction. - Allow export of Oracle's SYNONYMS as views. Views can use foreign table to create "synonym" on object of a remote database. - Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle column into varchar(n) or text. Default is to trim both side any space character. This behavior can be controlled using two new configuration directives TRIM_TYPE and TRIM_CHAR. - Add auto detection of geometry constraint type and dimensions through spatial index parameters. This avoid the overhead of sequential scan of the geometric column. - Add support to export Oracle sub partition and create sub partition for PostgreSQL with the corresponding trigger. - ALLOW and EXCLUDE directives are now able to apply filter on the object type. Backward compatibility can not be fully preserved, older definition will apply to current export type only, this could change your export in some conditions. See documentation update for more explanation. - Add PACKAGE_AS_SCHEMA directive to change default behavior that use a schema to emulate Oracle package function call. When disable, all calls to package_name.function_name() will be turn into package_name_function_name() just like a function call in current schema. - Add FKEY_OPTIONS to force foreign keys options. List of supported options are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION. - Add rewriting of internal functions in package body, those functions will be prefixed by the package name. Thanks to Dominique Legendre for the feature request.
Some change can break backward compatibility and make configuration directives obsolete:
- The ALLOW_PARTITION configuration directive has been removed. With new extended filters in ALLOW/EXCLUDE directive, this one is obsolete. Backward compatibility is preserved but may be removed in the future. - ALLOW and EXCLUDE directives do not works as previously. Backward compatibility may be preserved with some export type but may be broken in most of them. See documentation. - It is recommended now to leave the NLS_LANG and CLIENT_ENCODING commented to let Ora2Pg handle automatically the encoding. Those directives may be removed in the future.
Here is the full changelog of the release:
- Declares SYNONYM views as SECURITY DEFINER to be able to grant access to objects in other schema. - Fix wrong replacement of data type in function body. Thanks to Dominique Legendre for the report. - Fix missing column name replacement on trigger export when REPLACE_COLS is defined. Thanks to Dominique Legendre for the report. - Fix missing table replacement on trigger export when REPLACE_TABLES is defined. Thanks to Dominique Legendre for the report. - Fix case where IS NULL substitution was not working. Thanks to Dominique Legendre for the report. - Remove double exclusion clause when multiple export type is used with same column name and no values defined. - Allow parsing of DATABASE LINK and SYNONYM from a DDL file. - Add DIRECTORY export type to export all Oracle directories as entries for the external_file extension. This will also export read/write privilege on those directories. Thanks to Dominique Legendre for the feature request. - Review documentation about NULL_EQUAL_EMPTY. - Fix missing code to replace IS NULL as coalesce(...). Thanks to Dominique Legendre for the report. - Add external_file schema to search_path when BFILE is set to EFILE in directive DATA_TYPE. Thanks to Dominique Legendre for the request. - Remove IF EXIST clause to oracle function created by Ora2Pg for BFILE export. Thanks to Dominique Legendre for the report. - Add support to the PostgreSQL external_file extension to mimic BFILE type from Oracle. See https://github.com/darold/external_file for more information. - Add auto detection of geometry constraint type and dimensions through the spatial index parameters first. This avoid the overhead of sequential scan of the geometric column. - Remove lookup at package function when not required. - Fix issue with database < 10g that do not have the DROPPED column into the ALL_TABLES view. Thanks to Lance Jacob for the report. - Add trimming of data when DATA_TYPE is used to convert CHAR(n) Oracle column into varchar(n) or text column into PostgreSQL. Default is to trim both side any whitespace character. This behavior can be controlled using the new configuration directives TRIM_TYPE and TRIM_CHAR. - Update copyright year. - Add assessment cost for object TABLE SUBPARTITION and review cost for object DATABASE LINK. - Update documentation about SYNONYM export. - Allow export of SYNONYMS as views with a new export type: SYNONYM. - Fix object exclusion function with Oracle 8i and 9i. Thanks to Lance Jacob for the report. - Fix INTERVAL YEAR TO MONTH and DAY TO SECOND with precision. - Remove unused pragma from the cost assessment. - Suppress PRAGMA RESTRICT_REFERENCES, PRAGMA SERIALLY_REUSABLE and INLINE from the PLSQL code. There is no equivalent and no use in plpgsql. - Fix several issues in function/procedure/package extraction from file input and some other related bug. - Remove single slash and \\r from function code. - Remove schema from package name with input file to avoid creating function with SCHEMA.PKGNAME.FCTNAME - Fix ALLOW/EXCLUDE ignored with type COPY or INSERT. Thanks to thleblond for the patch. - Fix setting of NLS_NUMERIC_CHARACTERS and NLS_TIMESTAMP_FORMAT with multiprocess, the session parameters was lost with the cloning of the database handle. Thanks to thleblond for the patch. - Fix issue that could produce errors "invalid byte sequence" when dumping data to pg database by forcing the client_encoding when PG_DSN is set. Thanks to thleblond for the patch. - Fix issue to add parenthesis with function with no parameters and wrong use of PERFORM in cursor declaration. Thanks to hdeadman for the report. - Fix broken export of function or procedure without parameter in package body. Thanks to hdeadman for the report. - Fix ERROR: "stack depth limit exceeded" generated by an infinite loop in partition trigger when there is no default table when value is out of range. - Add support to Oracle sub partition export. - Fix issue with procedure in package without parameters. - Enable DISABLE_SEQUENCE in generic configuration file. - Fix unwanted alter sequence in data export when there is table allowed or excluded. - Fix initial default values of command line parameter that prevent value in configuration file to be taken. - Fix non working global definition of table in ALLOW and EXCLUDE directive with COPY and INSERT export. - Update ora2pg.spec, thanks to bbuechler for the patch. - Close temporary files before deleting them, on Windows if they are not explicitly closed there are not deleted. Thanks to spritchard for the patch. - Force schema name to be uppercase when PRESERVE_CASE is disable (default). Thanks to Jim Longwill for the report. - Add rewriting of internal functions in package body, those functions will be prefixed by the package name. Thanks to Dominique Legendre for the feature request. - Fix type replacement in user defined type. Thanks to Dominique Legendre for the report. - Add filter with INSTEAD OF triggers on views to TRIGGER export type. Thanks to Dominique Legendre for the feature request. - Fix replacement of function name when PACKAGE_AS_SCHEMA is disabled. - Fix PLSQL_PGSQL that was always set to 0 when -p was not used even if configuration directive PLSQL_PGSQL was activated. Thanks to Dominique Legendre for the report. - Remove ALTER SCHEMA ... OWNER TO ... when CREATE_SCHEMA is not enable. Thanks to Dominique Legendre for the report. - Add DBLINK export to be created as foreign data wrapper server. Thanks to the BRGM for the feature request. - Remove ALLOW_PARTITION configuration directive, with extended filter in ALLOW/EXCLUDE directive, this one is obsolete. Backward compatibility is preserved. - Add documentation about extended filters in ALLOW and EXCLUDE directive. - Update documentation about VIEW_AS_TABLE and remove statement change with export TYPE is VIEW. - Add filter to grant export on functions, sequences, views, etc. - Fix GRANT in ALLOW or EXCLUDE filters. - Add commented order: "REVOKE ALL ON FUNCTION ... FROM PUBLIC;" when the function is declared as SECURITY DEFINER. - Prevent collecting column information with SHOW_TABLE export type. - Fix default value SYSTIMESTAMP to CURRENT_TIMESTAMP, and remove DEFAULT empty_blob(). Thanks to hdeadman for the report. - ALLOW and EXCLUDE directives are now able to apply filter on the object type. Backward compatibility can not be fully preserved, older definition will apply to current export type only, this could change your export in some conditions. See documentation update for more explanation. Thanks to the BRGM for the feature request. - Force function to be created with SECURITY DEFINER when AUTHID in table ALL_PROCEDURES is set to DEFINER in Oracle. This only works with Oracle >= 10g. Thanks to Dominique Legendre for the feature request. - Add PACKAGE_AS_SCHEMA configuration directive to change default behavior to use a schema to emulate Oracle package function call. When disable all call to package_name.function_name() will be turn into package_name_function_name() just like a function call in current schema. Thanks to the BRGM for the feature request. - Add a note to documentation about the way to convert srid into Oracle database instead of in Ora2Pg. Thanks to Dominique Legendre for the hint. - Fix documentation about SHOW_ENCODING export type. - Remove use of REGEX_LIKE with Oracle version 9. Thanks to Lance Jacob for the report. - Replace new FKEY_OPTIONS by FKEY_ADD_UPDATE configuration directive with three possible values: always, never and delete. It will force or not Ora2Pg to add "ON UPDATE CASCADE" on foreign keys declaration. - Allow FORCE_OWNER to work with all exported objects. Thanks to BRGM for the feature request. - Add FKEY_OPTIONS to force foreign keys options. List of supported options are: ON DELETE|UPDATE CASCADE|RESTRICT|NO ACTION. Thanks to the BRGM for the feature request. - Fix ambiguous column in view extraction. Thanks to Dominique Legendre for the report. - Fix replacement of TYPE:LEN by boolean, ex: REPLACE_AS_BOOLEAN CHAR:1. Thanks to jwiechmann for the report. - Fix error ORA-00942 where Ora2Pg try to export data from a view defined in VIEW_AS_TABLE configuration directive. - Update list of excluded Oracle schema to the documentation. - Fix export of all views with comments when VIEW_AS_TABLE is set. - Fixed some typos in the generated sample configuration file. Thanks to Hal Deadman for the patch. - Limit column information export to the type of object extracted. - Remove call to MDSYS in SQL code. Thanks to Dominique Legendre for the report. - Add more Oracle schema to the exclusion list. - Fully remove join on DBA_SEGMENTS to retrieve the list of tables, views and comments. Replaced by ALL_OBJECTS. Thanks to Dominique Legendre for the help. - Exclude JAVA\$.* tables and fix tables list query to include newly created tables with no segments. Thanks to Dominique Legendre for the fix. - Fix regex that convert all x = NULL clauses to x IS NULL to not replace := NULL too. - Autodetect unusual characters in owner name when extracting data and used it embeded into double quote. - Replace single return with return new in trigger code. Thanks to Dominique Legendre for the report.
Version 14.1 released, Wednesday November 12 2014
This is a maintenance release only mainly to add patches that was not been applied in previous major release.- Remove ALLOW_CODE_BREAK, it is no more useful. - Change output of SHOW_ENCODING to reflect change to default encoding. - Comment ALLOW_PARTITION in default configuration file - Add QUERY and KETTLE export type in configuration file comments.
Version 14.0 released, Monday November 10 2014
This major release adds full export of Oracle Locator or Spatial geometries into PostGis, SDO_GEOM functions and
SDO_OPERATOR are also translated. This export adds the following features:1. Basic and complex geometry types support 2. Geometry data conversion from Oracle to PostGIS 3. Spatial Index conversion 4. Geometry metadata / constraints support 5. Spatial functions conversion
For spatial data export, you have three choice, WKT to export data using SDO_UTIL.TO_WKTGEOMETRY(), WKB to export data using SDO_UTIL.TO_WKBGEOMETRY() and INTERNAL to export geometry using a Pure Perl library. Unlike the first two methods, INTERNAL is fast and do not raise Out Of Memory. The export is done in WKT format so that you can verify your geometry before importing to PostgreSQL.
Other additional major features are:- Parallel table processing. - Auto generation of migration template with a complete project tree. - Allow user defined queries to extract data from Oracle.
Parallel table processing is controlled by the -P or --parallel command line options or the PARALLEL_TABLE configuration directive to set the number of tables that will be processed in parallel for data extraction. The limit is the number of cores on your machine. Ora2Pg will the open one connection to Oracle database for each parallel table extraction. This directive, when upper than 1, will invalidate ORACLE_COPIES but not JOBS, so the real number of process that will be used is (PARALLEL_TABLES * JOBS).
The two options --project_base and --init_project when used indicate to Ora2Pg to create a project template with a work tree, a generic configuration file and a shell script to export all objects from the Oracle database. So that you just have to define the Oracle database connection into the configuration file and then execute the shell script called export_schema.sh to export your Oracle database into files. Here a sample of the command and the project's tree.ora2pg --project_base /tmp --init_project test_project /tmp/test_project/ config/ ora2pg.conf data/ export_schema.sh reports/ schema/ fdws/ functions/ grants/ kettles/ mviews/ packages/ partitions/ procedures/ sequences/ tables/ tablespaces/ triggers/ types/ views/ sources/ functions/ mviews/ packages/ partitions/ procedures/ triggers/ types/ views/
It create a generic config file where you just have to define the Oracle database connection and a shell script called export_schema.sh. The sources/ directory will contains the Oracle code, the schema/ will contains the code ported to PostgreSQL. The reports/ directory will contains the html reports with the migration cost assessment.
Sometime you may want to extract data from an Oracle table but you need a custom query for that. Not just a "SELECT * FROM table" like Ora2Pg do but a more complex query. The new directive REPLACE_QUERY allow you to overwrite the query used by Ora2Pg to extract data. The format is TABLENAME[SQL_QUERY]. If you have multiple table to extract by replacing the Ora2Pg query, you can define multiple REPLACE_QUERY lines. For example: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')]
Other new features are:- Export of declaration of language C function. Previous version was not exporting function with no code body like external C function. - Export of COMMENT from views. - Function to replace some call to SYS_CONTECT(USERENV, ...) by the PostgreSQL equivalent. - Add POSTGIS_SCHEMA configuration directive to add the dedicated PostGis schema into the search_path. - Add PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress IF EXISTS call in DDL statement generated by Ora2Pg. - Triggers are now all excluded/allowed following the table names specified in the ALLOW and EXCLUDED directives - Allow automatic export of nested tables (TYPE+TABLE+COPY).
One change is not fully backward compatible: Ora2Pg now use UTF8 by default on both side. On Oracle connection NLS_LANG is set to AMERICAN_AMERICA.AL32UTF8, NLS_NCHAR to AL32UTF8. On PostgreSQL side CLIENT_ENCODING to UTF8. For export that dump to files, Perl binmode is set to utf8. You can always change those default setting in configuration file, but it is not recommanded.
Here is the full changlog of the release:- Fix inline comments into function declaration. Thanks to Marcel Huber for the report. - Fix case where SELECT ... INTO was wrongly replaced by PERFORM. - Fix DECODE() translation. Thanks to Dominique Legendre for the report. - Add replacement of SDO_OPERATOR into PostGis relationships. - Add replacement of SDO_GEOM spatial function to postgis ST_* functions. - Add GEOMETRY_EXTRACT_TYPE configuration directive to specify the geometry extracting mode: WKT (default), WKB and INTERNAL. - Add a pure Perl library to export SDO_GEOMETRY as a WKT representation. This is controlled by a new extraction type INTERNAL to use with the GEOMETRY_EXTRACT_TYPE configuration directive. - Remove USE_SC40_PACKAGE directive and any reference to this library, it is not useful now that we have the INTERNAL geometry extraction mode. - Fix replacement of varchar2 in PL/SQL function. - Fix bug in type replacement when default values used function. - Add export of declaration of language C function. Previous version was not exporting function with no code body like external function. - Fix create statement in export of view as table. Thanks to ntlis for the report. - Fix replacement of to_number without format. - Add export of COMMENT from VIEWS. - Add function to replace some call to SYS_CONTECT(USERENV, ...) by the PostgreSQL equivalent. - Fix parsing from file of tablespace. - Fix wrong alias name in FROM clause when extracting XML data. Thanks to Marc Sitges for the report. - Fix export of comments in FDW export, might be COMMENT ON FOREIGN TABLE. Thanks to David Fetter for the report. - Fix broken export of function based indexes. Thanks to Floyd Brown for the report. - Fix sequence with negative minvalue/maxvalue and negative increment. Thanks to jwiechmann for the report. - Fix forced owner to schema to the value of FORCE_OWNER when it is set to a user name. - Fix create schema when FORCE_OWNER is enabled. Thanks to Dominique Legendre for the report. - Add POSTGIS_SCHEMA configuration directive to add a schema to the search_path. Thanks to Dominique Legendre for the feature request. - Returns NULL when a geometry is NULL instead of calling ST_AsText with a null value. Thanks to Dominique Legendre for the report. - Add more explanation about values of CONVERT_SID. - Fix issue in DBMS_OUTPUT replacement. - Fix exclusion of default objects from type export. - When CONVERT_SRID is > 1 this value will be used to force the SRID value on all export. - Disable NULL_EQUAL_EMPTY in generic configuration when generating a project tree. - Add LOGMNR$ and RECAP$ in the exclusion objects list. - Fix performance issue in extracting data from geometry column and add AUDSYS,DVSYS and DVF to the list of schema to exclude. - Prefix table name with schema name on queries for retrieving data to avoid errors in multi schema export. - Add SDO_* cost to migration report. - Fix real number of Synonym that should be review. - Fix wrong report of CTXSYS synonym. - Enabled AUTODETECT_SPATIAL_TYPE by default. - Remove KETTLE and FDW export from the auto generated project. - Force the copy of /etc/ora2pg/ora2pg.conf.dist into the project directory with no more look at the current ora2pg.conf. Force autodetection of spatial type in the generic configuration. - Huge performance gain on querying information about Spatial column. Thanks to Dominique Legendre for the great help. - Fix wrong use of table alias with SEGMENT_NAME. - Add unified audit table (CLI_SWP$.*) from the exclusion list. - Fix operator in check condition of range partitions. Thanks to Kaissa Chellouche for the report. - Add to the internal exclusion list tables generated by spatial indexes MDRT_.*, sequences MDRS_.* and interMedia Text index DR$.*. Thanks to Dominique Legendre for the report. - Make REPLACE_TABLES and REPLACE_COLS work with VIEW. The view name and the columns aliases will be replaced. Take care that the table name or columns names in the statement will be kept untouched and need manual rewriting. Thanks to Sven Medin for the feature request. - Add PG_SUPPORTS_IFEXISTS configuration directive to be able to suppress IF EXISTS call in DDL statement generated by Ora2Pg. PostgreSQL below 9.x do not support this keywords. Thanks to George Kowalski fot the feature request. - Fix wrong substitution in EXECUTE ... USING statement, where parameters number was not prefixed by a $ sign. Thanks to Dominique Legendre for the report. - Fix document about KEEP_PKEY_NAMES that also affect unique key and not only primary key as it was specified in the documentation. Thanks to Dominique Legendre for the report. - Add tables generated by statistics on spatial index (MDXT_.*) into the internal exclusion list. This join the already excluded table generated by partition logging (USLOG$_.*) and materialized view logs (MLOG$_.*, RUPD$_.*) - Add DEFAULT_SRID configuration direction to permit change of the internal default EPSG srid 4326. - Fix new line after search_path settings. Thanks to Dominique Legendre for the report. - Triggers are now all excluded/allowed following the table names specified in the ALLOW and EXCLUDED directive, no more on there own name which had little interest. Thanks to Dominique Legendre for the feature request. - Add support to COPY export with Spatial objects. Thanks to Legendre Dominique for the great help to solve this problem. - Fix default SRID value when a NULL value is returned by Oracle, SRID 8307 and the corresponding EPSG SRID 4326. - Update documentation on relation between PARALLEL_TABLES and FILE_PER_TABLE - Add the -P or --parallel command line options and update documentation about parallel table processing. - Add PARALLEL_TABLES configuration directive to force ora2Pg to use on process and one connection per table up to the number of CPU specified. Thanks to menardorama for the feature request. - Add PARALLEL_TABLES configuration directive to force ora2Pg to use on process and one connection per table up to the number of CPU specified. Thanks to menardorama for the feature request. - Add --init_project and --project_base command line options to create a migration template with a complete project tree, a generic configuration file and script to automate export of all object in the project tree. - Fix unwanted space before AND returned by limit_to_tables(). Thanks to Alex Wang for the report. - Add note about regex inclusion/exclusion not working with 8i database in documentation - Fix regex inclusion/exlusion of table that was not more working since the inclusion of limit_to_tables() function. Thanks to alex wang for the patch - Exclude dropped tables (those who are in the recycle bin) from export. - When USER_GRANTS is disabled, aka login as dba user, force table list to be checked against DBA_SEGMENTS with SEGMENT_TYPE of type table or table partition. This could help solving some incomprehensible object found in Oracle view ALL_TABLES. - Fix query to retrieved list of tables, owner selection was set two time. - Add support to automatic nested table export (TYPE+TABLE+COPY). - Fix wrong export of materialized view log table. Thanks to Ronson Blossom for the report. - Update the SYSUSER array to exclude objects owned par those more users. - Fix unwanted export of overflow table of an index-organized table. Thanks to Ronson Blossom for the report. - Update the SYSUSER array to exclude objects owned par those users. - Display table owner in debug mode for SHOW_TABLE or SHOW_COLUMN. - Add a section to give hint about converting Oracle outer join syntax to ANSI. Thanks to Sven Medin for the links. - Fix issue #82 again. Thanks to Sven Medin fro the report. - Add first support to user defined queries to extract data from Oracle. This feature add a new configuration directive named REPLACE_QUERY. - Change program title when dump to file. - Fix MODIFY_TYPE directive that was broken when using type with space character. Thanks to Dmitry K. for the patch. - Show missing view name in debug mode when exporting some views as table. - Rewrite replace(a,b) with three arguments replace(a,b,'') for PostgreSQL. Thanks to Dominique Legendre for the report. - Convert all x <> NULL or x != NULL clauses to x IS NOT NULL. All x = NULL are converted into x IS NULL. Thanks to Dominique Legendre for the report. - Add warning at exit to signal when a OOM occurs. In that case, when a child Ora2Pg process was silently killed by the OOM killer there was no information that a failure occurs.
Version 13.0 released, Monday June 02 2014
This major release adds first support to export Oracle Spatial Objects to PostGis Spatial objects. There's also a new configuration
directive to allow logging of statement failures to prevent Ora2Pg to abort and continue to load valid data. The other main feature
is the possibility to convert DDL files without needing an Oracle database connection, until now this was reserved to files containing
stored procedures. There's also several bug fixes.- Allow error logging during data import. This feature controlled by the LOG_ON_ERROR directive allow you to not abort the data import process when an error is encountered and to log to a file the COPY or INSERT statement that generate the error. After fixing the statement you will be able to load the missing data. Thanks to menardoram for the feature request. - Force export type to be INSERT when COPY is used and a table have a GEOMETRY column. I can not find a solution to export as copy statement for the moment. Thanks to Dominique Legendre and Vincent Picavet for the help. - Fix export of user defined type as object. Thanks to Shanshan Wang for the report. - Limit look up of objects to the ALLOW or EXCLUDE filter into the SQL query instead of the Perl code to avoid retrieving huge list of objects on such database. Thanks to menardorama for the feature request. - Add support to spatial data export in INSERT mode. Still need some work in COPY export mode if possible. - Fix query to retrieve SRID that broken with patch on CONVERT_SRID. - Fix wrong filter with ALLOW directive when getting list of partition. - Add GRANT export read from an input file. - Fix data type conversion when using input file and data type such varchar2(10 BYTE). - Add export of comment with TABLE and VIEW exports using an input file. - Add extraction of TABLESPACE from an input file. - Add support to SEQUENCE extraction from input file. - Fix wrong filter with ALLOW directive when exporting partition. The filter was done on partition name instead of table name, that mean that setting ALLOW directive was resulting in no export at all. Thanks to menardorama for the report. - Add CONVERT_SRID configuration directive to control the automatic conversion of SRID to standard EPSG using the Oracle SDO function sdo_cs.map_oracle_srid_to_epsg() Oracle function. Thanks to Dominique Legendre for the help. - Fix a typo in the create index prefix on partitioned tables. Thanks to menardorama for the patch. - Fix non replacement of destination during SHOW_COLUMN and COPY export. Using MODIFY_TYPE was only working in TABLE export. - Force pl/sql conversion with TABLE export to replace advanced default values. Fix code TRUNC(SYSDATE, MONTH) in default value and everywhere that should be: date_trunc(month,LOCALTIMESTAMP). Thanks to menardorama for the report. - Fix code regarding unique partition index naming. Thanks to menardorama for the report. - Add PREFIX_PARTITION configuration directive. When enabled it will force renaming all partition table name with the name of the parent table. Thanks to menardoram for the feature request. - Add AUTODETECT_SPATIAL_TYPE in configuration file and documentation about this new directive. - Add export of SDO_GEOMETRY column type. They are basically exported to the non-constrained "geometry" type with SRID if defined. When the configuration directive AUTODETECT_SPATIAL_TYPE is enable, Ora2Pg will try to autodetect the geometry type, the dimension and the SRID used to set a constrained geometry type. For example, in the first case column shape with Oracle type SDO_GEOMETRY will be converted as: shape geometry(GEOMETRY) or shape geometry(GEOMETRY, 4326) and in the second case, with constrained geometry type: shape geometry(POLIGONZ, 4326) with a three dimensional polygon. Thanks to Vincent Picavet for the feature request and specification. - Add support to spatial index read from file. - Add export of Oracle spatial index. For example, index: CREATE INDEX cola_spatial_idx ON cola_markets(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX; will be exported as CREATE INDEX cola_spatial_idx ON cola_markets USING GIST(shape); Thanks to Vincent Picavet / Oslandia for the feature request and explanations. - Allow TRIGGER export to parse an input file with Oracle DML orders. - Add PG_SUPPORTS_CHECKOPTION configuration directive to not remove WITH CHECK OPTION in create view statement. It is supported in PostgreSQL 9.4. - Allow VIEW export to parse an input file with Oracle DML orders. - Allow TABLE export to parse an input file with Oracle DML orders. - Add SYNCHRONOUS_COMMIT configuration directive disabled by default. This is the current behavior of Ora2Pg, it set synchronous_commit to off before data import to PostgreSQL. This is only used when you load data directly to PostgreSQL, the default is off to disable synchronous commit to gain speed at writing data. Some modified or old version of PostgreSQL, like Greenplum, do not have this setting. - Add some useful information for Windows user in documentation. Thanks to Roger Park for the report. - Fix case when parentheses are omitted in index creation. Thanks to Yuri Ushakov for the report. - Fix export type PACKAGE when ALLOW is defined to extract only some packages. Thanks to Maciej Bak for the report. - Fix INSERT export where backslash should be escaped and single be doubled in standard conforming string notation. Thanks to Yuri Ushakov for the report. - Add important note about LONGREADLEN and DATA_LIMIT that could need to be adjusted to avoid out of memory. Thanks to Mike Kienenberger for the patch. - Fix case sensitivity issue with export of comment on column. Thanks to Pierre Crumeyrolle for the report. - Fix export of RAW data in COPY mode, was missing a backslash. Thanks to jwiechmann for the report. - Fix RAW data export in COPY and INSERT mode, RAW data type is returned in hex by DBD::Oracle. Thanks to jwiechmann for the report. - Fix one release 8i condition. - Fix inexistent column USE_NO_INDEX with Oracle 8i and MVIEW export. - Enclose call to utf8::encode and utf8::valid into eval. - Fix export of constraint with Oracle 8i release. - Fix unrecognized fatal error with 8i database. Thanks to UnvorherSeba for the patch. - Revert change level of error from fatal to error, when querying materialized view. - Change level of error from fatal to error, when querying materialized view.
Version 12.1 released, Tuesday January 28 2014
This is a maintenance release with some minor bug fixes and a new configuration
directive, INDEXES_SUFFIX, to allow appending a suffix to indexes names.- Fix example given for the WHERE configuration directive. Thanks to Bob Treumann for the report. - Add INDEXES_SUFFIX configuration option to allow append a suffix to indexes names. - Replace special charater ^M by \r as they are not supported by git. - Fix IF EXISTS in alter table of sub _drop_foreign_keys. Thanks to Francis Corriveau for the patch. - Fix isolation level when exporting data. Thanks to Ludovic Penet for the report. - Fix regression when ora2pg tries to create foreign keys on tables or to tables that are not selected for export. Thanks to Ludovic Penet. - Add information about backslashed comma into directive MODIFY_TYPE into Makefile.PL. - Add missing MODIFY_TYPE definition in documentation. - Allow backslashed comma into MODIFY_TYPE type redefinition. Example: TABLE1:COL3:decimal(9\,6),TABLE1:COL4:decimal(9\,6). Thanks to Mike Kienenberger for the report - Fix missing single cote into create_materialized_view() call. Thanks to Jacky Rigoreau for the patch. - Fix some typo in documentation, thanks to Mike Kienenberger for the report. - Add a chapter about installing DBD::Oracle into documentation. Thanks to Raghavendra for the patch. - Fix case sensitivity on external table name with FDW export type. Thanks to Guillaume Lelarge for the report. - Fix export of materialized views when PG_SUPPORTS_MVIEW is disabled. Thanks to Christian Bjornbak for the report. - Update copyright.
Version 12.0 released, Tuesday October 22 2013
This release fixes lot of issues and adds three new features. Using REORDERING_COLUMNS directive you will be able
to reorder columns to minimized the footprint on disc, so that more rows fit on a data page. The PG_SUPPORTS_MVIEW
will allow you to export materialized views with native PostgreSQL 9.3 syntaxe. The USE_TABLESPACE variable can be
used to export object using their original tablespace.- Skip constraints on system internal columns (sys_nc...$) from export. - Fix missing output directory in generic psql file for data loading. - Add missing progress bar during TYPE and PARTITION export type. - Remove duplicated message in debug mode during Oracle reconnection. - Allow file input with create type declaration to use ora2pg converter. Unsupported syntax is signaled into the output file. - Exclude MLOG$.* and RUPD$.* table from export. - Prevent export of indexes and constraints during FDW export type. - Fix wrong total number of sequences shown in progress bar. - Remove warning when PG_DSN is define during a export type that do not support direct import into PostgreSQL. - Auto switch prefix from DBA to ALL when error 942 is returned when looking at tables informations. A hint is also displayed to ask for activating USER_GRANTS or connect using a user with DBA privilege. - Add REORDERING_COLUMNS configuration directive to allow reordering columns during the TABLE export. This could help to minimized the footprint on disc, so that more rows fit on a data page. Thanks to Christian Bjornbak for the feature request. - Fix call to unblessed reference at disconnect when direct import to pg is not used. Thanks to Christian Bjornbak for the report. - Fix regression in drop/create foreign keys and index during data export. Thanks to Christian Bjornbak for the report. - Fix truncate table error with parallel and direct data copy. Thanks to keymaper for the report. - Fix several other issues with parallel and direct data import. - Fix trigger export on multi files when FILE_PER_FUNCTION is enabled. - Fix issue on converting boolean values with non default values. Thanks to Christian Bjornbak for the report. - Fix boolean value for disabled key in default %BOOLEAN_MAP key/value. - Fix case where INTO was wrongly replaced by INTO STRICT. Thanks to Jacky Rigoreau for the report. - Fix case where label after a END was not removed. Thanks to Jacky Rigoreau for the report. - Fix discard of input file parsing. Fix PERFORM replacement in PL/SQL code wirh cursor. Thanks to Jacky Rigoreau for the report. - Enable PG_SUPPORTS_MVIEW by default and update documentation. - Replace DBA_DATA_FILES by USER_SEGMENTS to get database size to avoid error ORA-00942. Thanks to Pierre Boizot for the report. - Fix trigger conversion error. Thanks to Pierre Boizot for the report. - Add support to PostgreSQL 9.3 materialized view syntaxe, this need a new configuration directive PG_SUPPORTS_MVIEW to be enabled. - Update default configuration file and documentation about USE_TABLESPACE. - Add USE_TABLESPACE configuration directive to force ora2pg to use Oracle tablespace name with table, constraints indexes and indexes if tablespace in not in the default (TEMP, USERS, SYSTEM). Thanks to Rob Moolhuijsen for the feature request. - Allow DEFER_FKEY, when enabled during TABLE export, to create all foreign keys as DEFERRABLE and INITIALLY DEFERRED. Thanks to David Greco for the patch. - Fix non working ON_ERROR_STOP set to 0 during data export. - Lot of code changes to fix dump to file in multiprocess mode. Ora2Pg will also only drop/create constraints and indexes related to the allow/exclude tables, thanks to Maciej Bak for the report. - Force decimal character from Oracle output to be a dot. Thanks to Maciej Bak for the report. - Add default exclusion of Oracle recycle bin objects with name begining by BIN$. - Fix escaping quote in table and column comments. Thanks to realyota for the report. - Reduce DECODE migration cost from 2 to 1 unit. - Reduce OUTER JOIN (+) migration cost from 3 to 1 unit. - Add Time::HiRes to the requirement chapter for Perl <= 5.8. Thanks to Mike Kienenberger for the report. - Replace wrong use of --config instead of --conf into the documentation. Thanks to Mike Kienenberger for the report. - Fix regex used to rewrite CREATE VIEW code. Thanks to David Greco for the patch. - Fix an issue with oracle copies when primary key was negative. Thanks to David Greco for the patch. - Fix case sensitivity with SEQUENCE when preserve_case is enabled. Thanks to Jean-Max Reymond for the report. - Fix table COMMENT export when preserve_case is enabled. Thanks to Jean-Max Reymond for the report.
Version 11.4 released, Tuesday May 28 2013
This release fixes others several major issues on migration cost assessment that was not addressed in previous release,
please upgrade.- Fix other major issues in migration cost assessment. - Redefine some migration cost values to be more precise.
Version 11.3 released, Monday May 27 2013
This release fixes several major issues on migration cost assessment, especially with stored procedures with lot of lines
or if you have lot of comments in that code. You may want to run your database evaluation again as the estimated times
can be up to tree time lower on huge PL/SQL code.- Add full details about PL/SQL evaluation by ora2pg when --estimate_cost or ESTIMATE_COST is enable. This will display cost units per keywords detected in the function/package code. - Fix wrong cost unit assessment on PL/SQL code size, this bug generated very high migration cost assessment for functions/packages with lot of lines. Please run your tests again, estimated times can be up to tree time lower on huge code. - Remove comments before code evalution. - Fix file input parser for PL/SQL packages export when IS or AS was in the next line than the CREATE PACKAGE BODY ... - Exclude NOT NULL constraint from the count of CHECK constraints into the TABLE report. - Fix decimal precision in table migration assessment cost. - Fix typo in changelog.
Version 11.2 released, Tuesday May 01 2013
This release fixes several major issues especially with direct import of data into PostgreSQL and Windows port that was both broken.- Update doc about Windows multiprocess issues and acknowledgements. - Fix Windows OS issues using multiprocessing options by disabling multiprocess support on this plateform. When -J or -j will be used a warning will be displayed and Ora2Pg will simply run single process like in previous 10.x versions. Thanks to Jean Marc Yao Adingra for the report. - Fix RAW and LONG RAW export to ByteA. Thanks to Prabhat Tripathi for the report and testing. - Fix patch regression on multiple TRUNCATE call for a single table. Thanks to David Greco for the report. - Placed calls to DB handle InactiveDestroy outside the forked process to prevent fatal errors on Windows. Thanks to Jean Marc Adingra for the report. - Forked running processes are renamed into more readable name like "ora2pg logger" for the progress bar, "ora2pg - querying Oracle" when used with -J option and "ora2pg - sending to PostgreSQL" to better know what is the current job of the process. - Removed the use of /Y flag in Windows install script, this was causing error "dmake: Error code 130, while making install_all". Thanks to Jean-Marc Adingra for the report. - Fix direct import to PostgreSQL that was just producing nothing. Thank to David Greco for the patch. - Fix ora2pg usage documentation. - Add an underscore to CLIENT ENCODING in SHOW_ENCODING output to be the same as the configuration directive.
Version 11.1 released, Sunday April 07 2013
This release adds partition data speed improvement by exporting data directly from and into the partitioned tables. There's also some bug fix on RAW or LONG RAW data export and PL/SQL to PL/PGSQL code rewrite.- Adjust cost assessment for indexes, tables and tables partition. - Add comment to report of index partition about local index only. - Fix position of TRUNCATE TABLE in output file. - Fix export of data from RAW or LONG RAW columns, they was exported as hex string. Now data are converted using utl_raw.cast_to_varchar2() function before being escaped for insert into a bytea. Thanks to Alex Delianis for the report. - Fix issue with Oracle TIMESTAMP(0) data export that add a single ending point, ex: "2008-08-09 00:00:00.", this ending character is now removed by format_data_type(). Thanks to Pierre-Marie Petit for the report. - Fix typo on MODIFY_STRUCT description. - Force DEBUG to off in default configuration file. - Change range PARTITION operators in the check conditions, >= and < replaced by > and <=, corresponding to Oracle VALUES LESS THAN. - Add ALLOW_PARTITION to limit data export to a list of partition name. - PLSQL: Fix wrong replacement of SELECT by PERFORM during VIEW export. - Partitioned tables data is now imported directly into the destination tables instead of inserted into the main table and dispatched by the trigger. Ora2Pg will automatically detect the in/out table partition, there's nothing to configure. - PL/SQL: Do not allow decode() rewrite by case/when/else when there is a function call in it. - Fix Error when Compress::Zlib is not installed, this module is not mandatory.
UPGRADE: please reinstall all as all files have changed.
Version 11.0 released, Sunday Marsh 24 2013
This new major release adds multi-processing support to export data in parallel mode at full speed, this allow a speed improvement during data import by more than ten times. Multiprocessing capabilities allow Ora2Pg to be closer than the speed of an ETL. There's also a new export type to generate Kettle XML transformation files to be used with Kettle for data import. A lot of work have be done for speed improvement to scan Oracle database with huge number of objects.- Add documentation about JOBS, ORACLE_COPIES, DEFINED_PK configuration directive and informations about KETTLE export type. - Add KETTLE export type to generate XML transformation file definition for Penthatlo Data Integrator (Kettle). Thanks to Marc Cousin for the work. Example of use: ora2pg -c ora2pg.conf -t KETTLE -j 12 -J 4 -o loaddata.sh - Fix major bug in export of auto generated named constraint. Thanks to mrojasaquino fot the report. - Show number of rows in the top largest tables. - Add TOP_MAX description to the documentation. - Add the TOP_MAX directive to default configuration file and update documentation. Directive used to control the top N tables to show. - Add top N of largest tables in SHOW_TABLE, SHOW_COLUMN and SHOW_REPORT export type. - Fix progressbar output when ora2pg is interrupted by ctrl+c. - Add JOBS, ORACLE_COPIES and DEFINED_PK directives to configuration file. JOBS replacing THREAD_COUNT but backward compatibility is preserved. - Add 3 new command line options, -j | --jobs and -J | --copies, used to set the number of connection to PostgreSQL and Oracle for parallel processing. The third, -L | --limit is used to change DATA_LIMIT at command line. - Add multiprocess support on data export. With the help of Thomas Ogrisegg. - Add more schema in SYSUSERS that should not be exported. - Add full detailed information about SYNONYM in SHOW_REPORT. - Add MODIFY_TYPE configuration directive to allow some table/column type to be changed on PostgreSQL side during the export. - Fix objects type count in progressbar of SHOW_REPORT. - Restrict table and index in SHOW_REPORT to the tables defined in ALLOW and EXCLUDE directives. - Show total number of rows in SHOW_TABLE and SHOW_REPORT output. - Add top 10 of tables sorted by number of rows in SHOW_TABLE and SHOW_REPORT output. - Fix typo in SYNONYM objects. - Add report of top ten tables ordered y number of rows. - Rewrite most of the Oracle schema storage information extraction for speed improvement. - Use Hash to store column informations. - Fix %unique_keys declaration in _table() method. - Remove call to _table_info() from SHOW_REPORT code as those informations are already loaded with the _table() method. - Fix missing column definition on TABLE export. - Add progress bar during output generation following export type. - Add STOP_ON_ERROR configuration directive to enable/disable the call to ON_ERROR_STOP into generated SQL scripts. Thanks to Ludovic Penet for the feature request. - Huge speed improvement on columns informations retrieving. - Fix progress bar to keep the total number of tables related to the ALLOW or EXCLUDE configuration directives. Thanks to Ludovic Penet for the report. - Change return type of function _table_info(), it now returns data instead of the database handle. - Improve speed on indexes and constraints extraction for database with huge number of tables. - Improve performance to retrieve columns information and comments. - Remove report of column details during export in debug mode, use SHOW_COLUMN instead. - Remove call to upper() in objects owner condition to improve performance with database with huge number of objects. - Add a fix to not export foreign key for exclude tables. Thanks to Ludovic Penet for the report. - Fix Windows install issue with copying ora2pg.conf.dist. Thanks to Dominique FOURDRINOY for the report. - Increase the cost of Oracle function not converted to PG automatically.
UPGRADE: reinstall all is required to override the old installation, you may use the new ora2pg.conf.dist file which included the new configuration directives.
Version 10.1 released, Wednesday January 16 2013
This release adds HTML report for migration cost assessment and some bug fix. A sample of the HTML report can be found at http://ora2pg.darold.net/report.html- Fix global where clause that should not be overwritten. Thanks to Dan Harbin for the patch. - Fix bug/typo in boolean replacement, where a colon was used instead of a single quote. Thanks to Alex Delianis for the patch. - Update copyright. - Add detection of additional Oracle functions for better migration cost assessment. - Update documentation. - Force report detail in lowercase. - Added information about the migration cost value to the reports. - Add --dump_as_html command line option and DUMP_AS_HTML configuration directive. - Allow migration report to be generated as HTML. - Separate report generation code from data collection code. UPGRADE: almost all files have changed so it is better to override your full installation.
Version 10.0 released, Tuesday December 18 2012
This is the first version of Ora2Pg 10.x series, that is a major release. Overall numerous improvements and bugs fixes there's
now a new export type: SHOW_REPORT that will output a report of all objects contained in your Oracle database and some comments
on how they will be exported. With this report you can use a new directive ESTIMATE_COST to ask to Ora2Pg to evaluate the database
migration cost in terms of man days. There's also an other new configuration directive EXTERNAL_TO_FDW, disable by default, to
permit the export of all Oracle external tables as file_fdw foreign tables.
The database content report and the migration cost estimation is a work in progress so all feedback on these new features are welcome.
Here is the complete changelog of the v10.0 release:- Update documentation about ora2pg usage and new feature. - Fix quote escaping on table comments. Thanks to Sebastian Fischer. - Fix some other issues with 8i databases, added database version auto-detection to avoid printinf warning. Thanks to Sebastian Fischer for the help. - Allow null value in BFILE to the oar2pg_get_bfilename(). - Update documentation about BFILE export. - Add drop function ora2pg_get_bfilename() when necessary. - Add support to BFILE external path export by creating a function ora2pg_get_bfilename( p_bfile IN BFILE ) to retrieve path from BFILE. BFILE will be exported as text field with the full path to the file as value. Note that this is the first time that Ora2Pg need write access to the Oracle database, if you do not have BFILE or you have set the corresponding PostgreSQL type asd bytea (the default) the function will not be created. - Fix a performance issue when extracting BLOB with a LongReadLen upper than 1MB. - Fix priviledge on schema created from Oracle package body. Thanks to Dominique Legendre for the report. - Add object type in comment before priviledge extraction. - Order output of grant to groups grants by object types. This is useful to quickly disable some SQL orders corresponding of not already loaded objects. Thanks to Dominique Legendre for the feature request. - Fix progress bar output. - Fix priviledge on sequence, tablespace and schema. - Fix backward compatibility with Oracle 8i, remove query with JOIN. Thanks to Sebastian Fischer for the report. - Fix backward compatibility with Oracle 8i on priviledge extraction. Thanks to Sebastian Fischer for the report. - Fix backward compatibility with Oracle 8i on index extraction. Thanks to Sebastian Fischer for the report. - Add more precision in cost estimation. - Add somme other PL/SQL uncovered code detection. - Add more debug information during data extraction. - Removed progress bar when debug is enabled. - Add report and estimate cost about CHECK constraint and function based indexes. - Update documentation about new export directives SHOW_REPORT and ESTIMATE_COST. - Add --estimate_cost and --cost_unit_value command line options. - Add ESTIMATE_COST and COST_UNIT_VALUE to default configuration file. - Rewritte and extend support to ROWNUM replacement. - Remove incompatible grants between Oracle and the PortgreSQL export, especially on views. - Limit GRANT export to VALID object. Activate EXPORT_INVALID to enable grants export on all object. - Add export of VALID only views. To export all with INVALID ones you must activate the EXPORT_INVALID directive. Thanks to Dominique Legendre for the feature request. - Fix issue in substr() pl/sql replacement, thanks to Dominique Legendre for the report, plus add other code replacements in pl/sql. - Fix issue with function name not on the same line as the create statement - was affecting file input only. - Add report of number of JOB object in the database (SHOW_REPORT). - Add PL/SQL replacement of various form of EXEC function call. - Remove creation of password with users that are not requiring password. Thanks to Dominique Legendre for the feature request. - A sql type and a precision can now be used in REPLACE_AS_BOOLEAN to replace all filed with that type as a boolean, example: NUMBER:1 will replace all field of type NUMBER(1) as a boolean. - Fix grants on partition export, will now used all_ and user_ tables. - Fix removing of newline in the DECLARE clause. Thanks to Dominique Legendre for the report. - PostgreSQL client_encoding is now forced to UTF8 when BINMODE is set to utf8. Thanks to Dominique Legendre for the report. - Replace DISABLE TRIGGER ALL by DISABLE TRIGGER USER following the value if USER_GRANTS to avoid permission denied on constraint trigger when data are load under a non PG superuser. Thanks to Dominique Legendre for the report. - Rename DISABLE_TABLE_TRIGGERS to DISABLE_TRIGGERS and set default value to 0. Other values are USER or ALL following the connected user. - Fix missing newline after comment in PL/SQL code. Thanks to Dominique Legendre for the report. - Fix report message on external table export. - The export TYPE have been entirely rewritten to only export supported user defined types. Exported are: Nested Tables, Object type, Type in herited and Subtype, Varrays. Associative Arrays, Type Body and type with member method are not supported. - When FILE_PER_INDEX is enable, SQL order to move indexes in their respective tablespace will be written into a dedicated file prefixed by TBSP_INDEXES_. - Fix location on external table export. Thanks to Thomas Reiss for the help. - PG_SUPPORTS_INSTEADOF is now activated by default, that mean that migration should be done on PG >= 9.1. - Remove obsolete --xtable commande line option, should be replaced by --allow, backward compatibility is preserved. - Add EXTERNAL_TO_FDW configuration directive, disable by default, to export all Oracle external tables as file_fdw foreign tables. - Fix an other case where user defined type were not exported with an ending semi-colon. Thank to Dominique Legrendre for the report. - Fix export of user defined type with extra ");" at end of the type definition and remove system types from export. Thanks to Dominique Legendre for the report. - Add PLSQL replacemement of currval. Thanks to Thomas Reiss for the patch. - Add PLSQL replacement of PIPELINED and PIPE ROW by SETOF and RETURN NEXT. - Add rewrite of Oracle DETERMINISTIC function into PostgreSQL IMMUTABLE function. - Fix copy during install on MacOSx and add /Y option to windows install copy to force overwrite existing files. Thanks to Dennis Spaag for the report. - Fix issue exporting rows with perl ARRAYS ref. Thanks to Sorin Gheorghiu for the report. - Add report of number of database link in SHOW_REPORT output. - Fix major bug on export of NUMBER with precision, they was all exported as bigint. Thanks to Dominique Legendre for the report. - Add progress bar during SHOW_REPORT export. - Add detailed report about index in SHOW_REPORT output. - Fix data export when schema was given in lower case. Thanks to Dominique Legendre for the report. - Add SHOW_REPORT export type to display a full summary of the Oracle database content. - PLPGSQL: add the name keyword to XMLELEMENT call. Thanks to Thomas Reiss for the hint. - Add SHOW_VERSION export type to display the version of Oracle. - Add COLLATION to the keyword list. Thanks to Dominique Legendre for the report - Change documentation to add more detail on exporting Oracle views as PostgreSQL tables based on the new VIEW_AS_TABLE directive. - Add -a | --allow option and --view_as_table to ora2pg script. - Add VIEW_AS_TABLE configuration option to allow export of view as table and permit the additional use of the ALLOW or/and EXCLUDE directive. Thanks to Dominique Legendre for the feature request. - Removed conflict with transaction when DEFER_FKEY was enabled and allow DEFER_FKEY and DROP_FKEY to be enabled both. Before, only DEFER_FKEY was used in this case, now both are used and of course DEFER_FKEY is wasted. Thanks to Dominique Legendre for the report. - Directives ALLOW and EXCLUDE are now usable with all kind of object following the export type. - Rename TABLES directive as ALLOW to be less confusing, backward compatibility is preserved. - Thanks to Dominique Legendre for the feature request. - Remove auto ordering of table export following the foreign keys to fix an infinite loop. Thanks to Siva Janamanchi for the report. - Rewrite the view as table export to reuse the same code as table export, old code was resulting in issues with disable triggers and deferring constraints. - Remove alter session to set NLS_NCHAR that was returning error on some installation. Thanks to Dominique Legendre for the report. - Fix replacement of IS SELECT wrongly replaced by IS PERFORM in some case. Thanks to Dominique Legendre fot the report. UPGRADE: Almost all files have changed so a new installation is required.
With very specials thanks to Dominique Legendre for his help on testing and retesting all Ora2pg features and suggesting numerous improvements. About the migration cost assessment part, I want to thanks Philippe Lang - Service Delivery Manager, Atos - for the complete review of the french documentation and the global project management. All my recognition goes to the French DGFiP (Direction Generale des Finances Publiques) who asked for that great Ora2Pg improvement and of course Dalibo who offer me the opportunity to work on this project and PostgreSQL in general.
Version 9.3 released, Sunday October 07 2012
This release adds lot of changes with five new configuration directives, a new export type MVIEW to export materialised view and an automatic detection and setting of Oracle character set and PostgreSQL client encoding. Lot of bug fixes.- Add auto detection of Oracle character set and the corresponding PostgreSQL client encoding to use. NLS_LANG and CLIENT_ENCODING configuration directives can be leaved commented, Ora2Pg will set their values automatically. - Add PL/SQL replacement of CURSOR IS SELECT by CURSOR FOR SELECT and IS REF CURSOR by REFCURSOR. Thanks to Dominique Legendre for the report. - Fix missing set client_encoding orders into fonction or procedure export file. Thanks to Dominique Legendre for the report. - Fix not working SKIP configuration directive. Thanks to Siva Janamanchi for the report. - Add configuration directive NULL_EQUAL_EMPTY to disable the IS NULL and IS NOT NULL replacement into PL/SQL code. Enabled by default. Thanks to Dominique Legendre for the feature request. - Remove exclusion of object names with the dollar sign. Thanks to Konrad Beiske for the suggestion. - Fix timestamp with time zone when microsecond is enabled. Thanks to Siva Janamanchi for the report. - Fix extra semi-column when PKEY_IN_CREATE is enabled. Thanks to Siva Janamanchi for the report. - Update configuration about boolean replacement. - Allow any column type replacement as a boolean in PostgreSQL, values will be converted as well. Thanks to Konrad Beiske for the feature request. - Add REPLACE_AS_BOOLEAN and BOOLEAN_VALUES configuration directives to allow type replacement with a boolean. Thanks to Konrad Beiske for the feature request. - Add new configuration directive PKEY_IN_CREATE to add primary keys definition in CREATE TABLE statement instead of creating them after with an ALTER TABLE statement. For Greenplum database, primary key must be created with the CREATE TABLE statement so you may want to enable this configuration directive. Thanks to Siva Janamanchi for the feature request. - Add new configuration directive USE_RESERVED_WORDS to force Ora2Pg to auto-detect PostgreSQL reserved words in Oracle object's names and automatically double quote them. Thanks to Siva Janamanchi for the feature request. - SHOW_TABLE and SHOW_COLUMN will now display a warning when Oracle object's name is a PG reserved words. Those names will need to be renamed or double-quoted (see USE_RESERVED_WORDS). - Add TIMESTAMP WITH LOCAL TIME ZONE Oracle type conversion to timestamp and force timestamp with time zone format to use TZH:TZM. Thanks to Siva Janamanchi for the report. - Fix table and column replacement issues introduced with path that removed double-quote when PRESERVE_CASE is disabled. Thanks to Steve DeLong for the report. - PLPGSQL convertion: Fix SELECT replacement with PERFORM in VIEW declaration. Thanks to Thierry Capitaine for the report. - Add display Ora2Pg type conversion map between Oracle originals types and PostgreSQL's types when using export type SHOW_COLUMN. Thanks to Thierry Capitaine for the feature request. - Reorder command line options in ora2pg script usage and documentation. - Add call to quote_ident() and quote_literal() into materialised functions to secure parameters. - Fix major issue in pl/sql to pl/pgsql conversion with multiple package declaration in the same code record. Thanks to Marc Cousin for the report. - Add data type TIMESTAMP WITH TIME ZONE. Thanks to Siva Janamanchi for the report. - Add new export type: MVIEW to allow export of all materialised views as snapshot materialised view (fully reload of the view). - Add -e | --exclude option to Perl script ora2pg to exclude some given objects from the export. It will override any value of the EXCLUDE directive. The value is a coma separated list of object name or regex. - Update domumentation about the EXCLUDE directive change. - Allow exclusion from export of functions, procedures and functions in package by specifying a list of name or regex in EXCLUDE directive. Thanks to Keith Fiske from Omniti for the feature request.
UPGRADE: Almost all files have changed so a new installation is required.
Version 9.2 published, Wednesday September 05 2012
This release has lots of code improvement especially about case sensitivity. It adds a major feature by allowing data export of user defined composite type. It also fixes several bugs and have major changes, see full changelog below for details.- In plpgsql conversion, SELECT without INTO becomes PERFORM. - In plpgsql conversion, EXECUTE IMMEDIATE replaced by EXECUTE. - Fix DATA_TYPE value in configuration file. - Fix case sensitivity on data export using COPY mode. - Directive XML_PRETTY is now disabled by default as it is better to use getClobVal() to get the XML data out of an xmltype column. - Add documentation about regex usage int EXCLUDE and TABLES directives. - Remove all double-quote around object name when CASE_SENSITIVY is disabled. Thanks to Dominique Legendre for the suggestion. - Rename CASE_SENSITIVE by PRESERVE_CASE to be less confusing, backward compatibility preserved. Thanks to Dominique Legendre for the request. - Add support to user defined type data export. Before it will simply export an array reference ARRAY(0xa555fb8), now the array is explored and inserted as ROW(col1,col2,...). Thanks to Mathieu Wingel for the feature request. - Fix bug in direct data import in postgresql with COPY: pg_putcopydata can only be called directly after issuing a COPY FROM command. Thanks to Steve Delong for the report. - Add warning at any debug level before abort when a data file already exist during data export. - Fix issue with oracle sensitivity when exporting data. - Fix search_path on package export, indexed and constraints files on TABLE export. - Remove obsolete ORA_SENSITIVE configuration directive, thanks to Dominique Legendre it is no more used. - Force automatic conversion of PL/SQL when entry is an input file. - Fix errors in main file for package loader with FILE_PER_FUNCTION enabled. - Fix case where package body where not exported. - Add missing EXPORT_INVALID directive into default configuration file. - Fix replacement of END followed by the name of the function, the semi- colon was removed. - Fix case sensitivity issue in INDEX creation. - Fix case sensitivity issue in CHECK constraint and a typo in a progress bar variable. - Replace old DATA export type by INSERT in configuration file. - Fix case sensitivity issue in ALTER TABLE ... ADD CONSTRAINT. Thanks to David Greco for the report. - Add set client_encoding before table output to handle character encoding into comments and possibly objects names. - Fix some case sensitivity issue with schema name. Thanks to Dominique Legendre for the report. - Do not display warning message about direct import if no connection to a PostgreSQL database is defined. - Allow multiple export type to be specified into the ora2pg -t command line option. - Dump progress bar to stderr instead of stdout to separate logs. - Add new -q | --quiet option to perl script ora2pg to disable progress bar.
UPGRADE: all files have changed, you have to reinstall all. Please report any issue.
Version 9.1 is out, Sunday August 19 2012
This release adds support to automatic detection of case sensitivity on Oracle object names and two new configuration directives (EXPORT_SCHEMA and PG_INTEGER_TYPE). There is also a new feature that shows a progress bar during data espoxt and a lot of bug fix.- Add progress bar to show data export progression. - Add -q | --quiet option to ora2pg perl script to disable progress bar. - Change documention about tnsnames.ora to mark it is not necessary. - Add progress bar during data export, per table and globaly. - Replace export type DATA by INSERT to mark the difference with COPY and avoid confusion. Documentation is updated and full backward compatibility preserved. - Improve Oracle case sensitivity detection on column and update documentation about ORA_SENSITIVE directive. - Direct import for COPY statement now used DBD::Pg and pg_putcopydata() instead of a pipe to psql command. - Fix case sentitivity issue on disabling/enabling all triggers. - Add autodetection of case sensitivity with column name. - Move trunc() to data_truc() convertion into the ALLOW_CODE_BREAK part. - Update comment about FILE_PER_FUNCTION in configuration file. - Fix NOT NULL constraint add twice, the first time in the column definition and the second time in an ALTER TABLE ... ADD CONSTRAINT ... CHECK ( ... NOT NULL). Reported by Dominique Legendre. - Add support to direct CALL of stored procedures in trigger definition. Reported by Dominique Legendre. - Remove index creation on primary and unique key autogenerated by PostgreSQL. - Fix PL/SQL to PLPGSQL automatic convertion on index when exporting data with DROP_INDEX activated. - Fix DROP_INDEX to only delete indexes that will be created at end. - Fix search path when exporting data with EXPORT_SCHEMA disabled - Add missing documentation about the LOGFILE directive - Fix case sensitivity on sequence export. They will now always be insensitive as in PostgreSQL its called is converted between quotes: nextval('seq_name'). Reported by Dominique Legendre. - Limit export of primary and unique key if KEEP_PKEY_NAMES is enabled to those who are not autogenerated by Oracle. Reported by Dominique Legendre. - Trigger export is now limited to those belonging to table that are not excluded from the export (see TABLES and EXCLUDE directives). Reported by Dominique Legendre - Fix case sensitivity on trigger export. - Fix data export failure in table with column name with accent. Reported by Dominique Legendre. - Fix set client_encoding syntax. Reported by Dominique Legendre. - Add automatic try with oracle sensitivity when an error occurs during retreving table information. This additionaly also fixes an error when table has accent on his name. - Fix replacement of user defined data type with directive DATA_TYPE. Reported by Dominique Legendre. - Fix function or procedure detection with external input file. Reported by Arul Shaji. - Update documentation about Windows installation and ActiveState Perl distribution. Thanks to Stephan Hilb for the report. - Fix date format issue by forcing NLS_DATE_FORMAT to format: YYYY-MM-DD HH24:MI:SS. Thanks to Stephan Hilb for the report. - Remove obsolete pod documentation in Ora2Pg.pm. - Add new configuration directive CREATE_SCHEMA to disable the sql command of schema creation at top of the output file during TABLE export type. Patch by David Greco. - Added converting INSERTING/UPDATING/DELETING to PG_OP='INSERT|UPDATE|DELETE'. Patch by David Greco. - Fix parsing leading ':' on triggers, as they generally have :NEW and :OLD to denote the new and old recordsets. Patch by David Greco - Add new PG_INTEGER_TYPE configuration directive activated by default, to limit conversion into postgresql integer or bigint of Oracle number without scale - NUMBER(p), PG_NUMERIC_TYPE is now reserved to convert NUMBER(p,s). Patch by David Greco. - Limit numeric with precision <= 9 to be converted as integer, numeric with precision >= 10 will be converted to bigint to handle integer above 2147483647. Patch by David Greco. - Add plsql to plpgsql automatic conversion on check constraints. Patch by David Greco. - Add plpgsql replacement, patch by David Greco: REGEX_LIKE( string, pattern ) => string ~ pattern. - Update documentation about NOESCAPE and STANDARD_CONFORMING_STRING - Change place of the ENABLE_MICROSECOND into the documentation. - Fix forgot to add documentation about encryption with Oracle server. - Add missing DISABLE_COMMENT configuraton directive in default configuration file and update documentation.
UPGRADE: all files have changed, you have to reinstall all. Please report any issue.
Version 9.0 was published today, Sunday July 15 2012
This is a major release, mainly because it adds full support to Windows OSes with a new source tree and Makefile.PL. But also because it fixes some long standing issue on CLOB and BLOB export. Some default behaviors on timestamp and table/column export have changed.- Remove call to obsolete BINDIR and MANDIR variables into packaging scripts to reflect the changes in Makefile.PL. - Update documentation about installation of Ora2Pg under Windows. - Automatically set LONGREADLEN to ORA_PIECE_SIZE length if the last one is larger, for CLOB export. - Change Makefile.PL and source tree to fully support installation under Windows OSes. - Change double quote by single in Makefile.PL perl replacement call. - Replace double quote by single one in $CONFIG_FILE default setting to simplify automatic replacement at install. - Fix CLOB export that was limited to 64Kb even with LONGREADLEN defined to an upper value. Patch use the ora_piece_size DBD::Oracle prepare attribute. Patch by Mohamed Gargouri. See here for more detail: http://search.cpan.org/~pythian/DBD-Oracle-1.46/lib/DBD/Oracle.pm#Piecewise_Fetch_with_Polling - Add a note into documentation about encrypted communication between Ora2Pg and Oracle. Note by Jenny Palomino. - Change documentation to reflect change to the format of the Oracle timestamp with millisecond. This format is now enabled by default in configuration file. - Fix bug with LONGREADLEN and LONGTRUNCOK when exporting LOB that was not applied even after change into the configuration file. Reported by Mohamed Gargouri - Fix microsecond format FF3 not compatible with Oracle 8i. Set to FF. - Add a warning to stderr when a table export need that ORA_SENSITIVE be enabled. - Fix case where Oracle indexes with same name as a constraint was not exported - Rodrigo The following are old patches that was not applied to v8.10 and the git repository: - Fix creation of bad constraint for each indexes. - Add DISABLE_COMMENT configuration directive to remove comments from TABLE export type. Comments are exported by default. - Fix a bug in removing function name repetion at end - Add PL/SQL to PLGPSQL replacement of the to_number function - Fix PL/SQL to PLGPSQL replacement of substr into substring - Add replacement of specials IEEE 754 values BINARY_(FLOAT|DOUBLE)_NAN and BINARY_(FLOAT|DOUBLE)_INFINITY by NaN and Infinity on PLPGSQL conversion and on data export - Thanks to Daniel Lyons. - Fix return type of function with just OUT or INOUT params. Thanks to Krasi Zlatev for the patch. - Add schema name on functions or procedures export when EXPORT_SCHEMA is activated. Thanks to Krasi Zlatev for the patch. - Fix case sensitivity issue with schema on partition export. - Fix case sensitivity issue with --xtable option. - Fix issues with case sensitivity on the schema owner set into the SCHEMA configuration directive. - Add default search_path on schema for contraints, index and data export when EXPORT_SCHEMA is activated. - Fix case sensitivity issue in search_path. - Force Oracle datetime format to be YYYY-MM-DD HH24:MI:SS.FF in client session to prevent other defined output format. Thanks to Aaron Culich for the patch. - Add export/import of table and column comment. Thanks to Krasi Zlatev for the patch. UPGRADE: all files have changed, you have to reinstall all. Please report any issue.
Version 8.13 is out, Tuesday June 26 2012
This quick release fix a major bug introduced in last release that could break export.- Fix broken export with missing single quote in Oracle timestamp export formating with to_char(timestampcolumn, YYYY-MM-DD HH24:mi:ss). Thanks to Steve Delong for the report. UPGRADE: If you don't want to reinstall all, just overwriting Ora2Pg.pm will be enough.
Version 8.12 published, Friday June 22 2012
This release adds a new export type to be used with oracle_fdw and two new configuration directive to pretty print XML data and to export milliseconds from Oracle timestamp column. There's also two bug fix with case sensitivity.- Add new configuration directive ENABLE_MICROSECOND to allow timestamp to be exported with millisecond precision. Thanks to Patrick King for the feature request. - Fix multiple quote on foreign keys column names. Thanks to Vitaliy for the report. - Add new export type FDW to allow table export as foreign table for oracle_fdw. Thanks to David Fetter for the feature request. - Fix typo in LongTruncOk variable name. Thanks to Magnus Hagander for the patch. - Add XML_PRETTY configuration directive to replace getStringVal() by getClobVal() when extracting XML data. Thanks to Magnus Hagander for the patch. - Fix case sensitivity issue in ALTER TABLE and TRUNCATE statement. Thanks to Magnus Hagander for the patch. UPGRADE: Ora2Pg.pm and ora2pg perl scripts have changed as well as configuration file. Documentation has been updated too so you'd better install all again.
Version 8.11 is out, Sunday March 11 2012
This release fixes a major bug in BLOB export and an error when trying to import directly against PostgreSQL with indexes and constraints.- Fix an error when running ora2pg directly against PG, index and constraints are created against PG instead of being written to the output file. Thanks to David Greco for the report. - Ora2Pg will now output a warning message when direct import to PG is set with other import type than COPY and DATA. - Fix NUL character removing on LOB to bytea export. Thanks to info31 on PostgresqlFr for the report. UPGRADE: If you don't want to reinstall all, just override Ora2Pg.pm from your Perl installation.
Version 8.10 is out, Sunday March 11 2012
This release fix a LOBs export issue with a data length upper than 1 Mbytes and two minor bugs.- Add two configuration directives to control the BLOB export. LONGREADLEN to set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected size of the LOB. LONGTRUNKOK to bypass the 'ORA-24345: A Truncation' error. Thanks to Dirk Treger for the report. - Fix install problem on non-threaded Perl and the threads package. Replace use() by require() call. Thanks to Ian Sillitoe for the patch. - Fix strange Oracle behaviour where binary_double infinity is exported from Oracle as '~'. Replaced by 'inf'. Thanks to Daniel Lyons for the UPGRADE: only Ora2Pg.pm have changed so you can just override it. See documentation for the new configuration directives: LONGREADLEN and LONGTRUNKOK.
Version 8.9 available, Monday November 07 2011
This release add support to XMLType data export and plsql to plpgsql replacement for Oracle function substr() and decode(). PL/SQL conversion can now be called during index extraction. There's also some major bugfixes on foreign key export and raise_application_error() rewrite.- Fix double quote into file name of package function export when case sensitivity is preserved. - Add support to XMLType data extraction. Thanks to Aaron Culich for the report. Before this release, xml data was exported as a Perl array reference. - Fix bug during foreign key export when foreign keys have different owners. Thanks to Krasi Zlatev for the patch. - Add support to plpgsql conversion during index extraction as many index use some Oracle function on their declaration. Thanks to Sriram Chandrasekaran fot the feature request. - PLSQL: Add replacement of Oracle subtr() by PostgreSQL substring(). Thanks to Sriram Chandrasekaran for the feature request. - PLSQL: Add replacement of Oracle decode() by PostgreSQL CASE/THEN/ELSE. Thanks to Sriram Chandrasekaran for the feature request. Note that this two replacements could break the code if there's complex subqueries inside their declaration. This is why you can enable it by setting ALLOW_CODE_BREAK to 1 (new). In later release this directive will be enable by default. - Add output ordering on some object name so that results between two runs can be compared. Thanks to Henk Enting for the patch. - Fix misshandling of all cases of RAISE_APPLICATION_ERROR rewrite into RAISE EXCEPTION concatenations. Thanks to Krasi Zlatev for the report. UPGRADE: only Ora2Pg.pm and Ora2Pg/PSQL.pm have changed so you can just override them if you dont want to reinstall all.
Talk at PgConf EU, Wednesday October 19 2011
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.