Community
cancel
Showing results for 
Search instead for 
Did you mean: 
SoniaG
Beginner
164 Views

Calling an Oracle stored function

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
Valued Contributor III
150 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.

SoniaG
Beginner
104 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

 

Arjen_Markus
Valued Contributor III
90 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.

SoniaG
Beginner
67 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 

JohnNichols
Valued Contributor I
45 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. 

Reply