Errors performing Oracle Stored Procedure using 'custom' operation

[simokris]

Hi.

ISC Versions:

• SNAPSHOT_v10.1d_2015-08-16/EVAL Development Only
• v10.0p_2015-08-20/Enterprise Development Only

I'm trying to call a stored procedure from a DMI using 'custom' operation, but with bad results.

I tried to call a procedure without OUT paramaters.

OPERATION BINDING:

<operationBinding operationType="custom" operationId="execProcedure">
            <customSQL>
                CALL PROC($criteria.START_DATE,$criteria.END_DATE)
            </customSQL>
</operationBinding>

DMI:

try {
            SQLTransaction.startTransaction(rpcManager, "dbTest");
            DSRequest execProcedure = new DSRequest("EXAM", "custom", rpcManager);
            execProcedure.setOperationId("execProcedure");
            execProcedure.addToCriteria("START_DATE", new Date(1443564000000L));
            execProcedure.addToCriteria("END_DATE", new Date(1448838000000L));
            DSResponse RespExecProcedure = execProcedure.execute();

            DSRequest fetchView = new DSRequest("EXAM", "fetch", rpcManager);
            DSResponse respFetchView = fetchView.execute();

            SQLTransaction.commitTransaction(rpcManager, "dbTest");
        }catch(Exception e){
            e.printStackTrace();
        }

ERROR:

2015-11-18 10:12:08,769 INFO  SQLDriver [builtinApplication.execProcedure] Executing SQL query on 'dbTest': CALL PROC(TO_DATE('2015-09-30 00:00:00','YYYY-MM-DD HH24:MI:SS'),TO_DATE('2015-11-30 00:00:00','YYYY-MM-DD HH24:MI:SS'))
nov 18, 2015 10:12:11 AM oracle.jdbc.driver.OracleResultSetImpl next
SEVERE: 6502E71E Throwing SQLException: 166next
2015-11-18 10:12:11,462 DEBUG DSRequest freeOnExecute is false for request of type custom on DataSource EXAM - not freeing resources!
java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next
    at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:577)
    at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:657)
    at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:570)
    at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:564)
    at com.isomorphic.sql.SQLTransform.toListOfMapsOrBeans(SQLTransform.java:539)
    at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:654)
    at com.isomorphic.sql.SQLDriver.executeQuery(SQLDriver.java:1013)
    at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:559)
    at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:553)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1916)
    at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:444)
    at com.isomorphic.sql.SQLDataSource.executeCustom(SQLDataSource.java:405)
    at com.isomorphic.datasource.DataSource.execute(DataSource.java:1991)
    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:2548)
    at com.example.dmi.ProcedureDMI.testProcedure(ProcedureDMI.java:99)

Observing the error, I realized that the jdbc driver uses executeQuery (instead execute()), so it expects a resultSet that Oracle Procedure is not possible without the use of OUT parameters (which ISC can't handle, see next paragraph)

So I tried to add an OUT parameter in my Stored Procedure but, How i take the OUT parameter inside the <customSQL> block?

[Crack]

It should work if you set sqlType to update:

<operationBinding operationType="custom" operationId="execProcedure" sqlType="update">
            <customSQL>
                CALL PROC($criteria.START_DATE,$criteria.END_DATE)
            </customSQL>
</operationBinding>

[simokris]

It should work if you set sqlType to update:

Oh thank you so much Crack. I hadn't thought about the type.
It solved my problem but, just to know, is there any way to retrieve Procedure OUT parameter?
If not, is there a possibility to see this feature in the future?

[Blama]

Hi simokris,

please see this thread.
I'd think (I did not try it) that you can get what you want with a Stored Procedure wrapped in a Table Function (see #9) that returns each out-Param of the Stored Procedure as column/field of the (only) returned row.

Best regards
Blama

[Crack]

Unless it does DML, then it will fail with ORA-14551: cannot perform a DML operation inside a query. If you can get by without your operation being transactional, you can mark your Table Function function with pragma autonomous_transaction.

[Medioman92]

Let me just say that this is "workaround programming" and in the best scenario will lead to a lot of confusion and to an unmaintainable architecture
The database's (Oracle in this case) code should stay as much as possible clear and linear. ISC should instead use the correct JDBC api which in this case is "execute" and not "executeQuery" or "executeUpdate"; and possibly a future handling for output parameters

[Isomorphic]

If you think there's a better approach here, try designing the feature: show portable (not Oracle-specific) JDBC code and corresponding XML declarations that would go into a .ds.xml file, and compare it to the code necessary to use JDBC directly yourself (which is quite short).

At the end of this exercise you may either have convinced yourself there's no point in a further feature here, or moved us closer to actually adding such a feature, if it seems to have value.