Version 18.2

@darold darold released this Sep 1, 2017 · 218 commits to master since this release

2017 09 01 - v18.2

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

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

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

Several new configuration directives have been added:

  - Add USE_ORAFCE configuration directive that can be enabled if you
    want to use functions defined in the Orafce library and prevent
    Ora2Pg to translate call to these functions. The Orafce library
    can be found here: https://github.com/orafce/orafce
    By default Ora2pg rewrite add_month(), add_year(), date_trunc()
    and to_char() functions, but you may prefer to use the Orafce
    functions that do not need any code transformation. Directive
    DATE_FUNCTION_REWRITE has been removed as it was also used to
    disable replacement of add_month(), add_year() and date_trunc()
    when Orafce is used, useless now.
  - Add FILE_PER_FKEYS configuration directive to allow foreign key
    declaration to be saved in a separate file during schema export.
    By default foreign keys are exported into the main output file or
    in the CONSTRAINT_output.sql file. If enabled foreign keys will be
    exported into a file named FKEYS_output.sql
  - Add new COMMENT_COMMIT_ROLLBACK configuration directive. Call to
    COMMIT/ROLLBACK in PL/SQL code are kept untouched by Ora2Pg to
    force the user to review the logic of the function. Once it is
    fixed in Oracle source code or you want to comment this calls
    enable the directive.
  - Add CREATE_OR_REPLACE configuration directive. By default Ora2Pg
    use CREATE OR REPLACE in function DDL, if you need not to override
    existing functions disable this configuration directive, DDL will
    not include OR REPLACE.
  - Add FUNCTION_CHECK configuration directive. Disable this directive
    if you want to disable check_function_bodies.
    
    	SET check_function_bodies = false;
    
    It disables validation of the function body string during CREATE
    FUNCTION. Default is to use de postgresql.conf setting that enable
    it by default.
  - Add PG_SUPPORTS_PARTITION directive, disabled by default.
    PostgreSQL version prior to 10.0 do not have native partitioning.
    Enable this directive if you want to use PostgreSQL declarative
    partitioning instead of the old style check constraint and trigger.
  - Add PG_SUPPORTS_SUBSTR configuration directive to replace substr()
    call with substring() on old PostgreSQL versions or some fork
    like Redshift.
  - Add PG_INITIAL_COMMAND to send some statements at session startup.
    This directive is the equivalent used for Oracle connection,
    ORA_INITIAL_COMMAND. Both can now be used multiple time now.
  - Add DBLINK_CONN configuration directive. By default if you have
    an autonomous transaction translated using dblink extension the
    connection is defined using the values set with PG_DSN, PG_USER
    and PG_PWD. If you want to fully override the connection string
    use this directive to set the connection in the autonomous
    transaction wrapper function. For example:
    
        DBLINK_CONN    port=5432 dbname=pgdb host=localhost user=pguser password=pgpass
    
  - Add STRING_CONSTANT_REGEXP configuration directive. Ora2Pg replace
    all string constant during the pl/sql to plpgsql translation,
    string constant are all text include between single quote. If you
    have some string placeholder used in dynamic call to queries you
    can set a list of regexp to be temporary replaced to not break the
    parser. For example:
    
    	STRING_CONSTANT_REGEXP         <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.

For a complete list of bug fix see changelog file.

Version 18.1

@darold darold released this Feb 17, 2017 · 580 commits to master since this release

2017 02 17 - v18.1

This release fix several issues reported on outer join translation
thanks to the help of Pavel Stehule and reapply the commit on virtual
column export that was accidentally removed from v18.0. It also adds
several new features:

  • Remove CHECK constraints for columns converted into boolean using
    REPLACE_AS_BOOLEAN column.
  • Oracle function are now marked as stable by default as they can
    not modify data.

Two new configuration directives have been added:

  • DATE_FUNCTION_REWRITE: by default Ora2pg rewrite add_month(),
    add_year() and date_trunc() functions set it to 0 to force Ora2Pg
    to not translate those functions if translated code is broken.
  • GRANT_OBJECT: when exporting GRANT you can now specify a comma
    separated list of objects which privileges must be exported.
    Default is to export privileges for all objects. For example
    set it to TABLE if you just want to export privilege on tables.

