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. 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
    
  3. All Oracle functions missing in MySQL have to be replaced by the appropriate MySQL equivalents:

    Oracle MySQL
    ADD_MONTHS($datetime, $n) DATEADD(month, $n, datetime)
    CHR(num) CHAR(num USING ASCII)
    CURRENT_DATE NOW()
    CURRENT_TIMESTAMP NOW()
    DECODE($exp, $when, $then, ...) CASE $exp WHEN $when THEN $then ... END
    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)
    NVL2($var,$expr1,$expr2) CASE WHEN $var IS NOT NULL THEN $expr1 ELSE $expr2 END
    SYS_GUID() REPLACE(UUID(), '-', '')
    TO_CHAR Click here
    TO_DATE Click here
    TRUNC TRUNCATE