Your question is a little hard to answer without more detail. It sounds like you're building the script locally, then trying to run it on a Server-hosted database. If this is the case, the problem could be coming from the DSN being present on the local machine, but missing on the server.
If that's not what's happening, then please provide some more information regarding your query so we can troubleshoot it more precisely.
+1 Mike_Mitchell. I do not work with Oracle but the problems should be similar to other ODBC. On my experience Mac ODBC tends to be slower and buggier than Windows. I would start clocking the views first, as if they are done incorrectly they can take a long time to refresh. E.g. if your view query is date based, first run is usually takes longer. (OK, it's Oracle, so it should not).
Export Query - try to write and run one in Oracle and than copy and paste. If it does not work, try to create a stored procedure and run this from through your ODBC connection.
If you stuck completely, you can try to schedule an export from Oracle and import it into FileMaker.
sounds like you already have ESS set up, so you could just do an export of those ESS tables at night from inside FM and not worry about building the proper SQL query?
We are currently viewing data from Oracle tables
Exporting appears to be the way to solve this issue. Thanks so much. Since the export takes nearly 10 minutes to run, should I have 2 scripts, one for export and one for import. Or can the script pause for 600 seconds before importing?
should I have 2 scripts, one for export and one for import. Or can the script pause for 600 seconds before importing?
No, that's asking for timing errors. If you do it all in one script FM will wait on the export script step before continuing on so you will never run into timing issues.
10 minutes for 5000 records seems like a very long time. Is this straight from an Oracle table or a complicated view?
It's a complicated view. Many thanks for your help. I'll be running a test in a few minutes