java.sql.SQLSyntaxErrorException: ORA-01722: invalid number while resultSet.next() on refcursor and I don't understand why?

Question

I have an issue eating me for hours and I'm unable to find the exact reason why it is happening? I get java.sql.SQLSyntaxErrorException: ORA-01722: invalid number while iterating over refcursor out parameter of stored procedure using ResultSet.next(). What is bothering me is, it occurs even when there is actually no result to iterate over.

The signature of stored procedure:

PROCEDURE GET_PROG_SRCH_BY_CRITERIA
       (p_orgID                  IN    NUMBER,
        p_MEPID                  IN    NUMBER,
        p_prog_prg_id            IN    NUMBER,
        p_pcom_enrol_date        IN    VARCHAR2,
        p_pcom_enrol_year_from   IN    VARCHAR2,
        p_pcom_enrol_year_to     IN    VARCHAR2,
        p_pcom_non_prtcm_date    IN    VARCHAR2,
        p_pcom_completion_date   IN    VARCHAR2,
        p_pcom_status_code       IN    VARCHAR2,        
        p_sys_cursor             OUT   SYS_REFCURSOR
       );

Java code snippet:

statement = connection.prepareCall("call PROG_TOOLS.GET_PROG_SRCH_BY_CRITERIA(?,?,?,?,?,?,?,?,?,?)");
statement.setLong(1, searchCriteria.getOrganizationID());
/*
 * Code to Set other 8 Parameters comes here
 */
statement.registerOutParameter(10, oracle.jdbc.OracleTypes.CURSOR);
statement.executeQuery();
resultSet = (ResultSet) statement.getObject(10);
/*
 * Code to Initialize necessary value Objects comes here
 */
while (resultSet.next()) {
    /*
     * Code to get values from resultSet using column named index
     * and initialize value objects
     * Few example:
     */
     dataVO = new DataVO();
     dataVO.setPcomID(result.getLong("PCOM_ID"));
     dataVO.setPersonID(result.getLong("PERSON_ID"));
     .
     .
     programList.add(dataVO);
}

Issue: If I call GET_PROG_SRCH_BY_CRITERIA by sending NULL in 3rd parameter, I get expected result. If I call GET_PROG_SRCH_BY_CRITERIA by sending an actual value in 3rd parameter, I get java.sql.SQLSyntaxErrorException: ORA-01722: invalid number on the line

while (resultSet.next()) {

Exception trace:

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
    at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1000)
    at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:314)
    at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:228)
    at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.next(Unknown Source)
    at org.app.ejb.program.dao.ProgDAO.searchProgEnrollees(ProgDAO.java:136)

I checked the procedure by testing it directly using SQLDeveloper tools by sending the same parameters which the above Java code used. It worked absolutely fine.

I tried with OJDBC14 and OJDBC6 drivers, but no luck.

I have no clue why this occurs on resultSet.next(). If the 3rd parameter is null then I don't get this exception and flow works as expected.

Any help here is really appreciated.

Technologies used: - Java 6, ejb - Oracle 11g Database R2 (11.2.0.3) - Oracle Weblogic 10g (10.3.0)

What is the SQL query associated to the returned cursor? That's where you need to be looking. — sstan
also show the actual code where you set the number parameter (your 3rd parameter). Your error does seems to be in the SQL code itself, but maybe you pass something that Oracle can't deal with as a number... — JP Moresmau
@JPMoresmau Code to set the parameter is: if (null != searchCriteria.getProgramId() && !searchCriteria.getProgramId().trim().equals("")) { long programId = Long.valueOf(searchCriteria.getProgramId()); statement.setLong(3, programId); } else { statement.setNull(3, NUMBER); } — Gokul
OK, so post the SQL code that generates the cursor, the problem is in there somewhere. — JP Moresmau
@sstan Thanks for the clue. I tried running the select query inside stored procedure GET_PROG_SRCH_BY_CRITERIA directly using prepared statement and also retained same parameters. It worked! — Gokul

lance-java lance-java · Accepted Answer · 2015-07-07T14:38:41

Perhaps you need to call next() on the containing ResultSet first?

statement.registerOutParameter(10, oracle.jdbc.OracleTypes.CURSOR);
ResultSet rs1 = statement.executeQuery();
rs1.next();
resultSet = (ResultSet) statement.getObject(10);

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number while resultSet.next() on refcursor and I don't understand why?

1 Answers