and a new command line option:

  • Add -g | --grant_object command line option to ora2pg to be able
    to extract privilege from the given object type. See possible values
    with GRANT_OBJECT configuration directive.

Here is the complete list of changes:

  • Remove empty output.sql file in current directory with direct data
    import. Thanks to kuzmaka for the report.
  • Fix shell replacement of $$ in function definition in Makefile.PL
    embedded configuration file. Thanks to kuzmaka for the report.
  • Fix shell replacement of backslash in Makefile.PL embedded
    configuration file. Thanks to kuzmaka for the report.
  • Add warning level to virtual column notice.
  • Fix comment in where clause breaking the outer join association.
    Thanks to Pavel Stehule for the report.
  • Add parsing and support of virtual column from DDL file.
  • Reapply commit on virtual column export that was accidentally
    removed in commit d5866c9. Thanks to Alexey for the report.
  • Fix mix of inner join and outer join not translated correctly.
    Thanks to Pavel Stehule for the help to solve this issue.
  • Fix additional comma in column DEFAULT value from DDL input file.
    Thanks to Cynthia Shang for the report.
  • Fix comments inside FROM clause breaking translation to ANSI outer
    joins. Thanks to Pavel Stehule for the report.
  • Fix replacement of sdo_geometry type into function. Thanks to
    Saber Chaabane for the report.
  • Fix subquery in outer join clause. Thanks to Saber Chaabane for
    the report.
  • Fix duplicated subqueries placeholder in the from clause.
    Thanks to Saber Chaabane for the report.
  • Fix replacement of subquery place older during outer join rewrite.
    Thanks to Saber Chaabane for the report.
  • Add DATE_FUNCTION_REWRITE configuration directive. By default
    Ora2pg rewrite add_month(), add_year() and date_trunc() functions
    set it to 0 to force Ora2Pg to not translate those functions if
    translated code is broken. Thanks to Pavel Stehule for the feature
    request.
  • Do not report error when -g is used but action is not GRANT.
    Thanks to Shane Jimmerson for the report.
  • Oracle function can not modify data, only procedure can do that,
    so mark them as stable. Thanks to Pavel Stehule for the report.
  • Missed some obvious combination like upper/lower case or no space
    after AND/OR on outer join parsing and some other issues.
  • Add missing call to extract_subqueries() recursively. Thanks to
    Pavel Stehule for the report.
  • Add full support of outer join translation in sub queries.
  • Add translation of mixed inner join and Oracle outer join. Thanks
    to Pavel Stehule for the report.
  • Fix missing space between keyword AS and END from the decode()
    transformation. Thanks to Pavel Stehule for the report.
  • Fix parsing of outer join with UNION and translation to left join.
    Thanks to Pavel Stehule for the report.
  • Remove CHECK constraints for columns converted into boolean using
    REPLACE_AS_BOOLEAN column. Thanks to Shane Jimmerson for the
    feature request.
  • Fix regression on SQL and PLSQL rewrite when a text constant
    contained a semi-comma.
  • Add the GRANT_OBJECT configuration directive. When exporting GRANT
    you can specify a comma separated list of objects for which the
    privileges will be exported. Default is export for all objects.
    Here are the possibles values TABLE, VIEW, MATERIALIZED VIEW,
    SEQUENCE, PROCEDURE, FUNCTION, PACKAGE BODY, TYPE, SYNONYM and
    DIRECTORY. Only one object type is allowed at a time. For example
    set it to TABLE if you just want to export privilege on tables.
    You can use the -g option to overwrite it.
    When used this directive prevent the export of users unless it is
    set to USER. In this case only users definitions are exported.
  • Add the -g | --grant_object command line option to ora2pg to be able
    to extract privilege from the given object type. See possible values
    with GRANT_OBJECT configuration directive.
  • Improve replacement of ROWNUM by LIMIT+OFFSET clause.
  • Fix extra semi-colon at end of statement.
  • Override ora2pg.spec with Devrim's one but with String::Random
    removing as it is no more used.

