Intel® Fortran Compiler
Build applications that can scale for the future with optimized code designed for Intel® Xeon® and compatible processors.

Calling an Oracle stored function

SoniaG
Novice
855 Views

Hello,

I am trying to call the following Oracle stored function from my Fortran program but I am unsure of the syntax to use.  

SoniaG_0-1619151837351.png

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.

SoniaG_2-1619152525475.png

 

 

 

0 Kudos
5 Replies
Arjen_Markus
Honored Contributor I
841 Views

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.

0 Kudos
SoniaG
Novice
795 Views

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:

SoniaG_0-1619655859190.png

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

 

0 Kudos
Arjen_Markus
Honored Contributor I
781 Views

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.

0 Kudos
SoniaG
Novice
758 Views

The function runs successfully within SQL Developer but requires the parameters to be set and 'BEGIN' and 'END' included etc.  I

SoniaG_0-1619746989997.png

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 

0 Kudos
JohnNichols
Valued Contributor III
736 Views

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. 

0 Kudos
Reply