Skip to content
248152d Apr 21, 2016
@darold @dalibot
3074 lines (2736 sloc) 156 KB
2016 04 21 - v17.4
Errata in first release attempt.
- Fix previous patch that does not handle blob case but just clob
- Forgot to change back the query when EMPTY_LOB_NULL is not activated.
- Put parenthesis around AT TIME ZONE expression
This is a maintenance release to fix several issues reported by users.
There is also some major data export speed improvement thanks to the
work of PostgreSQL Pro and a new RPM spec file provided by Devrim
Gündüz to be able to build RPM package for Ora2Pg.
There is a new configuration directive:
- EMPTY_LOB_NULL: when enabled force empty_clob() and empty_blob()
to be exported as NULL instead as empty string.
Here is the complete list of other changes:
- Add EMPTY_LOB_NULL directive to force empty_clob() and empty_blob()
to be exported as NULL instead as empty string. This might improve
data export speed if you have lot of empty lob. Thanks to Alex
Ignatov for the report.
- Fix import_all.sh script to import grant and tablespace separately
as postgres user and just after indexes and constraints creation.
- Add parsing of tablespace from "alter table ... add constraint"
with DDL input file. Thanks to Felipe Lavoura.
- Remove --single-transaction in import_all.sh script with TABLESPACE
import. Thanks to Guillaume Lelarge for the report.
- Fix Makefile.PL to used with latest spec file from Devrim Gündüz
and following the advice of calbiston.
- Update spec file to v17.4 and latest change to Makefile.PL
- Replace ora2pg.spec by postgressql.org spec file by Devrim Gunduz.
- Generate man page to avoids rpmbuild error.
- Fix Windows install. Thanks to Lorena Figueredo for the report.
- Remove "deferrability" call for mysql foreign keys. Thanks to
Jean-Eric Cuendet for the report.
- Fix issue in restoring foreign key for mysql data export. Thanks
to Jean-Eric Cuendet for the report.
- Remove connection test to PostgreSQL instance as postgres or any
superuser in import_all.sh
- Fix creation of configuration directory.
- Fix Makefile to dissociate CONFDIR and DOCDIR from PREFIX or
DESTDIR. Thanks to Stephane Schildknecht for the report.
- Fix date_trunc+add_month replacement issue. Thanks to Lorena
Figueredo for the report.
- Do not replace configuration directory in scripts/ora2pg if this
is a RPM build. Thanks to calbiston for the report.
- Return empty bytea when a LOB is empty and not NULL.
- Regular expressions and conditions checks improvement in method
format_data_type() to make it a bit faster on huge tables. Thanks
to Svetlana Shorina for the patch.
- Fix INSERT using on the fly data import with boolean values.
Thanks to jecuendet for the report.
- Allow MySQL data type to be converted into boolean. Thanks to
jecuendet for the report.
- Fix export of BIT mysql data type into bit bit varying. Thanks
to jecuendet for the report.
- Fix call to escape_copy/escape_insert function call.
2016 03 26 - v17.3
This release fix two regressions introduced in latest release.
* Fix major bug in data export. Thanks to Frederic Guiet for the report.
* Fix another regression with character data that was not escaped. Thanks
to Frederic Guiet for the report.
2016 03 24 - v17.2
This is a maintenance release to fix several issues reported in new
LOB extraction method. There is also some feature improvement:
* Allow NUMBER with precision to be replaced as boolean.
* Allow full relocation of Ora2Pg installation using for
example: perl Makefile.PL DESTDIR=/opt/ora2pg
Here is the complete list of other changes:
- Allow NUMBER with precision to be replaced as boolean. Thanks
to Silvan Auer for the report.
- Force empty LOB to be exported as NULL when NO_LOB_LOCATOR is
activated to have the same behavior.
- Fix case where a LOB is NULL and ora2pg reports error :
DBD::Oracle::db::ora_lob_read: locator is not of type OCILobLocatorPtr
LOB initialised with EMPTY_CLOB() are also exported as NULL
instead of \\x
- Fix replacement with PERFORM after MINUS. Thanks to Stephane
Tachoires for the report.
- Comment DBMS_OUTPUT.ENABLE calls. Thanks to Stephane Tachoire for
the report.
- Fix wrong replacement of SELECT by PERFORM after EXCEPT. Thanks
to Stephane Tachoire for the report.
- Apply ORACLE_COPIES automatic predicate on custom queries set with
REPLACE_QUERY if possible. Thanks to pawelbs for the report.
- Fix install of ora2pg.conf file in /etc/ instead of /etc/ora2pg/.
Thanks to pawelbs for the report.
- Add debug information before searching for custom type.
- Attempt to fix error "ORA-01002: fetch out of sequence" when exporting
data from a table with user defined types and ORACLE_COPIES. Thanks to
pawelbs and Alex Ignatov fir the report.
- Fix replacement of path to configuration file in scripts/ora2pg
- Remove report sample from documentation about migration assessment
report and replace it with a href link. Fix comment about export of
domain index.
- Always prefix table name with schema in Oracle row count, to prevent
failure when the schema is not the connexion default.
- Add pattern TOAD_PLAN_.* to the internal table exclusion list.
- Fix modification of database owner search_path in import_all.sh auto
generated script. Thanks to Stephane Tachoire for the report.
2016 02 29 - v17.1
This is a maintenance release to fix several issues reported in new
TEST action. There is also some feature improvement:
* Add OPTIONS (key 'true') on table FDW export when a column is
detected as a primary key.
* Add DELETE configuration directive that allow a similar feature
than the WHERE clause to replace TRUNCATE call by a "DELETE FROM
table WHERE condition". This feature can be useful with regular
"updates". Thanks to Sebastien Albert for the feature request.
Here is the complete list of other changes:
- Fix the counter of user defined types and sequences in TEST action
- Fix COPY import of data from column with user defined type with
NULL value.
- Fix DBD::Pg segmentation fault with direct INSERT import from
column with user defined type.
- Fix TEST action with multiple PG_SCHEMA export. Thanks to Michael
Vitale for the report.
- Fix documentation about PG_SCHEMA
2016 02 22 - v17.0
This new major release adds a new action type TEST to obtain a count
of all objects at both sides, Oracle and PostgreSQL, to perform a
diff between the two database and verify that everything have been
well imported. It also fixes several issues reported by users.
A new ora2pg command line option have been added to ora2pg script:
* Add --count_rows command line option to perform a real row count
on both side, Oracle and PostgreSQL, in TEST report.
Here is the complete list of changes and bugfixes:
- Prefix direct call to function with a call to PERFORM. Thanks to
Michael Vitale for the feature request.
- Fix revoke call on function with multiline parameters declaration.
- Fix auto setting of internal schema variable with mysql.
- Define ORACLE_HOME with the corresponding environment variable in
generic configuration when available and --init_project is used.
Thanks to Stephane Tachoires for the report.
- Fix documentation about exporting view as table.
- Remove some obsolete code and display information when a view is
exported as table.
- Fix empty LOB data export with Oracle Lob locator (NO_LOB_LOCATOR
set to 0).
- Fix data export of partitions with single process mode and when
FILE_PER_TABLE is enabled.
- Fix export of RAW data type.
- Fix missing $ to call to self variable. Thanks to NTLIS and Sirko
for the report.
- Force FKey to be initially immediate when deferred is not set.
Thanks to Stephane Tachoire for the report.
- Fix count of check constraint when a schema is forced.
- Allow TEST action on mysql database too with some improvements
and bug fix on the feature.
- Fix index column renaming in mysql export.
- Fix dblink extraction query when an exclusion is set.
- Fix sequence name auto generation for mysql serial number.
- Add --count_rows command line option to make optional the real
row count in TEST report. This is useful when you have lot of
data and do not want to loose time in call to count(*).
- Update documentation about the TEST action and usage, see
chapter "Test the migration".
- Apply schema context on PostgreSQL side with TEST action.
- Add TEST action type to ask Ora2Pg to count rows and all objects
at both sides, Oracle and PostgreSQL, to verify that everything
have been well imported.
- Fix missing export of foreign keys on multiple columns, ex:
ALTER TABLE products ADD CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id,supplier_name)
REFERENCES supplier(supplier_id,supplier_name)...
- Fix import of BLOB data using INSERT statements into the bytea.
Thanks to rballer for the patch.
- Fix missing export of FK when no schema is provided.
2016 01 13 - v16.2
This release fixes several issues, is more accurates on migration
assessment report and adds some new ora2pg command line options:
* Add --pg_dsn, --pg_user and --pg_pwd to be able to set the
connection to import directly into PostgreSQL at command line.
* Add -f option to script import_all.sh to force to not check
user/database existing and skip their creation.
Potential backward compatibility issues:
* PG_SUPPORTS_CHECKOPTION is now enabled by default, you may want
to migrate to PostgreSQL 9.4 or above.
* Remove modification of CLIENT_ENCODING in generic configuration
file with --init_project, use the default instead.
* Remove modification of directive NLS_LANG to AMERICAN_AMERICA.UTF8
in generic configuration file with --init_project, use the default
instead.
Here is the complete list of other changes:
- Adjust DBMS_OUTPUT calls to the migration assessment count.
- Fix migration assessment count of call to cursor%ISOPEN and
cursor%ROWCOUNT.
- Replace zero date also with prepared statement with online
PostgreSQL import and INSERT action. Thanks to Sebastian Albert
for the report.
- Remove REFERENCING clause in conditional triggers. Thanks to Raqua
for the report.
- Fix position of TG_OP condition when an exception is defined.
Thanks to Raqua for the report.
- Fix wrong replacement of SELECT with PERFORM when a comment was
found between an open parenthesis and the select statement.
Thanks to Raqua for the report.
- Fix procedure return type with OUT and INOUT parameter. Thanks to
Raqua for the report.
- Fix rewrite of triggers with referencing clause. Thanks to Raqua
for the report.
- Fix default number of --human_days_limit in usage.
- Fix replacement of placeholder %TEXTVALUE-d% to hide text string
in query during function call rewrite. Thanks to Lorena Figueredo
for the report.
- Fix progress bar when a WHERE clause is used to limit the number
of row to export.
- Fix error "DBD::Pg::db do failed: SSL error: decryption failed or
bad record mac" with pararellel table export (-P) and direct
import to PostgreSQL via a ssl connection. Thanks to pbe-axelor
for the report.
- Fix missing index name in indexes creation. Thanks to Raqua for
the report.
- Fix pg DSN in import_all.sh autogenerated script.
- Fix extraction of trigger. When the name of a column or something
contained INSERTING, DELETING or UPDATING was converted to TG_OP
= 'INSERT' or corresponding event. Thanks to Stanis¿aw Jankowski
for the patch.
- Fix multiple use of same column in check constraint and indexes
of partitions when there was several schema with the same objects.
- Fix default value for HUMAN_DAY_LIMIT to 5 when it is not defined
in ora2pg.conf.
- Fix double quote on column name in COPY export of partition tables
Thanks to Chris Brick for the report.
- Prevent case with several time same column in multicolumns unique
constraints. Fix typo in previous patch.
- Fix double quoted name with auto incremented sequence exported as
serial.
- Fix syntax error with MySQL data export with a WHERE clause using
LIMIT.
2015 11 30 - v16.1
This release fixes several issues and adds some very useful features:
* Generate automatically a new import_all.sh shell script when using option
--init_project to help automate all import into PostgreSQL.
See sh import_all.sh -? for more information.
* Export Oracle bitmap index as PostgreSQL btree_gin index. This require the
btree_gin extension and PostgreSQL >= 9.4. This is the default.
* Auto set DEFINED_PK to the first column of a table that have a unique key
defined that is a NUMBER. This allow data of any table with a numeric
unique key to be extracted using multiple connexions to Oracle using -J
option. Tables with no numeric unique key will be exported with a single
process.
* Improve BLOB export speed by using hex encoding instead of escape. This
might speed up be BLOB export by 10.
* Allow use of LOB locator to retrieve BLOB and CLOB data to prevent having
to set LONGREADLEN. Now LONGREADLEN is set to 8KB. Old behavior using
LONGREADLEN can still be enabled by setting NO_LOB_LOCATOR to 0, given
for backward compatibility. Default is to use LOB locator.
* Ora2Pg will also auto detect table with BLOB and automatically decrease
DATA_LIMIT to a value lower or equal to 1000. This is to prevent OOM.
* Improving indexes and constraints creation speed by using the LOAD action
and a file containing SQL orders to perform. It is possible to dispatch
those orders over multiple PostgreSQL connections. To be able to use this
feature, PG_DSN, PG_USER and PG_PWD must be set. Then:
ora2pg -t LOAD -c config/ora2pg.conf -i schema/tables/INDEXES_table.sql -j 4
will dispatch indexes creation over 4 simultaneous PostgreSQL connections.
This will considerably accelerate this part of the migration process with
huge data size.
* Domain indexes are now exported as b-tree but commented to let you know
where possible FTS are required.
* Add number of refresh ON COMMIT materialized view in detailed report.
* Allow redefinition of numeric type, ex: NUMBER(3)::bigint to fix wrong
original definition in Oracle.
* Allow export of all schemas from an Oracle Instance when SCHEMA directive
is empty and EXPORT_SCHEMA is enabled. All exported objects will be
prefixed with the name of their original Oracle schema or search_path will
be set to that schema name. Thanks to Magnus Hagander for the feature
request.
* Allow use of COPY FREEZE to export data when COPY_FREEZE is enabled. This
will only works with export to file and when -J or ORACLE_COPIES is not
set or default to 1. It can be used with direct import into PostgreSQL
under the same condition but -j or JOBS must be unset or default to 1.
Thanks to Magnus Hagander for the feature request.
Some new configuration directives:
* BITMAP_AS_GIN: enable it to use btree_gin extension to create bitmap
like index with pg >= 9.4. You will need to create the extension by
yourself: "create extension btree_gin;". Default is to create GIN index,
when disabled, a btree index will be created.
* NO_LOB_LOCATOR: to disable use of LOB locator and extract BLOB "inline"
using a less or more high value in LONGREADLEN.
* BLOB_LIMIT: to force the value of DATA_LIMIT for tables with BLOB. Default
is to automatically set this limit using the following code:
BLOB_LIMIT=DATA_LIMIT; while (BLOB_LIMIT > 1000) BLOB_LIMIT /= 10
* COPY_FREEZE: use it to use COPY FREEZE instead of simple COPY to speedup
import into PostgreSQL.
Here is the complete list of other changes:
- Limite package function name rewrite to call with parenthesis after the
function name to avoid rewriting names of other objects.
- Fix extra replacement of function name with package prefix. On some
condition it was done multiple time.
- Set REPLACE_ZERO_DATE to -INFINITY in generic configuration when --mysql
is enabled.
- Fix extraction of partition with MySQL that was not limited to a single
database.
- Do some replacement on ORACLE_DNS and SCHEMA into generic configuration
when --mysql is used for better understanding.
- Add call to round() on -J parallelization when the auto detected column
is a numeric with scale.
- Add COMMIT to the difficulties migration assessment keywords as it need
context analyzing.
- Add call to cursor's %ROWCOUNT, %ISOPEN and %NOTFOUND to difficulties
migration assessment keywords.
- Replace call to CURSOR%ROWTYPE by RECORD. Thanks to Marc Cousin for the
report.
- Fix ALTER FUNCTION ... OWNER TO ... and REVOKE statement where functions
parameters were missing.
- Add Get_Env to the Oracle functions list for migration assessment.
- Disable variable NO_LOB_LOCATOR and set LONGREADLEN to 8192 bytes to use
LOB locators to extract BLOB in generic configuration file.
- Fix call method "disconnect" on unblessed reference at line 9998. Thanks
to Stephane Tachoires for the report.
- Exclude from export objects name matching /.*\$JAVA\$.*/ and /^PROF\$.*/.
- Fix migration assessment report when created during the package export.
- Force writing Oracle package body in separate files when FILE_PER_FUNCTION
is enabled and PLSQL_PGSQL disable to obtain package source code.
- Fix case where sequence max value is lower than start value, in this case,
set max value = start value.
- Fix missing newline after each package file to import in global package.sql
file when FILE_PER_FUNCTION is enabled.
- Remove export of user PUBLIC in GRANT export.
- Set DISABLE_TRIGGERS to 1 in generic configuration file auto generated when
ora2pg option --init_project is used.
- Remove call to quote_reserved_words() with index column when it we detect
a function based index, too much false positive are rewritten with SQL code
like CASE...WHEN.
- Update export_schema.sh to remove .sql files when there is not such object
leaving export directory empty.
- Prevent creating TBSP_INDEXES_tablespace.sql when no tablespaces are found
- Update documentation on WHERE clause on how to limit the number of tuples
exported for Oracle and MySQL to test data import.
- Fix unlisted spatial indexes in assessment report.
- Fix double quote on index name with index renaming and reserved keyword.
- Do not try to export tablespaces, privileges and audited queries as non DBA
user when USER_GRANT is enabled.
- Remove carriage return from list file.
- Force SCHEMA to database name with MySQL migration.
- Fix missing declaration of _extract_sequence_info(). Thank to Yannick DEVOS
for the report.
- Add documentation about COPY_FREEZE directive and add a note about export
of all schema.
- Remove systematic schema name appended to table name on KETTLE export, this
must only be true when EXPORT_SCHEMA is enabled.
- Fix TO_NUMBER() wrong replacement when a function is called as a parameter.
- Fix non converted DECODE() when they was called in an XMLELEMENT function.
- Suppress MDSYS.SDO_* from MDSYS call in migration assessment cost.
- Remove use of DBMS_STANDARD called with raise_application_error function
- Fix STRING type replacement
- Recreate README as a text file, not a man page.
- Reformat changelog to 80 characters.
- Add -t | --test command line option to ora2pg_scanner to be able to test
all connections defined into the CVS list file.
2015 10 15 - v16.0
This major release improve PL/SQL code replacement, fixes several bugs and
adds some major new features:
* Full migration of MySQL database, it just work like with Oracle database.
* Full migration assessment report for MySQL database.
* New script, ora2pg_scanner, to perform a migration assessment of all
Oracle and MySQL instances on a network.
* Add technical difficulty level in migration assessment.
* Allow migration assessment on client queries extracted from AUDIT_TRAIL
(oracle) or general_log table (mysql).
* Ora2Pg has a "made in one night" brand new Web site (still need some work).
See http://ora2pg.darold.net/
Example of technical difficulty level assessment output for the sakila database
with some more difficulties:
Total 83.90 cost migration units means approximatively 1 man-day(s).
Migration level: B-5
Here are the explanation of the migration level code:
Migration levels:
A - Migration that might be run automatically
B - Migration with code rewrite and a human-days cost up to 10 days
C - Migration with code rewrite and a human-days cost above 10 days
Technical levels:
1 = trivial: no stored functions and no triggers
2 = easy: no stored functions but with triggers, no manual rewriting
3 = simple: stored functions and/or triggers, no manual rewriting
4 = manual: no stored functions but with triggers or views with code
rewriting
5 = difficult: stored functions and/or triggers with code rewriting
This is to help you to find the database that can be migrated first with small
efforts (A and B) and those who need to conduct a full migration project (C).
This release has also some new useful features:
* Export type SHOW_TABLE now shows additional information about table type
(FOREIGN, EXTERNAL or PARTITIONED with the number of partition).
* Connection's user and password can be passed through environment variables
ORA2PG_USER and ORA2PG_PASSWD to avoid setting them at ora2pg command line.
* Improve PL/SQL replacement on ADD_MONTH(), ADD_YEAR(), TRUNC(), INSTR() and
remove the replacement limitation on DECODE().
* Add detection of migration difficulties in views, was previously reserved
to functions, procedures, packages and triggers.
* Replace values in auto generated configuration file from command line
options -s, -n, -u and -p when --init_project is used.
* Adjust lot of scores following new functionalities in Ora2Pg, ex: dblink or
synomyms are now easy to migrate.
There is some new command line options to ora2pg script:
* -m | --mysql : to be used with --init_project and -i option to inform
ora2pg that we work with a MySQL format
* -T | --temp_dir : option to be able to set a distinct temporary directory
to run ora2pg in parallel.
* --audit_user : option to set the user used in audit filter and enable
migration assessment report on queries from AUDIT_TRAIL (oracle) or
general_log table (mysql).
* --dump_as_sheet and --print_header options to be able to compute a CSV
file with all migration assessment from a list of oracle database.
* --dump_as_csv option to report assessments into a csv file. It will not
include comments or details, just objects names, numbers and cost.
Backward compatibility:
- Change NULL_EQUAL_EMPTY to be disabled by default to force change in the
application instead of transforming the PL/SQL.
This release adds some new configuration directives:
* MYSQL_PIPES_AS_CONCAT: Enable it if double pipe and double ampersand
(|| and &&) should not be taken as equivalent to OR and AND.
* MYSQL_INTERNAL_EXTRACT_FORMAT: Enable it if you want EXTRACT() replacement
to use the internal format returned as an integer.
* AUDIT_USER: Set the comma separated list of user name that must be used
to filter from the DBA_AUDIT_TRAIL or general_log tables.
* REPLACE_ZERO_DATE: "zero" date: 0000-00-00 00:00:00 it is replaced by a
NULL by default, use it to use the date of your choice.
* INDEXES_RENAMING: force renaming of all indexes using tablename_columnsname
Very useful for database that have multiple time the same index name or
that use the same name than a table.
* HUMAN_DAYS_LIMIT: default to 5 days, used to set the number of human-days
limit for migration of type C.
Here is the full list of other changes:
- Remove list of acknowledgment that was not maintained anymore and some
person may feel injured. Acknowledgment for patches or bug reports are
always written to changelog, so this part reports to it now.
- Fix bad trigger export when objects was enclosed in double quote and fix
an additional bug in WHEN clause export. Thanks to Cyrille for the report.
- Update documentation.
- Update Makefile.PL with new script to install and new configuration
directives in auto generated configuration file.
- Update with new and missing files.
- Add a Perl Module dedicated to MySQL database object discovery and export,
lib/Ora2Pg/MySQL.pm.
- Fix function based index type replacement in previous commit.
- Do not report indexes with just DESC as function based index like Oracle
report it. Thanks to Marc Cousin for the report.
- Some excluded table was missing in the previous patch.
- Remove use of DBI InactiveDestroy call when a fork is done and replace it
to a single use AutoInactiveDestroy at connection. This require DBI>=1.614.
- Add SDO_* tables and OLS_DIR_BUSINESSES in table exclusion list to fix issue
#124 when no schema is provided. Thanks to Kenny Joseph for the report.
- Fix partition prefix.
- Remove UNIQUE keyword from spatial index.
- Fix alter triggers function with missing parenthesis. Thanks to Spike Hodge
MWEB for the report.
- Fix export of foreign keys when they was defined in lowercase. Thanks to
Spike for the report.
- Fix wrong offset when rewriting ROWNUM with LIMIT+OFFSET. Thanks to Marc
Cousin for the report.
- Allow -INFINITY to be used to replace zero date.
- Migration assessment in hour-day are now set to 1 man-day, we do not need
such a precision and it is easier to process csv report. Thanks to Stephane
Tachoire for the report.
- Fix some issue with FDW and WKT spatial export. Add migration assessment
of queries from the AUDIT_TRAIL table.
- Adjust assessment units of some objects and add QUERY migration weight.
- Rewrite information about migration levels.
- Fix speedometer in progress bar, it will now shows the current speed in
tuples/sec and the speed and time related to a table when export ended for
the object. Thanks to Alex Ignatov for the report.
- Fix break line when export data using INSERT mode. Thanks to Vu Bui for
the report.
- Do not display line about non existent objects in migration assessment
reports.
- Fix date default value for date when value is 0000-00-00 00:00:00
- Suppress display of title for function and trigger details when there is
no details.
- Remove INSTR() from the list of Oracle function that are not supported.
It is now replaced by position().
- Fix condition to call _get_largest_tables().
- Fix some minor issues in OUT/INOUT type returned by a function.
- Fix default value that may appears unquoted.
- Fix several issues on partition export: column with function, index on
default partition table and plsql to plpgsql translation in check condition.
- Fix some minor issues.
- Replace values from command line options -s, -n, -u and -p in --init_project
auto generated configuration file. Thanks to Stephane Tachoire for the
feature request.
- Fix wrong object count in SHOW_REPORT. Thanks to Stephane Tachoire for
the report.
- Use DBA_SEGMENTS to find database size when USER_GRANT is disable, aka user
is a DBA
- Remove report of Migration Level when --estimate_cost is not enabled.
- Add missing BINARY_INTEGER for type replacement.
- Always exclude function squirrel_get_error_offset() that is created by the
Squirrel Oracle plug-in.
- Adjust assessment scores following new functionalities in Ora2Pg, ex:
autonomous transaction, dblink or synomyms are now easy to migrate.
- Remove man page from source, it is auto generated by Makefile.PL and make.
- Fix unterminated DECODE replacement when there was more than 5 parameters
to DECODE() and remove the limitation to 10 parameters. There is no more
limit in the number of decode parameters. Thanks to Mael Rimbault for the
report.
- Remove inclusion of unwanted object when exporting a limited list of view
with ALLOW.
- Disable unsupported recursive query used to reorder views when Oracle
version is 11gR1. Thanks to Mael Rimbault for the patch.
- Add PLPGSQL replacement of INSTR() by POSITION(). Thanks to Mael Rimbault
for the report.
- Add difficulty level information in migration assessment, this include a
new configuration directive HUMAN_DAYS_LIMIT (default to 5 days) to set
the number of human-days limit for migration of type C.
- Add MERGE with a migration cost of 3, still need work be replaced by
ON CONFLICT.
- Remove some redundant regular expressions.
- Fix escaped commas not working properly in MODIFY_TYPE. A MODIFY_TYPE
value like `TABLE1:COL4:decimal(9\,6)` was leading to a column like
`col4 decimal(8#nosep#3)` in the SQL dump file that was generated. This
fixes the output to be `col4 decimal(8,3)`. Thanks to Nick Muerdter for
the patch.
- Strip default "empty_clob()" values from table dumps. This function does
not exist in Postgres and is not necessary. Thanks to Nick Muerdter for
the patch.
- Fix undesired double quoting of column name in function based indexes.
- Fix issue with Perl < 5.8 "Modification of a read-only value attempted"
- Fix retrieving of table size on Oracle 8i.
- Add auto double quoting of object name with unauthorized characters.
Thanks to Magnus Hagander for the feature request.
- Automatically double quote object name beginning with a number
- Fix missing DESC part in descending indexes. Thanks to Magnus Hagander
for the report.
- Fix case where a column name in oracle is just a number (e.g. the column
is called "7"), it will be created in postgres without quoted identifier,
which fails. Thanks to Magnus Hagander for the report.
- Fix "reqs/sec" display in debug mode. Thanks to Laurent Martelli for
the patch
- Fix export if Oracle procedure is created without a parameter. Thanks to
dirkgently007 for the report.
- Fix CSV report output.
- Fix triggers from file parser.
- Add a test on triggers return to handle case where it is triggered on
DELETE + other(s) event(s). In this case a test is done on the TG_OP to
return OLD if event is DELETE or NEW in other case. Thanks to Dominique
Legendre for the suggestion.
- Change NULL_EQUAL_EMPTY to be disabled by default to force change of the
application instead of transforming the PL/SQL.
- Change score of SYNONYM and DBLINK in the migration assessment.
- Add conversion of Oracle type STRING into varchar(n) or text.
- Add information about libaio1 requirement for instant client
- Remove extra space when calling ora2pg_get_efile() used to export BFILE
into EFILE. Thanks to Dominique Legendre for the export.
2015 06 01 - v15.3
This is a maintenance release only that fixes several minor bugs and typos.
The configuration file have been entirely rewritten to classify configuration
directives in section for better understanding.
Here is the full list of changes:
- Ora2Pg will use DEFAULT_SRID when call to sdo_cs.map_oracle_srid_to_epsg()
returns an ORA-01741 error. Mostly because there's no SRID defined for that
column in ALL_SDO_GEOM_METADATA. The error message will still be displayed
but a warning will explain the reason and ora2pg will continue with default
value. Thanks to kazam for the report.
- Add current setting for NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT to the
SHOW_ENCODING report.
- Change default value for GEOMETRY_EXTRACT_TYPE to INTERNAL instead of WKT.
- Change generic configuration file behavior with BINMODE parameter commented
if it was previously uncommented. This will force to use the default value.
- Fix potential issue with max open file limit with unclosed temporary file.
Thanks to Marc Clement for the report.
- Fix use of SECURITY DEFINER in SYNONYM export.
- Fix parsing of editable function/procedure/package from input DML file.
- Fix case where variable $2 and $3 was null after a too early call of a new
substitution in read_view_from_file(). Thanks to Alex Ignatov for the patch.
- Add support to "create or replace editionable|noneditionable" from input DML
files. Thanks to Alex Ignatov for the report.
- Fix unknown column HIGH_VALUE from *_TAB_PARTITIONS in Oracle 8i. Thanks to
Sebastian Fischer for the patch.
- Fix call to ALL_MVIEW_LOGS object which not exists with Oracle 8i. Thanks to
Sebastian Fischer for the report.
- Fix Error ORA-22905 while Get the dimension of the geometry by looking at
number of element in the SDO_DIM_ARRAY. Thanks to jkutterer for the patch.
- Remove reordering export of view for Oracle database before 11g. Thanks to
kyiannis for the report.
- Fix several some typos and a bunch of misspelled. Thanks to Euler Taveira
for all the patches.
- Fix missing Oracle database version before looking at function security
definer. Thanks to kyiannis for the report.
2015 04 13 - v15.2
This new minor release fixes some issues and adds two new configuration
directives:
* ORA_INITIAL_COMMAND to be able to execute a custom command just after
the connection to Oracle, for example to unlock a security policy.
* INTERNAL_DATE_MAX to change the behavior of Ora2Pg with internal date
found in user defined types.
This version will also automatically re-order exported views taking into
account interdependencies.
Here is the full list of changes:
- Add INTERNAL_DATE_MAX configuration directive with default to 49 to be
used when reformatting internal date returned with a user defined type
and a timestamp column. DBD::Oracle only return the internal date format
01-JAN-77 12.00.00.000000 AM so it is difficult to know if the year value
must be added to 2000 or 1900. We takes the default behavior where date
are between 1950 and 2049.
- Remove extra CHAR and BYTE information from column type. Thanks to Magnus
Hagander for the report.
- Re-order views taking into account interdependencies. Thanks to Kuppusamy
Ravindran and Ulrike for the suggestion and the Oracle query.
- Fix case sensitivity in function based indexes. Thanks to Kuppusamy
Ravindran for the report.
- Fix PERFORM wrong replacement and infinite loop processing DECODE in some
condition. Thanks to Didier Brugat for the report.
- Fix replacement of boolean value in DEFAULT value at table creation.
Thanks to baul87 for the report.
- Add ORA_INITIAL_COMMAND configuration directive to be able to execute a
custom command just after the connection to Oracle, to unlock a policy for
example. Thanks to Didier BRUGAT for the feature request.
- Fix alias in from clause when an XML type is found. Thanks to Lance Jacob
for the record.
- Invert condition on excluding temporary file with Windows OS. Thanks to
kazam for the report.
- Remove start time and global number of rows from _dump_table() parameters
they are not used anymore.
- Remove use of temporary file on Windows operating system.
- Disable parallel table export when operating system is Windows.
- Fix export of objects with case sensitivity using ALLOW or EXCLUDE
directives. Thanks to Alexey Ignatov for the report.
- Fix export of triggers from recycle bin.
- Fix count of synonym in assessment report.
- Add list of tables created by OEM to the exclusion list.
- Fix look at default configuration file and set mode of export_schema.sh
to executable by default. Thanks to Kuppusamy Ravindran for the report.
- Add AUTHORIZATION to the list of PostgreSQL reserved word. Thanks to
Kuppusamy Ravindran for the report.
- Display a warning when an index has the same name as the table itself so
that you can renamed it before export. Thanks to Kuppusamy Ravindran for
the feature request.
- Fix export of function based indexes with multiple column. Thanks to
Kuppusamy Ravindran for the report.
- Modify ora2pg script to return 0 on success, 1 on any fatal error and 2
when a child process die is detected.
- Change the way the generic configuration file is handle during project
initialization. You can use -c option to copy your own into the project
directory. If the file has the .dist extension, ora2pg will apply the
generic configuration on it. Thanks to Kuppusamy Ravindran for the report
and features request.
- Add debug information when cloning the Oracle connection.
- Force return of OLD when the trigger is on DELETE event
2015 02 06 - v15.1
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.
2015 02 04 - v15.0
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.
2014 11 12 - v14.1
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.
2014 11 05 - v14.0
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.
2014 06 02 - v13.0
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.
2014 01 28 - v12.1
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.
2013 10 22 - v12.0
This release fixes lot of issues and 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 with native PostgreSQL 9.3 syntaxe. The USE_TABLESPACE variable
will allow you 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.
2013 05 28 - v11.4
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.
2013 05 27 - v11.3
This release fixes several major issues on migration cost assessment, especialy
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.
2013 05 01 - v11.2
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.
UPGRADE: please reinstall all as most of the files have changed.
2013 04 07 - v11.1
This release adds partition data speed improvement by exporting data directly
from and into the destination partitioned table. 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.
2013 03 24 - v11.0
This is a new major release because it adds support to multiprocessing to export
data in parallel mode, this allow to improve speed during data import by more
than ten times. This multiprocessiing capabilities allow Ora2Pg to be closer than
the speed of any ETL. To compare speed or allow using Kettle for data import,
there's now a new export type to obtain Kettle XML transformation files. This
release adds also lot of work on speed improvement to scan Oracle database with
huge number of object.
- 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 preserve.
- 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.
2013 01 15 - v10.1
This release adds HTML report for migration cost assessment and some bug fix.
- Fix global where 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.
2012 12 12 - v10.0
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:
- 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.
2012 10 07 - v9.3
- 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 materialized
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 materialized views
as snapshot materialized 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.
2012 09 05 - v9.2
- 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.
2012 08 19 - 9.1
- 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¿, etc.
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
2012 07 15 - 9.0
- 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.
2012 06 26 - 8.13
- 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.
2012 06 22 - 8.12
- Add nex 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.
2012 04 30 - 8.11
- 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.
2012 03 11 - 8.10
- 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
report.
UPGRADE: only Ora2Pg.pm have changed so you can just override it. See also
documentation for new configuration directives: LONGREADLEN and LONGTRUNKOK.
2011 11 07 - 8.9
- 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 fot the feature request.
- PLSQL: Add replacement of Oracle decode() by PostgreSQL CASE/THEN/ELSE.
Thanks to Sriram Chandrasekaran fot the feature request.
Note that this two replacement was not implemented because they 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.
2011 10 13 - 8.8
- 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.
2011 09 07 - 8.7
- 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 controlled 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.
- Update web site documentation.
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.
2011 07 07 - 8.6
- 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 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 1,
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 and Ora2Pg/PGSQL.pm 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.
2011 07 01 - 8.5
- 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 enabled by default now.
UPGRADE: The new features has changed Ora2Pg.pm and ora2pg.pl so that they must
be overwritten. There's also changes in the configuration file and documentation
has changed as well. Take care of backward compatibility with escaped strings in
DATA export type and the new behavior on PACKAGE export.
2011 06 07 - 8.4
- 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.
UPGRADE: There's no new functionality, this is a bug fix release.
2011 05 11 - 8.3
- 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.
UPGRADE: There's no new functionality, this is a bug fix release and every one
should upgrade to it.
2011 05 01 - 8.2
- PLSQL: automatic replacement of EXIT WHEN cursor%NOTFOUND; by Pg
synthax: IF NOT FOUND THEN EXIT; END IF;. Works with additional
condition too.
- PLSQL: 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 it can return: 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.
- PLSQL: 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.
2011 03 28 - 8.1
- 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.
UPGRADE: Please make a full upgrade asap to this release.
2011 03 15 - 8.0
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() in Pg syntax
- PLSQL : Convert call to Oracle function add_years() in 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 as it is supported by with modern
PG version (Pg >= 8.4).
- Remove obsolete PG_SUPPORTS_DEFAULT as it is supported by with modern
PG version (Pg >= 8.4).
- 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 Pg
parser unhappy.
- PLSQL: Replace SQLCODE by SQLSTATE
UPGRADE: You must reinstall all and review your configuration file
2011 02 14 - 7.3
- 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.
2011 01 12 - 7.2
- Fix escaping 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 functions parameter (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), can be activated
with a new configuration directive: PG_SUPPORTS_WHEN.
- Add support to INSTEAD OF usage on triggers (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.
2010 12 04 - 7.1
- 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.
2010 11 23 - 7.0
- 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.
- Improve data export speed by 6! The data export code has been entierly
rewritten and the speed gain is really fun.
- 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.
2010 09 10 - 6.4
- Configuration directives SHOWTABLEID, MIN and MAX are now obsolete
and has been definitively removed. They were never used and add too
much confusion.
- Fix bug in column name replacement where table name was also replaced.
Thank to Jean-Paul Argudo from DALIBO for the report.
- Fix case sensitive errata in PG schema search path. Thank to Jean-Paul
Argudo from DALIBO for the report.
- Remove double \n at end of debug message.
- Fix debug mode not activated if the DEBUG directive is enable and
the -d command line is not present.
- Add unbuffered output for debug message.
UPGRADE: simply override the Ora2Pg.pm Perl module where it is installed.
2010 07 22 - 6.3
- Fix Oracle 8i compatibility error during schema extraction complaining
that column CHAR_LENGTH doesn't exist. Thanks to Philippe Rimbault for
the report. Note that the error message is still displayed but tagged
as WARNING only.
- Fix error using the IMPORT option on a read_conf method call. Thanks
to Diogo Biazus for the report.
- Fix export of sequences that does not handle maxvalue well and can be
lower than minvalue. Thanks to Nathalie Doremieux for the report.
UPGRADE: Just override Ora2Pg.pm
2010 06 15 - 6.2
- Change default transaction isolation level from READ ONLY to
SERIALIZABLE to ensure consistency during data export. Thanks to
Hans-Jürgen Schönig from postgresql-support.de
- Add the TRANSACTION configuration directive to allow overriding of
the isolation level. Value can be readonly, readwrite, committed and
serializable. The last is the default.
2010 05 07 - 6.1
- Fix error on partition export following schema definition.
- Add first support to export Oracle user defined types.
- Add CTXSYS,XDB,WMSYS,SYSMAN,SQLTXPLAIN,MDSYS,EXFSYS,ORDSYS,DMSYS,
OLAPSYS to the sysuser default exclusion list.
- PLSQL.pm: Add automatic translation of Oracle raise_application_error
and dup_val_on_index to PG RAISE EXCEPTION and UNIQUE_VIOLATION.
- Change/fallback to a lower case package name (ora2pg-6.x.tar.gz).
- Change default convert type for 'LONG RAW' to bytea.
- Add PG_SCHEMA configuration directive to defined a coma delimited
list of schema to use in SET search_path PostgreSQL command.
2010 02 28 - 6.0
- Publish a dedicated site to Ora2Pg at http://ora2pg.darold.net/
- Add export of Oracle table partitoning. See export type PARTITION.
- Add command line arguments to perl script ora2pg. See --help for a
full listing of these option. The most interesting is --type to change
the export type directly at command execution without needing to edit
the configuration file, --plsql to directly enable PLSQL to PLPSQL
code conversion and --source, --user --password to set Oracle data
source. There's also --namespace to set the Oracle schema.
- Create all file for standard Perl module install. Install is now done
with: perl Makefile.PL && make && make install
- Move Ora2Pg license from Perl Artistics to GPLv3.
- Move PLSQL package as Ora2Pg::PLSQL for standard Perl module install.
- Remove use of Perl module String::Random.
- Rename program ora2pg.pl into ora2pg for standard usage.
- Fix extra double quote on column name of index export. Thanks to
Guillaume Lelarge for the patch.
- Add packaging facilities to build RPM, SlackBuild and Debian packages.
- Fix miss handling of Ora2Pg.pm options at object instance init.
- Configuration file ora2pg.conf is now generated by Makefile.PL
2009 12 18 - 5.5
- Fix CONSTANT declaration in Procedure/Function/Package export.
- Fix length of char and varchar on multibyte like UTF8 encoding. Thanks
to Ali Pouya for the patch.
- Fix view export where alias to column in Oracle not use 'AS' and
PostgreSQL required it. Thanks to Ali Pouya for the report.
- Add type replacement of sql variable in PLSQL code (PLSQL.pm). Thanks
to vijay for the patch.
2009 07 15 - 5.4
- Fix bug introduced in multiple output file feature. This bug force
Ora2pg to crach after the first table export when output is wanted in
a single file. Thanks to Leo Mannhart for the report.
- Fix debug filename output on multiple export file. Thanks to Leo
Mannhart for the report.
2009 07 07 - version 5.3
- Fix wrong escaping of data named as column during view export. Thank
to Andrea Agosti for the patch.
- Allow export of datas into one file per table. See FILE_PER_TABLE
configuration directive. Thanks to Alexandre - Aldeia Digital for the
idea.
2009 06 19 - version 5.2
- Fix order of the column name of the view which was not preserved. Now
ordered by COLUMN_ID. Thank to Andrea Agosti for the report.
- Fix case sensitivity in VIEW extraction. Thank to Andrea Agosti for
the patch.
2009 03 06 - version 5.1
- Fix missing -U username at Pg connection. Thanks to Brendan Richards.
- Fix $ENV{ORACLE_HOME} and $ENV{NLS_LANG} to not being overwritten
by configuration settings if they are already defined in environment.
- Fix typo in ora2pg.pl where keep_pkey_names was replaced by
keep_pkeys_name and so prevent use of KEEP_PKEY_NAMES in configuration.
Thanks to Olivier Mazain for the report.
- Configuration file directives are now case insensitive.
- Force $type parameter given to _sql_type() to be uppercase in that
methode instead of during function call. Thanks to Ali Pouya for the
report.
- Modify ora2pg.pl to remove the obsolete call to export_data(). Use
only export_schema() now.
- Modify ora2pg.pl to simplify it. Reading configuration is now done
internally by Ora2Pg.pm as well as all other initialization process.
You can always overwrite all configuration options into call to new
Ora2Pg(). Now ora2pg.pl can be as simple as:
use Ora2Pg;
my $schema = new Ora2Pg('config' => "/etc/ora2pg.conf");
$schema->export_schema();
exit(0);
This will be less confusing. You can upgrade Ora2Pg.pm without carring
about that, backward compatibility with previous version is preserved.
- Review entire documentation with the great help of Ali Pouya.
- Add type BOOLEAN converted to boolean.
- PG_SUPPORTS_INOUT is now enabled by default in the configuration file
- SQL and PL/SQL to PLPGSQL converter:
.Replace MINUS call to EXCEPT
.Replace DBMS_OUTPUT.put, DBMS_OUTPUT.put_line, DBMS_OUTPUT.new_line
by the PLPGSQL equivalent: RAISE NOTICE
.Rewrite function/procedure/package convertion functions.
This Oracle SQL converter for function/procedure/package is now only
applied if the configuration directive PLSQL_PGSQL is enable, else
these Oracle code are exported as is. Thanks to Ali Pouya for the help.
- Reserved call to sql transaction only for DATA export type. Others
export type now use \set ON_ERROR_STOP ON. Thanks to Ali Pouya.
- Fix tablespace creation into schema (missing search_path). Thanks to
Olivier Mazain.
- Fix the type returned by the _sql_type() method in the case of a
numeric with null len and pg_numeric_type is set. Thanks to Ali Pouya.
- Change function body delimiter to $body$ to allow use of $$ into the
body as quote replacement. Thanks to Ali Pouya.
- Fix returns type from function. If multiple OUT parameters: RECORD,
if only one OUT parameter, return his type. If no out parameter: return
VOID. Thanks to Ali Pouya.
- Fix export DATA when the name of a column in the table match COMMENT,
AUDIT or any other defined reserved words. These reserved words are
defined in a new configuration variable ORA_RESERVED_WORDS. It accept
a list of comma separated reserved words. Thanks to Andrea Agosti for
the report.
- Fix configuration parser that omit custom SYSUSERS definition.
2009 02 13 - version 5.0
- Fix places where $self->{prefix} where not used. This prefix is
used to replace DBA_... objects into ALL_... objects. Thanks to Daniel
Scott report and patch.
- Fix some problem on trigger export (missing ending semicolon, return
opaque replaced by return trigger, add missing return new value, single
quote for delimitating the function body hits against quotes inside the
function). Thanks to Luca DallOlio for reports and patches.
- Add first attempt to rewrite plsql code to plpgsql code (see function
plsql_to_plpgsql in new perl module PLSQL.pm). There's a configuration
option named PLSQL_PGSQL to activate the convertion.
2008 12 16 - version 4.11
- Fix Ora2Pg failure on Oracle database with case sensitive tables.
Thanks to Marc Cousin for report and patch.
- Fix missing schema name in query when extract views as tables.
2008 12 04 - version 4.10
- Fix missing replacement of table name on disable triggers when
required.
- Fix some malformed debug output messages.
- Add the capability to extract data from view as if it was a table.
This is usefull if you want to export/import data from an Oracle
view into a Pg table. There's nothing special to do, just to give
the view name into the TABLES configuration directive and set TYPE
to DATA or COPY. If views are not specified in the TABLES directive
there's not view export but only table data.
- Add capability to extract views structure as table schema. There's
nothing special to do, just to give the view name into the TABLES
configuration directive and set TYPE to TABLE. This will not extract
constraints or other table tuning from table used in the view. Thanks
to Groupe SAMSE for the feature request.
2008 10 27 - version 4.9
- Modify the DISABLE_TABLE_TRIGGERS configuration option. Should be now
replaced by DISABLE_TRIGGERS, but compatibility is preserved.
- Add DISABLE_SEQUENCE configuration option to not export alter
sequence after COPY or DATA export.
- Fix extraction of function based index that appears as SYS_NC....
Thanks to Bozkurt Erkut from SONY for the report
2008 09 04 - version 4.8
- Add SYSUSERS configuration option that allow you to specify a coma
separated list of Oracle System user/schema to exclude from extracted
object. By default it only exclude user SYS,SYSTEM,DBSNMP,OUTLN and
PERFSTAT
- Add support to other binary mode output than ':raw' to avoid the Perl
error message:"Wide character in print". See the BINMODE configuration
directive. This will help a lot if you have UTF-8 records.
Thank to Guillaume Demillecamps for the report.
- Fix double escaping of special character.
Thank to Guillaume Demillecamps for the report.
2008 01 25 - version 4.7
- Add support to regular expressions in the exclusion list. Thanks to
Peter Eisentraut
- Fix misformatted SQL string in function _extract_sequence_info.
Thanks to Bernd Helmle.
- Add escaping of backslash on COPY output. Thanks to Peter Eisentraut
2008 01 03 - version 4.6
- Applied a patch to add ALTER SEQUENCE statements to the dump to
adjust the sequence to the correct values after DATA and COPY dumps.
Thanks to Bernd Helmle.
- Applied a patch which fixes problems with broken COPY output when
extracting data from Orace databases with embedded tabs, carriage
returns and line feeds. Thanks to Bernd Helmle.
- Move the project to PgFoundry
2007 06 20 - version 4.5
- Fix columns order in index extraction. Thanks to Ugo Brunel from BULL.
2007 04 30 - version 4.4
- Fix missing single quote in role extraction.
- Add configuration directive NOESCAPE to enable/disable
escaping characters during data extraction. Default is enabled.
- Add TIMESTAMP, BINARY_FLOAT and BINARY_DOUBLE data type translation.
- Add DATA_TYPE configuration directive to allow user defined data type
translation.
- Add NLS_LANG configuration directive to set Oracle database encoding
and enforce a default language-setting in ora2pg.pl. Thanks to Lars
Weber
2007 04 03 - version 4.3
- Fix duplicate view export. Add schema selector to views. Thank to
Ugo BRUNEL from BULL for the fix.
- Remove 'use strict' to prevent failure on certain condition.
Thank to Andrea Schnabl for the report.
2006 06 08 - version 4.2
- Fix a miss taping on constraint type search that convert unique key
to primary key. Thank to Ugo BRUNEL (BULL) for the patch.
- Fix case sensitivity on CHECK constraint that could cause problem when
check value is uppercase. Thank to Ugo BRUNEL (BULL) for the patch.
2006 03 28 - version 4.1
- Fix a problem when using data_limit and where clause. Thank to
Rene Bentzen for the patch.
- Add enable/disable trigger on data import. Thank to Bernd Helmle.
- Fix escaping of chr(13) MS crashing data import into PG. Thank
to Ugo Brunel (BULL).
2006 03 22 - version 4.0
- Add validation of the requested schema in the database before all.
Thanks to Max Walton for the idea.
- Add multiple export type at the same time. Thanks to Max Walton
for the idea.
- Add support for in/out/inout function parameter. See PG_SUPPORTS_INOUT
configuration option. Thanks to Bernd Helmle for this great
contribution/patch.
- Add support for ROLES with Pg v8.1+. See PG_SUPPORTS_ROLE configure
option.
2006 02 10 - version 3.4
This release add better support to Oracle grant, function and grant
extraction. Great thanks to the Pg team!
- Add preservation of oracle primary key names. See KEEP_PKEY_NAMES
configuration option. Thanks to Antonios Christofides for this patch.
- Fix bug in case insensitive check constrainte. Thanks to Wojciech
Szenajch for the patch.
- Fix saving data to files correctly (binmod) when the oracle database
contains utf8 chars. Thanks to Richard Chen for the report.
- Fix bug on view extraction when a column contains the word WITH.
Thanks to Richard Chen for the patch.
- Fix wrong mapping between tge data type in Oracle "number(10)" and
Postgresql, which should be "integer" and not "bigint". Thanks to
Sergio Freire for the patch.
- Fix bug in EXCLUDE configuration directive parsing. Thanks to Matt
Miller for the patch.
2005 02 22 - version 3.3
- Fix bug "Modification of a read-only value attempted"
2005 02 11 - version 3.2
- Fix patch error on column position sort
- Replace 'now' by CURRENT_TIMESTAMP on SYSDATE replacement
- Fix bytea type that was not quoted.
2005 02 10 - version 3.1
- Fix bug on deferrable constraint. Thanks to Antonios Christofide for
the patch.
- Fix problem on defer_fkey that should be in a transaction. Thanks to
Antonios Christofide for the patch.
- Add sort by column position during schema extraction.
- Add support to SYSDATE. Thanks to David Cotter-Alatto Technologies Ltd
2004 12 24 - version 3.0
- Add 'TABLESPACE' extraction type to create PostgreSQL v8 tablespace.
2004 12 24 - version 2.9
- Debuging output rewrite. Thanks to Antonios Christofide for help.
- Add 'PG_NUMERIC_TYPE' configuration option to replace portable
numeric type into PostgreSQL internal type (smallint, integer,
bigint, real and float).
2004 12 24 - version 2.8
- Fix/add support to data export of type BLOB, RAW and LONG RAW.
Thanks to Antonios Christofide for help.
2004 12 23 - version 2.7
- Add 'FKEY_DEFERRABLE' configuration option to force foreign key
constraints to be exported as deferrable. Thanks to Antonios
Christofide for help.
- Add 'DEFER_FKEY' configuration option to defer all foreign key
constraints during data export. Thanks to Antonios Christofide
for help.
2004 12 23 - version 2.6
- Fix duplicate output during export. Thanks to Adriano Bonat for the
report.
- Fix data limit infinite loop during data extraction. Thanks to Thomas
REISS for the report.
- Add 'GEN_USER_PWD' configuration option allowing to generate a random
password. Thanks to Antonios Christofide for help.
(Require String::Random from CPAN).
- Fix USER/ROLES/GRANT extraction problem. Now all users are dumped.
All roles are translated to PostgreSQL groups. All grants are
exported. YOU MUST EDIT the output file to rewrite real privilege
and match your needs. Thanks to Antonios Christofide for help.
- Fix split COPY export into multiple transaction for large data export.
The number of row per transaction is set to 'DATA_LIMIT' value. A
value of O mean all in a single transaction.
2004 10 13 - version 2.5
- Fix extraction problem when the connection to Oracle DB is not as DBA.
2004 08 22 - version 2.4
- Fix bug in DBI errstr call.
- Add CASE_SENSITIVE configuration option to allow case sensitivity on
Add a new configuration directive 'USER_GRANTS' to do that. Thanks to
Octavi Fors for the report.
object name. Thanks to Thomas Wegner.
- Fix major bug in unique keys extraction. Thanks to Andreas Haumer and
Marco Lombardo for their great help.
- Add CHECK constraint extration. Thanks again to Andreas Haumer.
- Add IMPORT configuration option to include common configuration file
throught multiple configuration files.Thanks to Adam Sah and Zedo Inc.
- Add SKIP configuration option to turning off extraction of certain
- schema features. Thanks to Adam Sah and Zedo Inc.
- Fix bug in excluded tables
- Fix backslash escaping. Thanks to Adam Sah and Zedo Inc.
- Add REPLACE_TABLES configuration option to change table name during
data extraction.
- Add REPLACE_COLS configuration option to change columns name during
data extraction.
- Add WHERE configuration option to add where clause to each table or
specific tables during extraction. Usefull for replication. Thanks
to Adam Sah and Zedo Inc.
- Add progress indicators (per 1000 rows) and performance results
during data extraction in debug mod. Thanks to Adam Sah and Zedo Inc.
- Add Gzip and Bzip2 compress to output file if extension .gz or .bz2.
Gzip compress require perl module Compress::Zlib from CPAN. Thanks
to Adam Sah for the idea.
2004 04 13 - Version 2.3
- Fix bug in date/time conversion when using data export limit. Thanks
to Andreas Haumer.
- Add sort order when extracting tables and data to respect the TABLES
limited extraction array write order. Usefull if you have foreign key
constraints. Thanks to Andreas Haumer for the idea.
2004 04 13 - Version 2.2
- Add EXCLUDE configuration option to allow table exclusion
from all extraction.
- Fix a bug in escaping single quote on data export.
2004 03 09 - Version 2.1
- Fix COPY output by replacing special character.
- Add configuration file usefull for people who don't have Perl in mind
Thank's to Tanya Krasnokutsky to force me to do that :-)
- Fix other minor problem.
2002 12 26 - Version 2.0
- Clean code.
- Fix COPY output on column value with EOL and add column naming.
- Add support to the PostgreSQL 7.3 schema. So Oracle schema can now be
exported. (see export_schema init option)
- Remove data extraction limit (old default: 10) so each tuple will be
dump by default.
2002 12 03 - Version 1.12
I have fixed 2 bugs when using it against Oracle 817R3 on linux.
- Fix problem regarding RI constraints, the owner name was not
getting into the sql statement. Thank to Ian Boston.
- Moved all the RI constraints out of the create table statement.
Thank to Ian Boston for this contribution. This was a major request
from Ora2pg users.
2002 09 27 - Version 1.11
- Fix a problem when retrieving package+package body. Thanks to Mike
WILHELM-HILTZ.
- Set LongReadLen to 100000 when exporting table information. Many
users reports this kind of error: A-01406 LongReadLen too small and/or
LongTruncOk not set. This should fix the problem else you must
increase the value.
- Filtering by owner for better performance when retreiving database
schema. Thanks to Jefferson MEDEIROS.
2002 07 29 - Version 1.10
- Fix a problem with local settings regarding decimal separator (all ,
are changed to .) Thank to Jan Kester.
2002 06 04 - Version 1.9
- Fix a problem on exporting data which fill NULL instead of 0 or
empty string. Thanks to Jan Kester.
- Add time + date when export data [ tochar('YYYY-MM-DD HH24:MI:SS') ].
Thanks to Paolo Mattioli.
2002 03 05 - Version 1.8
- Add Oracle type FLOAT conversion to float8.
- Add column alias extraction on view.
Thanks to Jean-Francois RIPOUTEAU
- Add PACKAGE extraction (type => DATA).
2002 02 14 - Version 1.7
- Remove export of OUTLINE object type. Thanks to Jean-Paul ARGUDO.
2002 01 07 - Version 1.6
- Fix problem exporting NULL value. Thanks to Stephane Schildknecht.
2001 12 28 - Version 1.5
- Fix LongReadLen problem when exporting Oracle data on LONG and LOB
types. Thanks to Stephane Schildknecht for report and test.
- Add more precision on NUMBER type conversion
- Add conversion of type LONG, LOB, FILE
- Fix a problem when extracting data, sometime table could need to be
prefixed by the schema name.
- Fix output of Oracle data extraction. It now require a call to
function export_data().
2001 06 27 - Version 1.4
- Add online Oracle data extraction and insertion into PG database.
- Data export as insert statement (type => DATA)
- Data export as copy from stdin statement (type => COPY)
2001 06 20 - Version 1.3
- Grant/privilege extraction are now done separatly with option
type=>'GRANT'
- Sequence extraction with the option type=>'SEQUENCE'
- Trigger extraction with the option type=>'TRIGGER'
- Function extraction with the option type=>'FUNCTION' and
type=>'PROCEDURE'
- Complete rewrite of the foreign key extraction
- Fix incorrect type translation and many other bug fix
- Add schema only extraction by option schema => 'MYSCHEM'
2001 05 11 - Version 1.2
- Views extraction is now really done with the option type=>'VIEW'
- Add indexes extraction on tables.
- Changes name of constraints, default is now used.
- Add debug printing to see that the process is running :-)
- Add extraction of only required tablename.
- Add extraction of only n to n table indice. Indices of extraction
can be obtained with the option showtableid set to 1.
- Fix print of NOT NULL field.
- Complete rewrite of the grant extraction
- Complete rewrite of most things
2001 05 09 - Version 1.1
- Add table grant extraction based on group.
Oracle ROLES are exported as groups in PG
2001 05 09 - Initial version 1.0
------------------------------------------------------------------------------
Special thanks to Ali Pouya for documentation review. All my recognition
to Ali Pouya and Olivier Mazain for their great work in the package and
function export. Thanks to Jean-Paul Argudo for the time spent to heavily
testing Ora2Pg.
Special thanks to Josian Larcheveque and Stephane Silly as Oracle DBA
and their "patience".
Special Thanks to Dominique Legendre for his help on Spatial support and
all the tests performed.
Many thanks for all congratulation message, idea and bug report+fix I received.
Very special thanks to Jean-Paul Argudo that represent Ora2Pg at Linux Solution Paris 2005.
Gilles DAROLD <gilles (at) darold (dot) net>
Something went wrong with that request. Please try again.