How to convert Oracle views into MySQL format

[Oracle to MySQL Converter]  [About Migration]

Migration of Oracle database to MySQL envolves translating CREATE VIEW statements into MySQL format. Syntax of statements to create views in Oracle and MySQL is similar but not identical. The primary difference is that two DBMS provide distinguished sets of built-in functions. That's why it is necessary to process each of those statement before passing it to the destination database management system. This article discovers basic techniques of converting Oracle views into MySQL format. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

  1. Oracle provides multiple features for creating views that are not supported by MySQL. Such features must be removed during conversion:

    • DEFAULT
    • FORCE / NO FORCE
    • WITH CHECK OPTION
    • WITH OBJECT IDENTIFIER
    • WITH READ ONLY
    • UNDER
    • XMLType views are not supported by PostgreSQL
  2. Built-in function TO_DATE must be replaced by STR_TO_DATE and TO_CHAR - by DATE_FORMAT MySQL function with respect with difference in date format specifiers in Oracle and MySQL illustrated by this table:

    Oracle MySQL Meaning
    DD %d Day (1 - 31)
    DY %a Abbreviated day (Sun - Sat)
    HH or HH12 %h Hour (1 - 12)
    HH24 %H Hour (0 - 23)
    MI %i Minutes (0 - 59)
    MM %m Month (1 - 12)
    MON %b Abbreviated month (Jan - Dec)
    MONTH %M Month name (January - December)
    RR %y 2-digit year, 20th century for 00-49
    RRRR %Y 2 or 4-digit year, 20th century for 00-49
    SS %s Seconds (0 - 59)
    YY %y 2-digit year
    YYYY %Y 4-digit year

  3. Oracle function ADD_MONTHS($datetime, $n) that adds $n months to $datetime must be replaced by DATEADD(month, $n, datetime) in MySQL
  4. Oracle function nvl($var, $expr) that returns $expr if $var is NULL must be replaced by ISNULL($var, $expr) in MySQL, while its more complicated version nvl2($var,$expr1,$expr2) that returns $arg2 if $var is NULL and $arg1 if $var is not NULL must be replaced by the following statement:
    CASE WHEN $var IS NOT NULL THEN $arg1 ELSE $arg2 END
    
  5. Oracle function DECODE($exp, $when, $then, ...) used to evaluate a condition has no direct equivalent in MySQL. It can be replace by the following statement:
    CASE $exp WHEN $when THEN $then ... END
    
  6. Oracle has special operator (+) that is specific notation for LEFT OUTER JOIN. So, must be converted into MySQL format as follows. In Oracle:
    SELECT ...
    FROM a,b
    WHERE a.id=b.id(+)
    
    In MySQL:
    SELECT ...
    FROM a
    LEFT JOIN b ON b.id = a.id
    
  7. All Oracle functions missing in MySQL have to be replaced by the appropriate MySQL equivalents:

    Oracle MySQL
    CHR(num) CHAR(num USING ASCII)
    CURRENT_DATE NOW()
    CURRENT_TIMESTAMP NOW()
    EXTRACT(YEAR FROM date) YEAR(date)
    EXTRACT(MONTH FROM date) MONTH(date)
    EXTRACT(DAY FROM date) DAY(date)
    EXTRACT(HOUR FROM time) HOUR(time)
    EXTRACT(MINUTE FROM time) MINUTE(time)
    EXTRACT(SECOND FROM time) SECOND(time)
    LENGTH CHAR_LENGTH
    LISTAGG GROUP_CONCAT()
    NVL(expr, replace) IFNULL(expr, replace)
    SYS_GUID() REPLACE(UUID(), '-', '')
    TRUNC TRUNCATE

More articles about Oracle, MySQL and other databases can be found at https://www.convert-in.com/articles.htm