Ora2Pg v18.2 has been released

Paris, France - September 1st, 2017

**Ora2Pg 18.2 released**

Version 18.2 of Ora2Pg, a free and reliable tool used to migrate an
Oracle database to PostgreSQL, has been officially released and is
publicly available for download.

This release fix several issues reported during the last six months
by all users. It also adds lot of new features and configuration
directives:

* Lot of improvement in outer join (+) translation even if there is
still some case not or wrongly translated.
* Add translation of SUBSTRB into substr.
* Allow use of array in MODIFY_TYPE to export Oracle user defined
type that are just array of some data type. For example:
CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
can be directly translated into text[] or varchar[]. In this case
use the directive as follow: MODIFY_TYPE CLUB:MEMBERS:text[]
Ora2Pg will take care to transform all data of this column into
the correct format. Only arrays of characters and numerics types
are supported.
* Add translation of Oracle function LISTAGG() into string_agg().
* Add TEST_VIEW action to perform a simple count of rows returned by
views on both database.
* Translate SQL%ROWCOUNT into GET DIAGNOSTICS rowcount = ROW_COUNT
and add translation of SQL%FOUND.
* Add translation of column in trigger event test with IS DISTINCT,
for example: IF updating('ID') THEN ... will be translated into:
IF TG_OP = 'UPDATE' AND NEW.'ID' IS DISTINCT FROM OLD.'ID' then...
* Replace UTL_MATH.EDIT_DISTANCE function by fuzzymatch levenshtein.
* Allow use of MODIFY_STRUCT with TABLE export. Table creation DDL
will respect the new list of columns and all indexes or foreign
key pointing to or from a column removed will not be exported.
* Add export of partition and subpartition using PostgreSQL native
partitioning.
* Auto detect encrypted columns and report them into the assessment.
SHOW_COLUMN will also mark columns as encrypted.
* Add information to global temporary tables in migration assessment.
* Add experimental DATADIFF functionality.
* Allow use of multiprocess with -j option or JOBS to FUNCTION and
PROCEDURE export. Useful if you have thousands of these objects.
* Force RAW(N) type with default value set to sys_guid() as UUID
on PostgreSQL.
* Replace function with out parameter using select into. For example
a call to: get_item_attr( attr_name, p_value );
where p_value is an INOUT parameter, will be rewritten as

p_value := get_item_attr( attr_name, p_value );

If there is multiple OUT parameters, Ora2Pg will use syntax:

SELECT get_item_attr( attr_name, p_value )
INTO (attr_name, p_value);

* Add translation of CONNECT BY using PostgreSQL CTE equivalent.
This translation also include a replacement of LEVEL and
SYS_CONNECT_BY_PATH native Oracle features. On complex queries
there could still be manual editing but all the main work is done.
* Add support to user defined exception, errcode affected to each
custom exception start from 50001.
* Translate call to to_char() with a single parameter into a cast
to varchar. Can be disabled using USE_ORAFCE directive.
* Improve ora2pg_scanner to automatically generates migration
assessment reports for all schema on an Oracle instance. Before
the schema name to audit was mandatory, now, when the schema
is not set Ora2Pg will scan all schema. The connexion user need
to have DBA privilege. Ora2Pg will also add the hostname and SID
as prefix in the filename of the report. This last changee forbids
ora2pg_scanner to overwrite a report if the same schema name is
found in several databases.

Several new configuration directives have been added:

* Add USE_ORAFCE configuration directive that can be enabled if you
want to use functions defined in the Orafce library and prevent
Ora2Pg to translate call to these functions. The Orafce library
can be found here: https://github.com/orafce/orafce
By default Ora2pg rewrite add_month(), add_year(), date_trunc()
and to_char() functions, but you may prefer to use the Orafce
functions that do not need any code transformation. Directive
DATE_FUNCTION_REWRITE has been removed as it was also used to
disable replacement of add_month(), add_year() and date_trunc()
when Orafce is used, useless now.
* Add FILE_PER_FKEYS configuration directive to allow foreign key
declaration to be saved in a separate file during schema export.
By default foreign keys are exported into the main output file or
in the CONSTRAINT_output.sql file. If enabled foreign keys will be
exported into a file named FKEYS_output.sql
* Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to
COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to
force the user to review the logic of the function. Once it is
fixed in Oracle source code or you want to comment this calls
enable the directive.
* Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg
use CREATE OR REPLACE in function DDL, if you need not to override
existing functions disable this configuration directive, DDL will
not include OR REPLACE.
* Add FUNCTION_CHECK configuration directive. Disable this directive
if you want to disable check_function_bodies.