Version 18.0

@darold darold released this Jan 30, 2017 · 613 commits to master since this release

2017 01 29 - v18.0

This new major release adds several new useful features and lot of
improvements.

  • Automatic rewrite of simple form of (+) outer join Oracle's
    syntax. This major feature makes Ora2Pg become the first free
    tool that is able to rewrite automatically some (+) outer join in
    command line mode. This works with simple form of outer join
    but this is a beginning.
  • Add export of Oracle's virtual column using a real column and
    a trigger.
  • Allow conversion of RAW/CHAR/VARCHAR2 type with precision in
    DATA_TYPE directive. Useful for example to transform all RAW(32)
    or VARCHAR2(32) columns into PostgreSQL special type uuid.
  • Add export NOT VALIDATED state from Oracle foreign keys and check
    constraints into NOT VALID constraints in PostgreSQL.
  • Replace call to SYS_GUID() with uuid_generate_v4() by default.
  • Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous
    transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
  • Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function
    calls and other PL/SQL to plpgsql replacement. There should not
    be any limitation in rewriting when a function contains a sub
    query or an other function call inside his parameters.
  • Refactoring of ora2pg to not requires any dependency other than
    the Perl DBI module by default. All DBD drivers are now optionals
    and ora2pg will expect an Oracle DDL file as input by default.
  • Add export of Oracle's global variables defined in package. They
    are exported as user defined custom variables and available in
    a session. If the variable is a constant or have a default value
    assigned at declaration, ora2pg will create a new file with the
    declaration (global_variables.conf) to be included in the main
    configuration file postgresql.conf file.
  • Create full text search configuration when USE_UNACCENT directive
    is enabled using the auto detected stemmer or the one defined in
    FTS_CONFIG. For example:
    CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
    ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR
    hword, hword_part, word WITH unaccent, french_stem;
    CREATE INDEX place_notes_cidx ON places
    USING gin(to_tsvector('fr', place_notes));

Changes and incompatibilities from previous release:

  • FTS_INDEX_ONLY is now enabled by default because the addition of
    a column is not always possible and not always necessary where a
    simple function-based index is enough.
  • Remove use to setweigth() on single column FTS based indexes.
  • Change default behaviour of Ora2Pg in Full Text Search index
    export.

A new command line option and some configuration directive have
been added:

  • Option -D | --data_type to allow custom data type replacement
    at command line like in configuration file with DATA_TYPE.
  • UUID_FUNCTION to be able to redefined the function called to
    replace SYS_GUID() Oracle function. Default to uuid_generate_v4.
  • REWRITE_OUTER_JOIN to be able to disable the rewriting of Oracle
    native syntax (+) into OUTER JOIN if rewritten code is broken.
  • USE_UNACCENT and USE_LOWER_UNACCENT configuration directives to
    use the unaccent extension with pg_trgm with the FTS indexes.
  • FTS_INDEX_ONLY, by default Ora2Pg creates an extra tsvector column
    with a dedicated triggers for FTS indexes. Enable this directive
    if you just want a function-based index like:
    CREATE INDEX ON t_document USING
    gin(to_tsvector('pg_catalog.english', title));
  • FTS_CONFIG, use this directive to force the text search stemmer
    used with the to_tsvector() function. Default is to auto detect
    the Oracle FTS stemmer. For example, setting FTS_CONFIG to
    pg_catalog.english or pg_catalog.french will override the auto
    detected stemmer.

