NULL in SQL92 doesn't mean "nothing". It means "unknown". Expecting 'string' || NULL to return 'string' implies that you know what the value NULL is, in this case - i.e., the empty string. But ... you don't, the value is "unknown".
'string' || NULL returns NULL because the "unknown" operand means the result of the concatenation is, itself, "unknown".
This is entirely consistent with how the NULL operand is treated with other operators. "value operator NULL" always returns "NULL".
Oracle itself claims they may treat the empty string and NULL correctly in a future release, so it's not good to have the Oracle code depend on this, either. While it is hard to imagine them following through on their threat to adopt the standard behavior, we'd hate to see our code break if they ever did so ...
The problem here is that Oracle is inserting NULL when you do an INSERT ... VALUES ('') rather than the empty string. This is dead wrong. The empty string is not an "unknown" value - the value is perfectly known, i.e. the string with no characters. As I mentioned above, Oracle recognizes that this treatment is incorrect and may change it in a future release. The reason they haven't, I'm sure, is because so many customers have so much code dependent on this incorrect behavior that changing is impractical. At the least they'll need to have a "-empty_string_bug_compat" switch or the like if they ever do switch to the standard semantics.
Regardless of your personal feelings, in this project we want to avoid Oracle dependencies, in particular a dependency on non-standard semantics that Oracle itself recognizes as being incorrect.
