Expect a few minor bumps on the road, but on whole should be relatively easy.

From the list of features you currently use, there should only be a few synctactic or semantic differences, in general easy to fix or account for. The fact that you do not use PL/SQL and/or Stored Procedures is a plus. A good rule of thumb is to try and stick to SQL-92 which most DBMSes support, in particular both Oracle and MySQL. (Note this is not the current SQL standard which is SQL-2008).

A few of the differences:

• "LIMIT" is a famous one: to limit the number of rows to retrieve in the results list, MySQL uses LIMIT n, at the end of the query, Oracle uses RowNumber() in the WHERE clause (which is pain, for you also need to reference it in the SELECT list...)
• Some datatypes are different. I think mostly BOOLEAN (but who uses this ;-) ) Also some I think subtle differences with the DATETIME type/format.
• Some function names are different (SUBSTRING vs. SUBSTR and such...)

Just found what seems to be a good resource about differences between SQL implementations.

Reading the responses from others, yeah, DDL, could be a problem. I discounted that probably because many applications do not require DDL, you just need to set the data schema etc. at once, and then just use SQL for querying, adding or updating the data.

I believe that maintaining a single set of SQL resource files with MySQL and Oracle, has several disadvantages as being caught between backward compatibility and solve a particular problem. it is best to have a sql for each SQL engine and thus maximize the capabilities of each.

Features that look identical in a brochure may be implemented very differently.

see these examples

Limiting result sets

MYSQL

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n

ORACLE

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
)
WHERE rownumber <= n

Limit—with offset

MYSQL

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip

ORACLE

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
    columns
  FROM tablename
)
WHERE rn > skip AND rn <= (n+skip)

You can check this Comparison of different SQL implementations

In addition to the stuff others have mentioned, oracle and mysql handle outer joins quite differently. Actually, Oracle offers a syntax that mySql won t cope with, but Oracle will cope with the standard syntax.

Oracle only:

SELECT a.foo, b.bar
  FROM a, b
 WHERE a.foo = b.foo(+)

mySql and Oracle:

SELECT a.foo, b.bar
     FROM a 
LEFT JOIN b 
       ON (a.foo=b.foo)

So you may have to convert some outer joins.

You definitely won t be able to keep your DDL the same. As far as DML goes, there are many similarities (there s a core subset of ANSI SQL standard supported by every database) but there are some differences as well.

To start, MySQL uses auto increment values and Oracle uses sequences. It s possible to work around this (sequence + trigger on Oracle side to simulate auto increment), but it s there. Built-in functions are quite different.

Basically, depending on what exactly you intend to use it may or may not be possible to keep one set of statements for both. Incidentally, even with Hibernate dialects it s not always possible to have the same set of queries - HQL is great but not always enough.

Oracle treats empty strings as nulls. MySQL treats empty strings as empty strings and null strings as null strings.