Announcement Announcement Module
Collapse
No announcement yet.
Exception calling oracle stored procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Exception calling oracle stored procedure

    I'm getting the following exception when I try to execute an Oracle stored procedure that has a single in parameter and 2 out params (one being the function return value).

    org.springframework.jdbc.UncategorizedSQLException : (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call STATION_TEST.ROLES_METHODS.GET_MEMBER_ROLES(?, ?)}]: params=[{cursor=null, stationId=1, result=null}]]): encountered SQLException [Ref cursor is invalid]; nested exception is java.sql.SQLException: Ref cursor is invalid
    java.sql.SQLException: Ref cursor is invalid
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:269)
    at oracle.jdbc.driver.OracleStatement.getCursorValue( OracleStatement.java:3681)
    at oracle.jdbc.driver.OracleStatement.getObjectValue( OracleStatement.java:5792)
    at oracle.jdbc.driver.OracleStatement.getObjectValue( OracleStatement.java:5622)
    at oracle.jdbc.driver.OracleCallableStatement.getObje ct(OracleCallableStatement.java:698)
    at org.springframework.jdbc.core.JdbcTemplate.extract OutputParameters(JdbcTemplate.java:837)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInC allableStatement(JdbcTemplate.java:773)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:738)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:761)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:100)
    at package.MemberRolesStoredProc.getRoles(UserDaoImpl .java:77)


    The stored procedure has the following signature:

    Code:
    FUNCTION get_member_roles(USER_ID_IN 			IN CSR_MEMBER_ROLES.USER_ID%TYPE,
         												ROLES_CUR_OUT 	OUT CORE_COMMON_METHODS.WEAK_TYPED_CUR)
      RETURN INTEGER;
    Here is my java code:

    Code:
    class MemberRolesStoredProc extends StoredProcedure {
        private static final String SQL = "STATION_TEST.ROLES_METHODS.GET_MEMBER_ROLES";
    
        public MemberRolesStoredProc(DataSource ds) {
            setDataSource(ds);
            setFunction(true);
            setSql(SQL);
            declareParameter(new SqlOutParameter("RESULT_INTEGER", Types.INTEGER));
            declareParameter(new SqlParameter("USER_ID_IN", Types.INTEGER));        
            declareParameter(new SqlOutParameter("ROLES_CUR_OUT", OracleTypes.CURSOR, new RowMapper() {
                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Role role = new Role();
                    role.setUid(rs.getInt("USER_ID"));
                    role.setProvider(rs.getInt("PROVIDER"));
                    role.setRoleId(rs.getInt("ROLE_ID"));
                    return role;
                }
            }));
            compile();
        }
    
        public Map getRoles(int stationId) {       
            Map params = new HashMap(3);
            params.put("USER_ID_IN", new Integer(stationId));
            Map results = execute(params);
            return results;
        }
    }
    I found that if I comment out the first out parameter, I no longer get the exception. This however is not a valid solution since I need the function return value for error processing and such.

    Thanks for any help you may have!

  • #2
    Sorry, just realized that I used an old exception trace. Here is a current one...

    org.springframework.jdbc.UncategorizedSQLException : (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call STATION_TEST.ROLES_METHODS.GET_MEMBER_ROLES(?, ?)}]: params=[{USER_ID_IN=1}]]): encountered SQLException [Ref cursor is invalid]; nested exception is java.sql.SQLException: Ref cursor is invalid
    java.sql.SQLException: Ref cursor is invalid
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:179)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:269)
    at oracle.jdbc.driver.OracleStatement.getCursorValue( OracleStatement.java:3681)
    at oracle.jdbc.driver.OracleStatement.getObjectValue( OracleStatement.java:5792)
    at oracle.jdbc.driver.OracleStatement.getObjectValue( OracleStatement.java:5622)
    at oracle.jdbc.driver.OracleCallableStatement.getObje ct(OracleCallableStatement.java:698)
    at org.springframework.jdbc.core.JdbcTemplate.extract OutputParameters(JdbcTemplate.java:837)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInC allableStatement(JdbcTemplate.java:773)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:738)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:761)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:100)
    at package.MemberRolesStoredProc.getRoles(UserDaoImpl .java:85)

    Comment


    • #3
      Wow, I really must be slipping. My earlier comment "I found that if I comment out the first out parameter, I no longer get the exception" is incorrect. This only worked since another procedure matching this signature existed as well. Sorry Again!

      Comment


      • #4
        Well, in case anyone else comes across this problem, it is due to the database stored procedure (handled by another dept in my case) setting the cursor to null when there are no results. Their argument for this is that it conserves database resources by avoiding opening an empty cursor. They rely on the functions return parameter to supply an error code to determine if the query was successful.

        The code in the JdbcTemplate class that causes the exception is:
        Code:
        Object out = cs.getObject(sqlColIndex);
        Where cs is an instance of oracle.jdbc.driver.OracleCallableStatement in my case.

        My question now is...Is there a way to avoid this problem? If modifying the db stored procs is not possible, what other choices do I have? I realize I could modify the JdbcTemplate class so it handles my proprietary error codes and avoids accessing the cursor when it should not exist, but is there a better way? Am I complicating things here?

        Thanks for any help...yet again.

        Comment


        • #5
          I'll look into this issue - maybe we can just detect the null and bypass the resultset processsing. We would then just return an empty list. I'll let you know when I have a solution.

          Comment


          • #6
            Sounds good to me. Thanks for your response.

            Comment


            • #7
              Problem is that we can't check the returned value for null until we retreive it and the exeption is thrown while it's being retrieved.

              You will have to write a custom handler for this. It's fairly easy - just call JdbcTemplate.execute(CallableStatementCreator csc, CallableStatementCallback action). The CallableStatementCreator has one method "createCallableStatement(Connection con)" where you prepare the CallableStatement. The other parameter is a CallableStatementCallback where you have to implement the "doInCallableStatement(CallableStatement cs)" method. Here you are handed the CallableStatement you just prepared and can execute it and retrieve the returned values.

              Here is an example I just tried out:

              Code:
                      List mydata =
                          (List)getJdbcTemplate().execute(
                              new CallableStatementCreator() {
                                  public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                                      CallableStatement cs = connection.prepareCall("{call get_names(?, ?)}");
                                      cs.registerOutParameter(1, OracleTypes.CURSOR);
                                      cs.registerOutParameter(2, Types.INTEGER);
                                      return cs;
                                  }
                              },
                              new CallableStatementCallback() {
                                  public Object doInCallableStatement(CallableStatement cs) throws SQLException {
                                      cs.execute();
                                      int ret = cs.getInt(2);
                                      List rowdata = new ArrayList();
                                      if (ret == 0) {
                                          ResultSet rs = (ResultSet) cs.getObject(1);
                                          while (rs.next()) {
                                             rowdata.add(rs.getString(2));
                                          }
                                          rs.close();
                                      }
                                  return rowdata;
                              }
                          });
                      return mydata;
              The stored procedure looks like this:

              Code:
              create or replace package Types as
                type refcursor is REF CURSOR;
              end;
              /
              create or replace procedure get_names(refcur out Types.refcursor, rc out integer)
                is
                  refsql varchar(255);
                begin
                  refsql := 'select id, name from mytest';
                  open refcur for refsql;
                  select 0 into rc from dual;
                end;
              /
              Last edited by trisberg; Mar 11th, 2006, 01:22 AM.

              Comment


              • #8
                Nice example, Thomas. I noticed that you're not closing the ResultSet, is that ok? Is the JdbcTemplate going to handle that in any way? I must say I can not imagine how that can be done since the variable is defined in the scope of the overriden method.

                Comment


                • #9
                  Ooops, you are right - you do get lazy relying on Spring all the time I have added the rs.close() in the example. On the other hand - the resultset should be closed when Spring closes the statement, but you never know if the driver is well behaved or not.

                  Comment


                  • #10
                    I am very interested in this. Is it possible to have the full listing for the java class so i can see it in its entirety?

                    Thanks

                    Rakesh

                    Comment


                    • #11
                      Originally posted by trisberg
                      Ooops, you are right - you do get lazy relying on Spring all the time I have added the rs.close() in the example. On the other hand - the resultset should be closed when Spring closes the statement, but you never know if the driver is well behaved or not.
                      Well, you are right, a good driver should do that on its own.

                      Comment


                      • #12
                        Hi!
                        And what about StoredProcedure.
                        We get the error when we call the execute() method of the SP:

                        Caused by: org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL .... SQL state [null]; error code [17062]; Ref cursor is invalid; nested exception is java.sql.SQLException: Ref cursor is invalid

                        We have all the procedures that return ref cursors extend StoredProcedure and implement RowCallbackHandler.
                        This way is much more cleaner. Why have the StoredProcedure class if we need do :
                        getJdbcTemplate().execute(
                        new CallableStatementCreator() {
                        public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                        CallableStatement cs = connection.prepareCall("{call get_names(?, ?)}");
                        cs.registerOutParameter(1, OracleTypes.CURSOR);
                        cs.registerOutParameter(2, Types.INTEGER);
                        return cs;
                        }
                        }

                        You need to do something about CallableStatementCallback to not panic like that

                        Comment

                        Working...
                        X