Home > Documentation > Oracle to MySQL

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 MySQL
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. Oracle string concatenation operator || must be replaced by MySQL CONCAT function
4. Oracle limitation by number of returned rows like where rownum <= 50 are replaced by limit 50 in MySQL
5. All Oracle functions missing in MySQL have to be replaced by the appropriate MySQL equivalents as it is described in this article