What’s New

The following enhancements are added to the EDB Postgres Migration Portal for this release:

  • You can view the reason an object failed to migrate for the selected schema in the Common Failures tab on the Workspace page:

common failures tab

  • You can download a CSV file for the common failures for the project:

csv file download

  • A warning message is displayed if a project or a schema is less than 70% compatible or if any DDL doesn’t succeed after multiple attempts. An online link provides quick access to EDB experts at mailto:migration-services@enterprisedb.com for migration assistance.

warning sign

  • The FAQ page is updated regularly with the latest frequently asked questions.

  • Changes were made to the new UI for better user experience.

Repair Handlers

New Repair Handler

The following repair handler is added to improve the Advance Server compatibility ratio:

  • ERH 1011 - Block Comment Terminator:

    Adds block comment terminator */ for unterminated block comments inside Procedures, Functions, Packages, Package Body, Triggers, Views, and Type Body.

    For example:

 CREATE OR REPLACE FUNCTION example_unterminated (
     input_str  IN VARCHAR2
 )
/***************************************************************************
  Function      : example_unterminated
  Author        : EnterpriseDB
  Description   : Returns substring
/***************************************************************************
 Change
 Date          : sysdate
 Changes       : new changes
/***************************************************************************/
 RETURN VARCHAR2
 AS
   var1         VARCHAR2(50);
 BEGIN
   RETURN( SUBSTR(input_str) ) ;
 END example_unterminated;

would become;

CREATE OR REPLACE FUNCTION example_unterminated (
input_str  IN VARCHAR2
)
/**************************************************************************
   Function      : example_unterminated
   Author        : EnterpriseDB
   Description   : Returns substring
*/
/**************************************************************************
    Change
    Date          : sysdate
    Changes       : new changes
*/
/**************************************************************************/
RETURN VARCHAR2
AS
  var1         VARCHAR2(50);
BEGIN
  RETURN( SUBSTR(input_str) ) ;
END example_unterminated;

Updated Repair Handler

The following repair handler is updated:

  • ERH 2055 - Unsupported using Index Clause

    Removes USING INDEX LOCAL/GLOBAL clause from the partitioned table DDL statement.

    For example: Example 1 :

    CREATE TABLE tab(
   ID INT,
   OPEN_STATUS INT,
   CLOSED_DATE DATE,
   CONSTRAINT tab_pk PRIMARY KEY (ID, OPEN_STATUS,
   CLOSED_DATE) USING INDEX LOCAL (PARTITION CLOSED, PARTITION
   OPEN ) ENABLE
) PARTITION BY RANGE (OPEN_STATUS,CLOSED_DATE)
   (PARTITION CLOSED VALUES LESS THAN (0, MAXVALUE),
   PARTITION OPEN VALUES LESS THAN (1, MAXVALUE) );

    would become;

CREATE TABLE tab(
   ID INT,
   OPEN_STATUS INT,
   CLOSED_DATE DATE,
   CONSTRAINT tab_pk PRIMARY KEY (ID, OPEN_STATUS, CLOSED_DATE)
) PARTITION BY RANGE (OPEN_STATUS,CLOSED_DATE)
   (PARTITION CLOSED VALUES LESS THAN (0, MAXVALUE),
   PARTITION OPEN VALUES LESS THAN (1, MAXVALUE) );

Example 2:

CREATE TABLE tab2(
    ID NUMBER(19,0) NOT NULL ENABLE,
    P_DATE DATE,
    CONSTRAINT tab2_pk PRIMARY KEY (ID)
    USING INDEX  GLOBAL PARTITION BY HASH (ID)
    (PARTITION SYS_P255 ,
    PARTITION SYS_P256 )
    )
PARTITION BY RANGE (P_DATE) INTERVAL (NUMTODSINTERVAL(7,''DAY''))
(PARTITION P_FIRST  VALUES LESS THAN (TO_DATE(''2017-01-04
00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'',
''NLS_CALENDAR=GREGORIAN'')) )  ;

would become;

CREATE TABLE tab2(
  ID NUMBER(19,0) NOT NULL ENABLE,
  P_DATE DATE,
  CONSTRAINT tab2_pk PRIMARY KEY (ID)
  )
PARTITION BY RANGE (P_DATE) INTERVAL (NUMTODSINTERVAL(7,''DAY''))
(PARTITION P_FIRST  VALUES LESS THAN (TO_DATE(''2017-01-04
00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'',
''NLS_CALENDAR=GREGORIAN'')) )

Knowledge Base

New Knowledge Base entries

The following new Knowledge Base entries have been added;refer to the Knowledge Base section on the Migration Portal for workaround details.

  • Remainder function:

    In Oracle, the REMAINDER function behaves differently than the MOD function. However, Advanced Server does not support the REMAINDER function.

  • BFILENAME function:

    In Oracle, the BFILENAME function returns the BFILE locator object from a specified directory and filename. This function is used to access the data within the BFILE.

  • Median function:

    Oracle supports the MEDIAN function to calculate the median of the values of an expression. Advanced Server Version 12 supports the MEDIAN function. However, the earlier versions of Advanced Server do not support this function.

  • SOUNDEX function:

    Oracle supports the SOUNDEX function to return a string that contains the phonetic representation of a string. In Advanced server, the SOUNDEX function can be used after creating a fuzzystrmatch extension.

  • DBMS_UTILITY package:

    Oracle uses the COMPILE_SCHEMA procedure in the DBMS_UTILITY package to compile all procedures, functions, packages, and triggers in the specified schema. However, the COMPILE_SCHEMA procedure does not exist in the EDB Advanced Server DBMS_UTILITY package. Hence while deploying the migrated schema on EDB Advanced Server, a runtime error occurs.

  • DBMS_DDL.WRAP and CREATE_WRAPPED functions:

    In Oracle, DBMS_DDL.WRAP or DBMS_DDL.CREATE_WRAPPED function is used to obfuscate code. However, in Advanced Server, EDBWRAP function is used for obfuscation. Before migrating schema, you must unwrap the PL/SQL object from Oracle and then wrap the PL/SQL object in Advanced Server using EDBWRAP utility.

  • Oracle TZ_OFFSET functions:

    In Oracle, the TZ_OFFSET() is used to get the time zone offset corresponding to the value you specify. However, in Advanced Server, you can write a function around Advanced Server catalog pg_timezone_names or you can directly query pg_timesone_names to get the time zone offset.

Updated Knowledge Base entry

The following knowledge base entry has been updated:

  • Merge statement:

    In Oracle, MERGE statement is used to conditionally insert or update a table. In Advanced Server, you can use ON CONFLICT DO UPDATE or UPSERT statement.