Technical Update for EDB Migration PortalⓇ 2.4.0

This article was originally published on December 12, 2019

• Advanced Server version 12 support

• Minor UI changes

New Repair Handlers

• ERH 2080 - Converts SIMPLE_INTEGER data type to INTEGER type.
  For example, CREATE OR REPLACE PROCEDURE hr.mproc IS
  v_col1 SIMPLE_INTEGER := 1;
  BEGIN
  DBMS_OUTPUT.PUT_LINE('Simple Integer is' || v_col1);
  END;

• ERH 2081 - Converts NATURAL data type to INTEGER type.
  For example, CREATE OR REPLACE PROCEDURE hr.mproc IS
  v_col1 NATURAL := 1;
  BEGIN
  DBMS_OUTPUT.PUT_LINE('Natural Integer is' || v_col1);
  END;
  would become CREATE OR REPLACE PROCEDURE hr.mproc IS
  v_col1 INTEGER := 1;
  BEGIN
  DBMS_OUTPUT.PUT_LINE('Natural Integer is' || v_col1);
  END;
   
• ERH 2082 - Removes NOT NULL clause from VARRAY collection type declaration within PL/SQL object.
  For example, TYPE t1 IS VARRAY(10) OF INTEGER NOT NULL;
  would become TYPE t1 IS VARRAY(10) OF INTEGER;
   
• ERH 2083 - Removes NOT NULL clause from nested table type declaration within PL/SQL object. 
  For example, TYPE nt IS TABLE OF NUMBER NOT NULL;
  would become TYPE nt IS TABLE OF NUMBER;
   
• ERH 2084 - Removes NOT NULL clause from index-by table type declaration within PL/SQL object.
  For example, TYPE ft IS TABLE OF VARCHAR2(10) NOT NULL INDEX BY BINARY_INTEGER;
  would become TYPE ft IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
   
• ERH 2085 - Converts ARRAY to VARRAY collection type declaration within PL/SQL object.
  For example, TYPE t1 IS ARRAY(10) OF INTEGER; would become TYPE t1 IS VARRAY(10) OF INTEGER;
   
• ERH 1010 - Removes the label name from the END clause in SPL object if the label name used in the start of the clause is different. For example
  For example, CREATE OR REPLACE FUNCTION hr.emp_comp (n INTEGER) RETURN NUMBER IS
  DECLARE
  counter INTEGER;
  BEGIN
  LOOP EXIT WHEN counter = n;
  counter := counter + 1;
  END LOOP;
  RETURN 100;
  END emp_comp_123;
  would become CREATE OR REPLACE FUNCTION hr.emp_comp (n INTEGER) RETURN NUMBER IS DECLARE
  counter INTEGER;
  BEGIN
  LOOP EXIT WHEN counter = n;
  counter := counter + 1;
  END LOOP;
  RETURN 100;
  END ;

Knowledge Base

• Use of EXECUTE IMMEDIATE USING IN/OUT parameter
  Oracle supports use of syntax EXECUTE IMMEDIATE <SQL or SPL Commands> [USING <bind variable list>]. However, Advanced Server does not support IN/OUT parameters in EXECUTE IMMEDIATE USING syntax.
   
• Operator does not exist: timestamp without timezone and double precision
  Oracle supports addition operation between timestamp without time zone and double precision datatypes in SQL operations or in VIEW/MVIEW creation. However, in Advanced Server double precision datatype needs to type cast to numeric data type while adding it to timestamp without time zone datatype.
   
• Extract function for XML
  Oracle supports extract() function for both extracting date part as well as for extracting XML data.
   
• Migrating Database Links
  While migrating the Database links from Oracle to Advanced Server sometimes following errors occur:
  Error
  ORA-12154: TNS: could not resolve the connect identifier specified
  server "db_link_name" does not exist.

Fixed Customer Defects

• Issue with procedure compatibility fails for GREATEST function with error message as “Incompatible function/package found: GREATEST”. [NGMTK 1154, Support Ticket 934088]
• EDB migration portal doesn't work for a view already extracted [NGMTK 1109, Support Ticket 922742]