I'm trying to count the number of rows that returns a function on oracle BBDD. I've seen in some other questions (for instance: rs.last() gives Invalid operation for forward only resultset : last), that needs to be added ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY to callablestatement.
I've tried ResultSet.TYPE_SCROLL_INSENSITIVE , also ResultSet.TYPE_SCROLL_SENSITIVE... without result. I still get the same invalid operation exception.
What I'm missing?
Heres my code:
String call = "{ ? = call MyProcedure (?) }";
CallableStatement cstmt= conn.prepareCall(call, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
cstmt.setString(2,"param 1");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);
rset.last(); //Exception comes here
int rowsnumber = rset.getRow();
Thanks.
Edit:
As said in the comments, I add first the Oracle function I'm calling:
create or replace FUNCTION MyProcedure
( IdC IN VARCHAR2
)
RETURN sys_refcursor
IS
c_result sys_refcursor;
BEGIN
OPEN c_result FOR SELECT name, properties FROM city WHERE name = IdC;
RETURN c_result;
END;
And the Stack Trace:
Stack: java.sql.SQLException: Operación no válida para el juego de resultados de sólo reenvío : last
at oracle.jdbc.driver.InsensitiveScrollableResultSet.last(InsensitiveScrollableResultSet.java:646)
at MyPackage.CoreWS.ExProcedure(CoreWS.java:58)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at weblogic.wsee.jaxws.WLSInstanceResolver$WLSInvoker.invoke(WLSInstanceResolver.java:120)
at weblogic.wsee.jaxws.WLSInstanceResolver$WLSInvoker.invoke(WLSInstanceResolver.java:93)
at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:149)
at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:88)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:1136)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:1050)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:1019)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:877)
at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:419)
at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:868)
at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:422)
at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:169)
at weblogic.wsee.jaxws.WLSServletAdapter.handle(WLSServletAdapter.java:229)
at weblogic.wsee.jaxws.HttpServletAdapter$AuthorizedInvoke.run(HttpServletAdapter.java:667)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:368)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:163)
at weblogic.wsee.util.ServerSecurityHelper.authenticatedInvoke(ServerSecurityHelper.java:108)
at weblogic.wsee.jaxws.HttpServletAdapter$3.run(HttpServletAdapter.java:286)
at weblogic.wsee.jaxws.HttpServletAdapter.post(HttpServletAdapter.java:295)
at weblogic.wsee.jaxws.JAXWSServlet.doRequest(JAXWSServlet.java:128)
at weblogic.servlet.http.AbstractAsyncServlet.service(AbstractAsyncServlet.java:103)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:286)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:260)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:137)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:350)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:247)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3679)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3649)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:326)
at weblogic.security.service.SecurityManager.runAsForUserCode(SecurityManager.java:197)
at weblogic.servlet.provider.WlsSecurityProvider.runAsForUserCode(WlsSecurityProvider.java:203)
at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:71)
at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2433)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2281)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2259)
at weblogic.servlet.internal.ServletRequestImpl.runInternal(ServletRequestImpl.java:1686)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1646)
at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:270)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:348)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:333)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:54)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:640)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:406)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:346)
The ability to move its cursor only forward is the default behavior for a ResultSet and is the only cursor movement possible with drivers that implement only the JDBC 1.0 API. This kind of result set has the type ResultSet.TYPE_FORWARD_ONLY and is referred to as a forward only result set.
If a driver implements the cursor movement methods in the JDBC 2.0 core API, its result sets can be scrollable. A scrollable result set's cursor can move both forward and backward as well as to a particular row. The following methods move the cursor backward, to the first row, to the last row, to a particular row number, to a specified number of rows from the current row, and so on: previous, first, last, absolute, relative, afterLast, and beforeFirst.
Can you please confirm the version of your API ??
Reference: Result set
Also as suggested by Mark Rotteveel in comments, why don't you make the cursor as an outparameter and fetch it explicitly instead of using RETURN in the function and verify if it is working?