CODEWITHSUNDEEP
javaoracle-database
Fourat
Fourat

Reputation: 2447

Using a REF CURSOR to retrieve a ResultSet with java program is throwing an error

I'm trying to follow this guide in order to retrieve a result set from a stored procedure.

The stored procedure (Oracle 12c) :

CREATE OR REPLACE PROCEDURE GetLastActions
(
  p_actions IN OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_actions FOR SELECT ACTION_ID, ACTION_DATE FROM ACTIONS FETCH NEXT 10 ROWS ONLY;
END;

The call in Java (I'm using JDK 1.6) :

String qLoadTmpData = "{call GetLastActions(?)}";
Connection con;
try {
    Connection con = getConnection();
    con.setAutoCommit(false);
    CallableStatement stmt = con.prepareCall(qLoadTmpData);
    stmt.setNull(1, Types.REF);
    stmt.registerOutParameter(1, Types.REF);
    stmt.execute();
    ResultSet rs = (ResultSet)stmt.getObject(1);
    System.out.println("Last actions:");
    while(rs.next()) {
        System.out.println(rs.getString(1) + "  => " rs.getDate(2));
    }
} catch (Exception e) {
    e.printStackTrace();
    throw new DaoException("Erreur lors de la recuperation des donnees !", e);
} finally {
    closeConnection(con);
}

The error :

java.sql.SQLException: Invalid column type: sqlType=2006
    at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4696)
    at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4578)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1285)
    at com.company.project.documents.report1.dao.ReportDAO.getRapportResultat(ReportDAO.java:59)
    at com.company.project.documents.report1.service.Service.getResultat(Service.java:40)
    at com.company.project.documents.report1.generator.Generator.generate(Generator.java:20)
    at com.company.project.documents.DocumentsGeneratorMain.main(DocumentsGeneratorMain.java:47)

Now the real query is much more complex than the example I tested above, that's why I'm using a cursor and a stored procedure to fetch the result.

What am I missing ?

UPDATE

The link I was referring to describes an approach related en EnterpriseDB, here's the same approach for Oracle that I tested and got the same error.

Upvotes: 0

Views: 2624

Answers (1)

Stephen C
Stephen C

Reputation: 718906

What am I missing ?

I think you are missing that you are trying to use documentation / examples for PostgreSQL with an Oracle database. This won't work. Different databases and dialects of SQL support cursors differently.

Here are some Q&As that show how to use REF CURSOR with Oracle DBs.

(I'm using JDK 1.6)

Maybe you are also missing that you really, really need to upgrade your Java platform.

Java 6 has been end-of-life for people without a support contract for over six years now. As of December 2018, it is end-of-life even for people with an Oracle Java SE Extended Support contract:

Upvotes: 2

Related Questions