Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBC - Unable to access ref cursor Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC - Unable to access ref cursor

    Hi, I am new to Spring JDBC. We decided to use spring JDBC to access oracle stored proc and functions. I am unable to access ref cursor in the code. I coded as per the specs. please refer the code and suggest the solution

    CODE
    =====
    public class FunctionCallTest2 extends StoredProcedure {
    // name of procedure/function in database
    public static final String FUNC_NAME = "hr_employee.func_get_fixed_emp_list";

    public FunctionCallTest2(DataSource ds) {
    setDataSource(ds);
    setSql(FUNC_NAME);
    setFunction(true);
    declareParameter(new SqlOutParameter("res", OracleTypes.CURSOR, new EmployeeDataMapper()));
    compile();
    }

    private class EmployeeDataMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    EmployeeVO rec = new EmployeeVO();
    String firstName = rs.getString("first_name");
    String lastName = rs.getString("last_name");
    rec.setFirstName(firstName);
    rec.setLastName(lastName);
    System.out.println("Employee Name : "+firstName+" "+lastName);
    return rec;
    }
    }

    }

    DB FUNCTION
    ==========
    --Ref Cursor Type declaration in package
    TYPE refcur_type IS REF CURSOR;

    FUNCTION func_get_fixed_emp_list
    RETURN refcur_type
    AS
    results refcur_type;
    erec employees%ROWTYPE;
    BEGIN
    OPEN results FOR 'select * from employees where rownum < 5';
    LOOP
    FETCH results INTO erec;
    EXIT WHEN results%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Displaying Employee Last Name : ' || erec.last_name);
    END LOOP;
    CLOSE results;

    return results;

    END func_get_fixed_emp_list;

    EXCEPTION MESSAGE
    ================
    Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException : CallableStatementCallback; uncategorized SQLException for SQL [{? = call hr_employee.func_get_fixed_emp_list()}]; SQL state [null]; error code [17062]; 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:932)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInC allableStatement(JdbcTemplate.java:868)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:823)
    at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:856)
    at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:102)
    at com.iweb.hr.dao.SpringJDBCDAO.main(SpringJDBCDAO.j ava:120)

  • #2
    FYI, I am using oracle 10g XE database and spring 1.2.7 version. I am able to make successful cal to the function returning VARCHAR2 but having issues with REF CURSOR

    Comment


    • #3
      Can someone please respond if you have already faced this issue. Thanks!

      Comment


      • #4
        Managed to fix this issue and in the process identified few other issues too
        1) Root cause for the issue is, the odbc driver jar is not loaded correctly. F
        Other notices issues

        Always declare input parameters first.

        Comment

        Working...
        X