insert into MyTable (c1,c2) values ('val1','');
this is the same as executing:
insert into MyTable (c1,c2) values ('val1',null);
IMHO this is plain wrong. '' does not equal NULL from a programmer's point of view.
Inserting an empty string (or '') can be convenient to make your prepared statements easier.
Suppose, in the above example, c2 is an optional parameter. When c2 is not available you insert ''. In this case a prepared statement to select records from MyTable could be:
select * from MyTable where c1 = ? and c2 = ?
After substitution the resulting statement for MySQL will be:
select * from MyTable where c1 = 'example' and c2 = ''
And for Oracle:
select * from MyTable where c1 = 'example' and c2 = null
Which is wrong.
So, if Oracle replaces my empty string with null, the prepared statement should be:
select * from MyTable where c1 = ? and coalesce(c2,'dontcare') = coalesce(?,'dontcare')
(please see also this link)
This works for both MySQL and Oracle ... of course.