How to convert MySQL views into Oracle format

[MySQL to Oracle Converter]  [Download]  [Buy]

Database migration from MySQL to Oracle server involves conversion of CREATE VIEW statements according to the destination DBMS syntax. Although syntax of create-views statements in MySQL and Oracle is similar, it is not equal. The primary challenge is that two database management systems provide different sets of built-in functions. This whitepaper discovers basic rules of conversion views from MySQL to Oracle.

  1. all symbols `` around database object names must be replaces by quotes '"'
  2. MySQL function STR_TO_DATE must be replaced by TO_DATE and DATE_FORMAT - by TO_CHAR Oracle function paying attention to difference in date format specifiers in MySQL and Oracle as listed in this table:

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

  3. MySQL function DATEADD($date, interval N $interval_name) can be converted into Oracle expression '$date + interval N $interval_name'. For example:
    DATE_ADD(SYSDATE(),INTERVAL 1 MONTH);
    
    will become
    SYSDATE + INTERVAL 1 MONTH
    
  4. datediff($date1, $date2) can be converted into TRUNC($date1-$date2)
  5. MySQL function timediff can be emulated in Oracle as follows:
    CREATE OR REPLACE FUNCTION timediff(dat1 IN DATE, dat2 IN DATE)
    RETURN VARCHAR2 AS
    result VARCHAR(50);
    BEGIN
      result:=CONCAT(TRUNC(24*(dat1-dat2)),':');
      result:=CONCAT(result,TRUNC(MOD(60*MOD(24*(SYSDATE-TO_DATE(
        '2017-05-01','YYYY-MM-DD')),24),60)
      ));
      result:=CONCAT(result,':');
      result:=CONCAT(result,TRUNC(MOD(60*MOD(60*MOD(24*(SYSDATE-TO_DATE(
        '2017-05-01','YYYY-MM-DD')),24),60),60)
      ));
      RETURN result;
    END timediff;
    

  6. other MySQL date functions must be converted according to these rules:

    MySQL Oracle
    CURDATE() CURRENT_DATE
    CURTIME() CURRENT_TIME
    DAY($date) TO_NUMBER(TO_CHAR($date,'DD'))
    HOUR($date) TO_NUMBER(TO_CHAR($date,'HH24'))
    MINUTE($date) TO_NUMBER(TO_CHAR($date,'MI'))
    MONTH($date) TO_NUMBER(TO_CHAR($date,'MM'))
    NOW() SYSDATE
    WEEK($date) TO_NUMBER(TO_CHAR($date,'WW'))
    YEAR($date) TO_NUMBER(TO_CHAR(dat,'YYYY'))

  7. MySQL function ISNULL($var, $expr) that returns $expr if $var is NULL must be replaced by nvl($var, $expr) in Oracle
  8. MySQL function GROUP_CONCAT must be converted into Oracle equivalent LISTAGG()

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