SET check_function_bodies = false;

It disables validation of the function body string during CREATE
FUNCTION. Default is to use de postgresql.conf setting that enable
it by default.
* Add PG_SUPPORTS_PARTITION directive, disabled by default.
PostgreSQL version prior to 10.0 do not have native partitioning.
Enable this directive if you want to use PostgreSQL declarative
partitioning instead of the old style check constraint and trigger.
* Add PG_SUPPORTS_SUBSTR configuration directive to replace substr()
call with substring() on old PostgreSQL versions or some fork
like Redshift.
* Add PG_INITIAL_COMMAND to send some statements at session startup.
This directive is the equivalent used for Oracle connection,
ORA_INITIAL_COMMAND. Both can now be used multiple time now.
* Add DBLINK_CONN configuration directive. By default if you have
an autonomous transaction translated using dblink extension the
connection is defined using the values set with PG_DSN, PG_USER
and PG_PWD. If you want to fully override the connection string
use this directive to set the connection in the autonomous
transaction wrapper function.
* Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace
all string constant during the pl/sql to plpgsql translation,
string constant are all text include between single quote. If you
have some string placeholder used in dynamic call to queries you
can set a list of regexp to be temporary replaced to not break the
parser. For example:

STRING_CONSTANT_REGEXP <cfqueryparam value=".*">

The list of regexp must use the semi colon as separator.
* Add FUNCTION_STABLE configuration directive. By default Oracle
functions are marked as STABLE as they can not modify data unless
when used in PL/SQL with variable assignment or as conditional
expression. You can force Ora2Pg to create these function as
VOLATILE by disabling this configuration directive.
* Add new TO_NUMBER_CONVERSION configuration directive to control
TO_NUMBER translation behavior. By default Oracle call to function
TO_NUMBER will be translated as a cast into numeric. For example,
TO_NUMBER('10.1234') is converted into PostgreSQL call:
to_number('10.1234')::numeric.
If you want you can cast the call to integer or bigint by changing
the value of the configuration directive. If you need better
control of the format, just set it as value, for example:
TO_NUMBER_CONVERSION 99999999999999999999D9999999999
will convert the code above as:
TO_NUMBER('10.1234', '99999999999999999999D9999999999')
Any value of the directive that it is not numeric, integer or
bigint will be taken as a mask format. If set to none, then no
conversion will be done.
* Add LOOK_FORWARD_FUNCTION configuration directive which takes a
list of schema to get functions/procedures meta information that
are used in the current schema export. When replacing call to
function with OUT or INOUT parameters, if a function is declared
in an other package then the function call rewriting can not be
done because Ora2Pg only knows about functions declared in the
current schema. By setting a comma separated list of schema as
value of the directive, Ora2Pg will look forward in these packages
for all functions, procedures and packages declaration before
proceeding to current schema export.
* Add PG_SUPPORTS_NAMED_OPERATOR to control the replacement of the
PL/SQL operator used in named parameter => with the PostgreSQL
proprietary operator := Disable this directive if you are using
PG < 9.5
* Add a warning when Ora2Pg reorder the parameters of a function
following the PostgreSQL rule that all input parameters following
a parameter with a default value must have default values as well.
In this case, Ora2Pg extracts all parameters with default values
and put them at end of the parameter list. This is to warn you
that a manual rewrite is required on calls to this function.

New command line options have been added:

- Add -N | --pg_schema command line option to be able to override
the PG_SCHEMA configuration directive. When this option is set
at command line, EXPORT_SCHEMA is automatically activated.
- Add --no_header option with equivalent NO_HEADER configuration
directive to output the Ora2Pg header but just the translated
code.

There is also some behavior changes from previous release:

- Remove SysTimestamp() from the list of not translated function,
it is replaced with CURRENT_TIMESTAMP for a long time now.
- Change migration assessment cost to 84 units (1 day) for type
TABLE, INDEX and SYNONYM and to 168 units (2 days) for TABLE
PARTITION and GLOBAL TEMPORARY TABLE, this is more realistic.
- Set minimum assessment unit to 1 when an object exists.
Improve PL/SQL code translation speed.
- Change behavior of COMPILE_SCHEMA directive used to force Oracle
to compile schema before exporting code. When this directive is
enabled and SCHEMA is set to a specific schema name, only invalid
objects in this schema will be recompiled. When SCHEMA is not set
then all schema will be recompiled. To force recompile invalid
object in a specific schema, set COMPILE_SCHEMA to the schema name
you want to recompile. This will ask to Oracle to validate the
PL/SQL that could have been invalidate after a export/import for
example. The 'VALID' or 'INVALID' status applies to functions,
procedures, packages and user defined types.
- Default transaction isolation level is now set to READ COMMITTED
for all action excluding data export.
- Oracle doesn't allow the use of lookahead expression but you may
want to exclude some objects that match the ALLOW regexp you have
defined. For example if you want to export all table starting
with E but not those starting with EXP it is not possible to do
that in a single expression.
Now you can start a regular expression with the ! character to
exclude all objects matching the regexp given just after. Our
previous example can be written as follow: ALLOW E.* !EXP.*
it will be translated into

REGEXP_LIKE(..., '^E.*$') AND NOT REGEXP_LIKE(..., '^EXP.*$')

in the object search expression.
- Fix quoting of PG_SCHEMA with multiple schema in search path. The
definition of the search path now follow the following behavior:
* when PG_SCHEMA is define, always set search_path to its value.
* when EXPORT_SCHEMA is enabled and SCHEMA is set, the search_path
is set the name of the schema.
- Remove forcing of export_schema when pg_schema is set at command
line. This could change the behavior of some previous use of these
variables and the resulting value of the search_path but it seems
much better understandable.
- Rewrite translation of raise_application_error to use RAISE
EXCEPTION with a message and the SQLSTATE code. Oracle user
defined code -20000 to -20999 are translated to PostgreSQL
user define code from 45000 to 45999. Call to
raise_application_error(mySQLCODE, myErrmsg);
will be translated into
RAISE EXCEPTION '%', myErrmsg USING ERRCODE = mySQLCODE;
- Remove migration assessment cost for TG_OP and NOT_FOUND they
might be fully covered now.

As usual there is also lot of bug fixes and Oracle to PostgreSQL
conversion adjustments, for a complete list see:
https://github.com/darold/ora2pg/blob/master/changelog

===== Links & Credits =====

Thanks to all contributors, they are all cited in the changelog file.

Useful Links:

- Website: http://ora2pg.darold.net/
- Download1: https://github.com/darold/ora2pg/releases/
- Download2: http://sourceforge.net/projects/ora2pg/
- Development: https://github.com/darold/ora2pg
- Changelog: https://github.com/darold/ora2pg/blob/master/changelog
- Documentation: https://github.com/darold/ora2pg/blob/master/README

--------------

**About Ora2Pg**

Ora2Pg is an easy and reliable tool to migrate from Oracle to
PostgreSQL. It is developed since 2001 and can export most of the
Oracle objects (table, view, materialized view, tablespace, sequence,
indexes, trigger, grant, function, procedure, package, partition,
data, blob, external table, etc.). Export of spatial data is also
supported with the PostGis extension.

Ora2Pg works on any platform and is available under the GPL v3 licence.

Docs, Download & Support at http://ora2pg.darold.net/

--------------

--
Gilles Darold