Hi all,

Is it possible to use StoredFunctionCall to execute an Oracle stored function that returns a ref cursor? I've not been able to find any information on how this can be done. I've written the following sample code but it fails to work (getting a "PLS-00306: wrong number or types of arguments" error despite my arguments being correct). The failure occurs on "session.executeQuery(query)".

StoredFunctionCall call = new StoredFunctionCall();
call.setProcedureName("MY_FUNCTION");
call.setResult("REF_CURSOR");
call.addNamedArgumentValue("FROM_DATE", dateFrom);
call.addNamedArgumentValue("TO_DATE", dateTo);
call.addNamedArgumentValue("NAME", "myname")

ValueReadQuery query = new ValueReadQuery();
query.setCall(call);
    	
session.executeQuery(query);

I have to add that I have executed the "my_function" Oracle function successfully using plain JDBC and OracleCallableStatement so I can confirm the function is fine. I have also switched "dateFrom" and "dateTo" from "java.util.Date" to "java.sql.Date" as I thought perhaps these were causing the error but it didn't make a difference.

Unfortunately there are no real solid examples of how to return a ref cursor from an Oracle function using StoredFunctionCall. Can any help and give me pointers? Thanks in advance.


John