I am trying to call the following Oracle stored function from my Fortran program but I am unsure of the syntax to use.
I can successfully call SELECT, UPDATE and DELETE statements using the following code but I'm not sure how qstring should be structured to call a function. Any assistance would be much appreciated. Thank you.
I am not that familiar with SQL and Oracle stored functions/procedures, but the code fragment you show already contains the answer: the string qstring should be a valid SQL statement (in the Oracle dialect ;)). So if you format qstring in the same way you would do it to apply the stored function in an interactive session for instance, that should work in a Fortran program as well.
Thanks for your response. I managed to call the function successfully by passing in the SQL statement:
qstring = "SELECT IRIS.ri_gndmsmt_pkg.delete_aplot_gndmsmt("//trim(cGndMsID)//",'"//trim(comments)//"') FROM DUAL"//char(0)
but received this error:
If the problem is a COMMIT, is it true that ForDBC deals with these somehow? I don't use COMMIT anywhere else in my sucessful SELECT, DELETE etc calls and have never had any ORA-14552 errors such as this. Or is it that functions are dealt with differently?
The function runs successfully within SQL Developer but requires the parameters to be set and 'BEGIN' and 'END' included etc. I
You are correct - the function deletes data from multiple tables in Oracle. After reading through the ForDBC documentation I don't think it is possble to call a stored function. The documentation only refers to standard DELETE, SELECT, UPDATE calls etc.
The reason we wanted to use a stored procedure was because the user running the program doesn't have (and can't be given) these deletion privileges for the relevant tables within Oracle.
I do a lot of SQL work, you would be better off using a DLL in a language that does SQL better and calling the DLL from the Fortran code, FORDBC is ok, but it is a bit limited. There are plenty of examples on the web so it should not be that hard as you only have a single command.