Call Stored Procedure in Java with data type SYS_REFCURSOR as output

0

I'm running a console application in JAVA, where I need to run a Stored Procedure done in Oracle , the Stored Procedure has three input variables that are of data type String and two output ones, one of which is String type and the last is of type SYS_REFCURSOR. The problem that I have to indicate at the moment the types of data that this Stored Procedure is going to return, in Java I do not recognize the data type SYS_REFCURSOR.

This is an example of my code in JAVA:

Connection connection = null;
CallableStatement callableStatement = null;


try {
    connection = ConnectionConfiguration.getConecction();
    callableStatement = connection.prepareCall("{CALL sp_jobsMonitor_UpdateCurrentSt(?,?,?,?,?)}");
    callableStatement.setString(1, job.getJobName());
    callableStatement.setString(2, job.getExecutionStatus());
    callableStatement.setString(3, job.getExceptionID());
    callableStatement.registerOutParameter(4, Types.VARCHAR);
    callableStatement.registerOutParameter(5, Types.xxxx);

    callableStatement.executeUpdate();

    job.setExceptionMessage(callableStatement.getString(4));

} catch (ClassNotFoundException e) {
    e.printStackTrace();
} catch (SQLException e) {
    e.printStackTrace();
}finally {
    if (callableStatement != null) {
        try {
            callableStatement.close();
        } catch (SQLException e) {
            return e.getMessage();
        }
    }

    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            return e.getMessage();
        }
    }
}
return "ok";

In line callableStatement.registerOutParameter(5, Types.xxxx); is where I need to indicate that type of data SYS_REFCURSOR.

I've already indicated the data type Types.OTHER and even Types.VARCHAR I do not know what I'm doing wrong or omitting.

Greetings

NOTE: The types of data I use are based on the Oracle Data types.

    
asked by Edgar Conrado 03.03.2016 в 19:43
source

1 answer

1

That type of cursor is not supported by JDBC, it is only supported via Oracle. To solve this, you must use the Oracle output data type:

import oracle.jdbc.*;

//...
callableStatement.registerOutParameter(5, OracleTypes.CURSOR);

Source: link

    
answered by 03.03.2016 / 19:56
source