For sure this will work if you have FMS12Advanced running on Windows Server and the client machines are also Windows. I don't have any experience with other configurations, so hopefully someone else will reply that does.
Just make sure you have the ESS data source configured so that clients share the ODBC authentication credentials. I also recommend having a special account setup on the SQL server and use this as the shared authentication account for all users. The SQL dB admin can grant this account any privs needed for your desired FM/ESS functionality.
I also suggest you download and read the ESS In Depth Techbrief linked below.
Yes this does work. OSX Server as well as Windows. If on OSX, the Actual Technologies ODBC drivers are needed. They do have a FileMaker server specific license. They are good to work with.
As Doug advises I'd set the DSN(s) up with dedicated Oracle accounts that grant the access needed and no more. No extra tables/views etc. In fact of views are possible use them as the heavy lifting is then on the Oracle side.