Calling an Oracle stored function

[SoniaG]

Hello,

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.

[Arjen_Markus]

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.

[SoniaG]

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?

Thank you

[Arjen_Markus]

What happens if you issue this very same command in an interactive session? From the name of the stored procedure I am guessing that it does indeed change the contents, otherwise I at least would not include the word "delete" in its name.

[SoniaG]

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.

Thanks 

[JohnNichols]

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. 

Good luck.