Calling Oracle Functions using the Spring Framework

Consider you have a function like this:


function get_content_value ( p_type numberic(10,0)
, p_name varchar2
)
return clob;

And you want to call this function using Spring. One of the easiest way to do this is by using the Spring StoredProcedure. There are some small things to consider though, when using this class to call Oracle Functions.

Calling the above function can be done like this:

So, in short, the trick is to:

  • Define output parameters before the input parameters
  • Call the setFunction(true) method
4 replies
  1. Marcel
    Marcel says:

    Hi,
    How can a call a function that has in and out parameters

    FUNCTION fr_validarcausante_fun (ai_cedula IN frsafitsolafi.numafi%TYPE, ao_men OUT VARCHAR2)
    RETURN NUMBER;

    Thanks in advance,

    • Erik Pragt
      Erik Pragt says:

      Hi Marcel,

      Instead of using the SqlParameters, you need to use SqlOutParameters, and you get the result of them using the ‘execute()’ method of the StoredProcedure. Hope that helps!

  2. Cristian
    Cristian says:

    I noticed the same thing
    “important that the out parameter is defined before the in parameter”

    Do you know if this ‘feature’ who eat half of my day is documented somewhere ?

    Thanks

    • Erik Pragt
      Erik Pragt says:

      Hi Christian,

      Not that I know of. I think I found this out by trial and error, and also spent quite some time on this. I hope this blogpost helped you out bit.

Comments are closed.