Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Possible to return a ref cursor from Oracle function using StoredFunctionCall?
Possible to return a ref cursor from Oracle function using StoredFunctionCall? [message #557099] Sat, 04 September 2010 10:31 Go to next message
No real name is currently offline No real name
Messages: 1
Registered: September 2010
Junior Member
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



Re: Possible to return a ref cursor from Oracle function using StoredFunctionCall? [message #557474 is a reply to message #557099] Tue, 07 September 2010 14:46 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

This can be done using a StoredProcedureCall using,

useNamedCursorOutputAsResultSet()

or,

useUnnamedCursorOutputAsResultSet()

I'm not sure if it works with StoreFunctionCall,

try,
useUnnamedCursorOutputAsResultSet()


If it does not work, please log a bug, and as a workaround convert the function to a procedure.



James : Wiki : Book : Blog : Twitter
Re: Possible to return a ref cursor from Oracle function using StoredFunctionCall? [message #735629 is a reply to message #557099] Wed, 12 October 2011 12:31 Go to previous message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
No real name wrote on Sat, 04 September 2010 06:31
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





Still not possible or what? Why have "StoredFunctionCall" then? I can do this using "select function() from dual" and then resultset.next();rs=(ResultSet)resultset.getObject(1) to get the real result set... but this way I cannot map the ResultSet to the @entity. Darn.

edit: Also, sorry for "necroing" but I cannot find any useful resources on this, teh same as OP.

[Updated on: Wed, 12 October 2011 12:32]

Report message to a moderator

Re: Possible to return a ref cursor from Oracle function using StoredFunctionCall? [message #735664 is a reply to message #557099] Wed, 12 October 2011 12:31 Go to previous message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
No real name wrote on Sat, 04 September 2010 06:31
> 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


Still not possible or what? Why have "StoredFunctionCall" then? I can do this using "select function() from dual" and then resultset.next();rs=(ResultSet)resultset.getObject(1) to get the real result set... but this way I cannot map the ResultSet to the @entity. Darn.
Previous Topic:Extensible Entities
Next Topic:Cached Ordered Lists have null inserted during 2nd update
Goto Forum:
  


Current Time: Tue Sep 30 11:54:30 GMT 2014

Powered by FUDForum. Page generated in 0.04033 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software