error using performCustomOperation to call a stored proced. which creates a sequence

[claudiobosticco]

SmartClient Version: v8.3p_2013-02-25/PowerEdition Deployment (built 2013-02-25)

I've got a stored procedure which drops and create a sequence.

I've got an operation binding to call it:

        <operationBinding operationType="custom" operationId="rebuildSequencePRG_ELB_TAXI">
            <customSQL>
                CALL REBUILD_SEQ_PRG_ELB_TAXI()
             </customSQL>
        </operationBinding>

to me, it seems an use case for performCustomOperation, which I'm using like this:

myDS.performCustomOperation("rebuildSequencePRG_ELB_TAXI")

the stored procedure gets executed, but I got an error:

2013-03-04 12:50:19,444 INFO  SQLDriver [builtinApplication.rebuildSequencePRG_ELB_TAXI] Executing SQL query on 'dbJpcEP': CALL REBUILD_SEQ_PRG_ELB_TAXI() 
2013-03-04 12:50:19,466 WARN  RequestContext dsRequest.execute() failed:  
java.sql.SQLException: Impossibile eseguire l'operazione di FETCH su un'istruzione PLSQL: next
	at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:568)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
	at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:598)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:511)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:505)
	at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:480)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:577)
	at com.isomorphic.sql.SQLDriver.executeQuery(SQLDriver.java:848)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:407)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:401)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1394)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:306)
	at com.isomorphic.sql.SQLDataSource.executeCustom(SQLDataSource.java:266)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1403)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:726)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2030)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:216)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:173)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:138)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:74)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

[Isomorphic]

Can you translate that error message to English?

[claudiobosticco]

something like :
'Unable to execute FETCH statement in PLSQL: next'

[Isomorphic]

We can't really tell what's going wrong here as it seems to be something misfiring in your PLSQL.

Basically what's going on is:
1. we execute your customSQL via Statement.executeQuery()
2. we receive a JDBC ResultSet object
3. we begin to iterate through the results by calling ResultSet.next(), and an exception is thrown by Oracle

It sounds like your PLSQL routine doesn't really need to return data. Returning an empty ResultSet should be fine. You might currently be returning some other kind of result which the JDBC driver isn't handling very well.

[claudiobosticco]

actually my stored procedure doesn't return data, is simple as that:

create or replace procedure dbsales.rebuild_seq_prg_elb_taxii 
is
begin

execute immediate 'drop sequence dbsales.sequence_prg_elb_taxi';
  execute immediate 'create sequence dbsales.sequence_prg_elb_taxi start with 1 increment by 1 cache 200';

end;
/

I was expecting that <customSQL> would handle it.
I'm trying to modify it to return a cursor:

CREATE OR REPLACE PROCEDURE DBSALES.REBUILD_SEQ_PRG_ELB_TAXI ( my_cursor out types.ref_cursor )
 IS
BEGIN

EXECUTE IMMEDIATE 'DROP SEQUENCE DBSALES.SEQUENCE_PRG_ELB_TAXI';
  EXECUTE IMMEDIATE 'CREATE SEQUENCE DBSALES.SEQUENCE_PRG_ELB_TAXI START WITH 1 INCREMENT BY 1 CACHE 200';
OPEN my_cursor FOR SELECT 1 FROM dual WHERE 1 = 0;
 
END;
/

but it's throwing another error:

java.sql.SQLException: ORA-06553: PLS-306: wrong number or types of arguments in call to 'REBUILD_SEQ_PRG_ELB_TAXI'

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:926)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:200)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:197)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1415)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1757)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:2006)
	at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:1709)
	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:549)
	at com.isomorphic.sql.SQLDriver.executeQuery(SQLDriver.java:848)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:407)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:401)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1394)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:306)
	at com.isomorphic.sql.SQLDataSource.executeCustom(SQLDataSource.java:266)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1403)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:726)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2030)
	at com.juve.jpcep.contratti.fatture.FattureContabDMI.rebuildSequencePRG_ELB_TAXI(FattureContabDMI.java:43)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at com.isomorphic.base.Reflection.adaptArgsAndInvoke(Reflection.java:972)
	at com.isomorphic.datasource.DataSourceDMI.execute(DataSourceDMI.java:416)
	at com.isomorphic.datasource.DataSourceDMI.execute(DataSourceDMI.java:64)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2027)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:216)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:173)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:138)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:74)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

[Isomorphic]

This new attempt is failing because you've declared a cursor as a parameter to the stored procedure, but you aren't passing one.

We don't have a lot of expertise in PL-SQL but this search seems to have simple instructions for returning an empty result.

Note also, so far as we know, it should not be necessary to explicitly return an empty result. We're not sure why your particular combination of JDBC driver and DB version are throwing an error in this case.