"There are RDBMS agnostic interface layers that can help somewhat"
Which boils down to using the database as a simple data dump with all the data logic and security outside the database, and usually re-implemented in each and every application (and usually data gets duplicated and becomes out of sync too) - unless a true common middleware is used (very, very rarely).
There are far more differences than some SQL "dialect" from data/index/etc. types to transaction management to storage options - all of them can impact data safety and performances. especially for very large databases.
What is OK for a CMS may not be good for a billing system or something managing lots of transactions concurrently.
In many situations the data are more important than the application using them - applications come and go, data may stay for very long periods (especially when you have mandated retention periods)
Note that this is true for any RDBMS, not Oracle only.
Sometimes it was (and sometimes still is) better to pay the exorbitant Oracle prices than trying to achieve the same yourself, spend eventually the same amount, and find your application can't work or meet performance requirements.