There's also lot fixes of issues reported by users from the past two
months, here is the complete list of changes:

  • Fix return type in function with a single inout parameter and a
    returned type.
  • Prevent wrong rewrite of empty as null when a function is used.
    Thanks to Pavel Stehule for the report.
  • Add the UUID_FUNCTION configuration directive. By default Ora2Pg
    will convert call to SYS_GUID() Oracle function with a call to
    uuid_generate_v4 from uuid-ossp extension. You can redefined it
    to use the gen_random_uuid function from pgcrypto extension by
    changing the function name. Default to uuid_generate_v4. Thanks
    to sjimmerson for the feature request.
  • Add rewrite of queries with simple form of left outer join syntax
    (+) into the ansi form.
  • Add new command line option -D | --data_type to allow custom data
    type replacement at command line like in configuration file with
    DATA_TYPE.
  • Fix type in ROWNUM replacement expression. Thanks to Pavel Stehule
    for the report.
  • Add replacement of SYS_GUID by uuid_generate_v4 and allow custom
    rewriting of RAW type. Thanks to Nicolas Martin for the report.
  • Fix missing WHERE clause in ROWNUM replacement with previous patch
    thanks to Pavel Stehule for the report.
  • Fix ROWNUM replacement when e sub select is used. Thanks to Pavel
    Stehule for the report.
  • Fix wrong syntax in index creation with DROP_INDEXES enabled.
    Thanks to Pave Stehule for the report.
  • Remove replacement of substr() by substring() as PostgreSQL have
    the substr() function too. Thanks to Pavel Stehule for the report.
  • Move LIMIT replacement for ROWNUM to the end of the query. Thanks
    to Pavel Stehule for the report.
  • Fix text default value between parenthesis in table declaration.
    Thanks to Pavel Stehule for the report.
  • Fix return type when a function have IN/OUT parameter. Thanks to
    Pavel Stehule for the report.
  • Mark uuid type to be exported as text. Thanks to sjimmerson for
    the report.
  • Add EXECUTE to open cursor with like "OPEN var1 FOR var2;". Thanks
    to Pavel Stehule for the report.
  • Fix replacement of local type ref cursor. Thanks to Pavel Stehule
    for the report.
  • Add EXECUTE keyword to OPEN CURSOR ... FOR with dynamic query.
    Thanks to Pavel Stehule for the report.
  • Fix case sensitivity issue in FOR .. IN variable declaration
    replacement. Thanks to Pavel Stehule for the report.
  • Fix wrong replacement of cast syntax ::. Thanks to Pavel Stehule
    for the report.
  • Reactivate numeric cast in call to round(value,n).
  • Close main output data file at end of export.
  • Add virtual column state in column info report, first stage to
    export those columns as columns with associated trigger.
  • Fix unwanted replacement of REGEXP_INSTR. Thanks to Bernard
    Bielecki for the report.
  • Allow rewrite of NUMBER(, 0) into bigint or other type instead
    numeric(38), just set DATA_TYPE to NUMBER(
    ,0):bigint. Thanks to
    kuzmaka for the feature request.
  • Export partitions indexes into PARTITION_INDEXES_....sql separate
    file named. Thanks to Nicolas Martin for the feature request.
  • Fix fatal error when schema CTXSYS does not exists. Thanks to
    Bernard Bielecki for the report.
  • Fix missing text value replacement. Thanks to Bernard Bielecki
    for the report.
  • Fix type replacement in declare section when the keyword END was
    present into a variable name.
  • Export NOT VALIDATED Oracle foreign key and check constraint as
    NOT VALID in PostgreSQL. Thanks to Alexey for the feature request.
  • Add object matching of regex 'SYS_.*$' to the default exclusion
    list.
  • Fix UTF8 output to file as the open pragma "use open ':utf8';"
    doesn't works in a global context. binmode(':encoding(...)') is
    used on each file descriptor for data output.
  • Improve parsing of tables/indexes/constraints/tablespaces DDL from
    file.
  • Improve parsing of sequences DDL from file.
  • Improve parsing of user defined types DDL from file.
  • Export Oracle's TYPE REF CURSOR with a warning as not supported.
  • Replace call to plsql_to_plpgsql() in Ora2Pg.pm by a call to new
    function convert_plsql_code().
  • Move export of constraints after indexes to be able to use USING
    index in constraint creation without error complaining that index
    does not exists.
  • Add "CREATE EXTENSION IF NOT EXISTS dblink;" before an autonomous
    transaction or "CREATE EXTENSION IF NOT EXISTS pg_background;".
  • Improve parsing of packages DDL from file.
  • When a variable in "FOR varname IN" statement is not found in the
    DECLARE bloc, Ora2Pg will automatically add the variable to this
    bloc declared as a RECORD. Thanks to Pavel Stehule for the report.
  • Major rewrite of the way Ora2Pg parse PL/SQL to rewrite function
    calls and other PL/SQL to plpgsql replacement. There should not
    be limitation in rewriting when a function contains a sub query
    or an other function call inside his parameters.
  • Fix unwanted SELECT to PERFORM transformation inside literal
    strings. Thanks to Pavel Stehule for the report.
  • Fix bug in DEFAULT value rewriting. Thanks to Pavel Stehule for
    the report.
  • Fix replacement of DBMS_OUTPUT.put_line with RAISE NOTICE.
  • Reset global variable storage for each package.
  • Improve comment parsing in packages and prevent possible infinite
    loop in global variable replacement.
  • Add the REWRITE_OUTER_JOIN configuration directive to be able to
    disable the rewriting of Oracle native syntax (+) into OUTER JOIN
    if it is broken. Default is to try to rewrite simple form of
    right outer join for the moment.
  • Export types and cursors declared as global objects in package
    spec header into the main output file for package export. Types
    and cursors declared into the package body are exported into the
    output file of the first function declared in this package.
  • Globals variables declared into the package spec header are now
    identified and replaced into the package code with the call to
    user defined custom variable. It works just like globals variables
    declared into the package body.
  • Add auto detection of Oracle FTS stemmer and disable FTS_CONFIG
    configuration directive per default. When FTS_CONFIG is set its
    value will overwrite the auto detected value.
  • Create full text search configuration when USE_UNACCENT directive
    is enabled using the auto detected stemmer or the one defined in
    FTS_CONFIG. For example:
    CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
    ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR
    hword, hword_part, word WITH unaccent, french_stem;
    CREATE INDEX place_notes_cidx ON places
    USING gin(to_tsvector('fr', place_notes));
  • Remove CONTAINS(ABOUT()) from the migration assessment, there no
    additional difficulty to CONTAINS rewrite.
  • Add ANYDATA to the migration assessment keyword to detect.
  • Allow conversion of CHAR/VARCHAR2 type with precision in DATA_TYPE
    directive. For example it's possible to transform all VARCHAR2(32)
    columns only into PostgreSQL special type uuid by setting:
    DATA_TYPE VARCHAR2(32):uuid
    Thanks to sjimmerson for the feature request.
  • Update year in copyrights
  • Fix creation of schema when CREATE_SCHEMA+PG_SCHEMA are defined.
  • Fix renaming of temporary file when exporting partitions.
  • Move MODIFY_TYPE to the type section
  • Update documentation about globals variables.
  • Add export of Oracle's global variables defined in package. They
    are exported as user defined custom variables and available in
    a session. Oracle variables assignment are exported as call to:
    PERFORM set_config('pkgname.varname', value, false);
    Use of these variable in the code is replaced by:
    current_setting('pkgname.varname')::global_variables_type;
    the variable type is extracted from the pacjkage definition. If
    the variable is a constant or have a default value assigned at
    declaration, ora2pg will create file global_variables.conf with
    the definition to include in postgresql.conf file so that their
    values will already be set at database connection. Note that the
    value can always modified by the user so you can not have exactly
    a constant.
  • Fix migration assessment of view.
  • Remove call to FROM SYS.DUAL, only FROM DUAL was replaced.
  • Replace call to trim into btrim.
  • Improve rewrite of DECODE when there is function call inside.
  • Add function replace_right_outer_join() to rewrite Oracle (+)
    right outer join.
  • Improve view migration assessment.
  • Create a FTS section in the configuration file dedicated to FTS
    control.
  • Add USE_UNACCENT and USE_LOWER_UNACCENT configuration directives
    to use the unaccent extension with pg_trgm.
  • Do not create FTS_INDEXES_* file when there is no Oracle Text
    indexes.
  • Update query test score when CONTAINS, SCORE, FUZZY, ABOUT, NEAR
    keyword are found.
  • Remove use to setweigth() on single column FTS based indexes.
    Thanks to Adrien Nayrat for the report.
  • Update documentation on FTS_INDEX_ONLY with full explanation on
    the Ora2Pg transformation.
  • Refactoring ora2pg to not requires any dependency other than the
    Perl DBI module by default. All DBD drivers are now optionals and
    ora2pg will expect to received an Oracle DDL file as input by
    default. This makes easiest packaging or for any distribution that
    can not build a package because of the DBD::Oracle requirement.
    DBD::Oracle, DBD::MySQL and DBD::Pg are still required if you want
    Ora2Pg to migrate your database "on-line" but they are optional
    because Ora2Pg can also convert input DDL file, this is the
    default now. Thanks to Gustavo Panizzo for the feature request and
    the work on Debian packaging.
  • Remove String::Random dependency in rpm spec file, it is no used
    even if it was mentioned into a comment.
  • Exclude internal Oracle Streams AQ JMS types from the export.
    Thanks to Joanna Xu for the report.
  • Fix some other spelling issues. Thanks to Gustavo Panizzo for the
    patch.
  • Fix some spelling errors. Thanks to Gustavo Panizzo for the patch.
  • Revert patch 697f09d that was breaking encoding with input file
    (-i). Thanks to Gary Evans for the report.
  • Add two new configuration directive to control FTS settings,
    FTS_INDEX_ONLY and FTS_CONFIG.

