How to convert MySQL views into Oracle format

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 here