FileMaker only directly communicates with Oracle through ODBC, which are SQL calls. ODBC does not support stored procedures or batch files or anything like that. I know you can use the FM server to run a schedule script that runs a batch file or stored procedure, but that would be happening at that scheduled time and not necessarily when you need it to happen live for the end user.
I am assuming there is some special reason for running it an Oracle stored procedure instead of a FileMaker script? If not, I highly recommend using a FileMaker script instead since that is what FileMaker is designed to do.
Theoretically, I think you can get the ScriptMaster plugin to let you program in Groovy (light weight versin of Java) that you could program to talk to Oracle and run a stored procedure. It will be complicated and probably expensive, but if it is important, talk to Jesse Barnum at 360 Works in Atlanta.
Are you sure this isn't something where you just need some assistance getting it to work with a FIleMaker script?
One more thought, could the stored procedure be triggered by PHP? You could have a web calculation in FileMaker create the batch script and run it in a web window that would then initiate the Oracle stored procedure.
Check the 360Works website, they have a plugin that can make FM scripts callable like web services from external applications like oracle
Thanks for taylorsharpe and gpupita reply so quickly.
Because many stored procedures has done in oracle of my client's, so they want just call it then return to filemaker.
But if calling stored procedures in ms sql server from FileMaker can be work, so I think it for oracle also can be do?
Got a feeling I've trigged stored procedures with the Execute SQL script step - bit hazy as was yrs ago. (not to be confused with FM12's new executeSQL function)
Quick Google shows someone else doing that here http://filemakeraddict.blogspot.co.uk/2008/06/understanding-filemaker-ess-and.html
Thanks, I aslo got that page too before, and email to Tim, but he not reply yet for this.
So anybody know how to use executeSQL function any methods to do it ? (No plug-ins will be better.)
To trigger the Stored Proceedure (S) - it should just be a matter of
1. setting up the Oracle source in ODBC
2. coding relevant SQL code to call your SP in 'Execute SQL' SCRIPT STEP in Filemaker
With regard the Oracle SQL syntax to do that I'm not sure. Your probably best looking at Oracle support sites.
You can definately call Oracle stored procedures from Filemaker. The interface is just not very robust. First way is through the Execute SQL SCRIPT STEP. This is different than the Execute SQL FUNCTION. It requires an ODBC connection to be setup on the Filemaker Client. If you need some examples, let me know. The drawbacks are you only get a single return value and no "binding" of fields to data, and it has to be setup on each Filemaker workstation.
The other way is using ESS. What I have been doing for awhile is setting up a Utlitity table, that I use to save the User name, procedure name and parameters. I then have a trigger on this table in Oracle that runs when the table is updated and it runs the stored procedure specified, and also has some fields to return results. There are some caveats with this methodology as well. You have to commit the Uility record to get your process to roll, and there are times you don't want to have to commit on a stored procedure. There are ways around these issues, but you will have to do some special coding in the Stored Procs and that often introduces some added complexity. The advantage of this method is that it all runs off the ODBC setup on the Server, so no special setup on the workstations, which means it will ALSO work with FMGo apps.
I would urge everyone interested in this functionality to BEG FMI to update the old Execute SQL _SCRIPT STEP_ to allow it to execute through the ODBC Driver on the Server as an option. I would also like to see the ExecuteSQL FUNCTION be able to run directly against the underlying ESS table instead of parsing through Filemaker's ODBC driver as it is most horrible in performance with more than a few hundred records. If enough folks petition FMI, I think these would be relatively trivial to implement and would make a HUGE difference in what Filemaker can do with native SQL Databases.
Function or Script step? Be clear. TWO DISTINCT features. The ExecuteSQL FUNCTION will do you no good with Oracle. You need to use the ExecuteSQL SCRIPT STEP.
Here's a super simple example of what you would put into the Text part of the ExecuteSQL SCRIPT STEP to call a stored proc.
Still Can't work.
I used ExecuteSQL Script step, still can't do it. Thanks.
Can you provide more details? Are you getting any errors? Do you have permissions set properly on the Stored Proc. you are trying to call? Do you have an ODBC DSN setup properly on the Client Machine? Can you successfully test the ODBC DSN connection with your login in the ODBC Control panel?
Check a few of these things and let me know. If you still are stuck, let me know and if you can allow it, I can connect to you remotely and help you out.
Thank you so much, but the environment is in the intranet...
Are you getting any errors? Yes, I'll show you a picture later. (I'm outside)
Do you have permissions set properly on the Stored Proc. you are trying to call? Yes, permission is no problem, just call a record set or return a string.
Do you have an ODBC DSN setup properly on the Client Machine? Yes, I can see table list in the SQL Builder,
Can you successfully test the ODBC DSN connection with your login in the ODBC Control panel? Yes, work fine with ODBC DSN, successfully.