Version 17.6

@darold darold released this Nov 17, 2016 · 684 commits to master since this release

2016 11 17 - v17.6

This release adds several new features:

  • Adds export of Oracle Text Indexes into FTS or pg_trgm based indexes,
  • Add export of indexes defined on materialized views
  • Allow export of materialized views as foreign tables when export type is FDW.
  • Add replacement of trim() by btrim().

Two new configuration directives have been added:

  • USE_INDEX_OPCLASS: when value is set to 1, this will force Ora2Pg to export all indexes defined on varchar2() and char() columns using *_pattern_ops operators. If you set it to a value greater than 1 it will only change indexes on columns where the character limit is greater or equal than this value.
  • CONTEXT_AS_TRGM: when enabled it forces Ora2Pg to translate Oracle Text indexes into PostgreSQL indexes using pg_trgm extension. Default is to translate CONTEXT indexes into FTS indexes and CTXCAT indexes using pg_trgm. Some time using pg_trgm based indexes is enough.

There's also some fix of issues reported by users, here is the complete list of changes:

  • Fixed non-use of custom temp_dir (-T). Thanks to Sebastian Albert for the patch.
  • Make export of FTS indexes from materialized view work as for tables.
  • Fix drop of indexes during export of data when DROP_INDEXES is enabled.
  • Remove double quote in function and procedure name from an input file to avoid creating a file with double quote in its name.
  • Fix export of unique index associated to a primary key.
  • Move OPTION (key "yes") of FDW table before NOT NUL constraint and default clause.
  • Fix some encoding issue during data export into file.
  • Rename FTS indexes prefix output file into FTS_INDEXES and export CTXCAT Oracle indexes as GIN pg_trgm indexes instead of FTS indexes.
  • Add export of indexes of type CTXCAT as FTS indexes.
  • Export triggers and update order for FTS indexes to separate file prefixed with FTS_INDEXES.
  • Exclude from export synonyms starting with a slash that correspond to incomplete deleted synonyms. Thanks to Nouredine Mallem for the report.
  • Add export of indexes defined on materialized views. Thanks to Nouredine Mallem for the report.
  • Fix export of foreign key and FTS indexes when looking at dba_* tables and multiple different schemas have the same fk or context indexes definition. Thanks to Nouredine Mallemfor the patch.
  • Fix export of CONTEXT or FULLTEXT Oracle index into PostgreSQL FTS with trigger and initial update statement.
  • Add configuration directive USE_INDEX_OPCLASS to force Ora2Pg to export all indexes defined on varchar2() and char() columns using those operators. A value greater than 1 will only change indexes on columns where the character limit is greater or equal than this value.
  • Fix FDW export of mysql tables. Thanks to yafeishi for the report.
  • Fix decode() rewrite. Thanks to Jean-Yves Julliot for the report.
  • Fix regression introduced into the export of NUMBER to integer like PG types.
  • Show partition name in progress bar instead of main table name.

