Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Errors performing Oracle Stored Procedure using 'custom' operation

    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:
    Code:
    <operationBinding operationType="custom" operationId="execProcedure">
                <customSQL>
                    CALL PROC($criteria.START_DATE,$criteria.END_DATE)
                </customSQL>
    </operationBinding>
    DMI:

    Code:
    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:
    Code:
    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?
    Last edited by simokris; 18 Nov 2015, 02:09.

    #2
    It should work if you set sqlType to update:
    Code:
    <operationBinding operationType="custom" operationId="execProcedure" sqlType="update">
                <customSQL>
                    CALL PROC($criteria.START_DATE,$criteria.END_DATE)
                </customSQL>
    </operationBinding>

    Comment


      #3
      Originally posted by Crack View Post
      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?

      Comment


        #4
        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

        Comment


          #5
          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.

          Comment


            #6
            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

            Comment


              #7
              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.

              Comment

              Working...
              X