Version 17.5

@darold darold released this Oct 21, 2016 · 708 commits to master since this release

This is a maintenance release to fix several issues reported by users.
There is also some major improvements and new features.

There is a new configuration directive or change default behavior:

  • Fix export of CLOBs and NCLOB that was truncated to 64 Kb.
  • PG_BACKGROUND : when enabled autonomous transactions will be
    built using Robert Haas extension pg_background instead of dblink.
    Default is to still used dblink as pg_background is available
    only for PostgreSQL >= 9.5.
  • All Perl I/O now use the open pragma instead of calling method
    binmode(). This will force input and output to utf8 using the
    Perl pragma:
    use open ':encoding(utf8)';
    when configuration directive BINMODE is not set or NLS_LANG is
    set to UTF8.
  • Ora2Pg will now export empty lob as empty string instead of NULL
    when the source column has NOT NULL constraint and that directive
    EMPTY_LOB_NULL is not activated.
  • Improve and fix progress bar especially when using JOBS/-J option.
  • Allow LOAD action to apply all settings defined in the input file
    on each opened session, this allow to use LOAD with export schema
    enabled. If settings are not set in the input file encoding and
    search_path is set from the ora2pg configuration settings.
  • NUMBER(*,0) is now exported as numeric(38) as well as a NUMBER
    with DATA_SCALE set to 0, no DATA_PRECISION and a DATA_LENGTH
    of 22. The last correspond to Oracle type INTEGER or INT.
  • Allow conversion of type with precision in DATA_TYPE directive.
    For example it is possible to transform all NUMBER(12,2) only
    into numeric(12,2) by escaping the comma. Example:
    DATA_TYPE NUMBER(12\,2):numeric(12\,2);...
  • Write data exported into temporary files (prefixed by tmp_) and
    renamed them at end of the export to be able to detect incomplete
    export and override it at next export.
  • Add export of type created in package declaration.
  • Export foreign key when the referenced table is not in the
    same schema.
  • Enabled by default PG_SUPPORTS_CHECKOPTION assuming that your Pg
    destination database is at least a 9.4 version.
  • Add 12 units to migration assessment report per table/column
    conflicting with a reserved word in PostgreSQL to reflect the
    need of code rewriting.
  • Output a warning when a column has the same name than a system
    column (xmin,xmax,ctid,etc.)
  • Replace SYSDATE by a call to clock_timestamp() instead of a call
    to LOCALTIMESTAMP in plpgsql code.
  • Add missing documentation about DISABLE_PARTITION directive used
    to not reproduce partitioning into PostgreSQL and only export
    partitioned data into the main table.

See changelog for a complete list of changes.

Version 17.4

@darold darold released this Apr 21, 2016 · 794 commits to master since this release

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

2016 04 20 - v17.4

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.

See changelog for the complete list of changes.

Version 17.3

@darold darold released this Mar 27, 2016 · 825 commits to master since this release

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.

Please, upgrade.

Version 17.2

@darold darold released this Mar 24, 2016 · 829 commits to master since this release

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.

Version 17.1

@darold darold released this Feb 29, 2016 · 847 commits to master since this release

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

Version 17.0

@darold darold released this Feb 22, 2016 · 852 commits to master since this release

This new major release adds a new action type TEST to obtain a count of all objects, primary keys, constraints, etc., at both sides, Oracle and PostgreSQL, to perform a diff between the two database and verify that everything have been well imported. It also fixes several issues reported by users.

A new ora2pg command line option have been added to ora2pg script:

  • Add --count_rows command line option to perform a real row count on both side, Oracle and PostgreSQL, in TEST report.

See changelog for the complete list of changes and bugfixes: