Tuesday, August 9, 2011

Easy way to deal with Oracle SYS_REFCURSOR in Java

If you have an Oracle stored procedure defined: 

create or replace
PROCEDURE STOREDPROCEDUREA(
  OUT_CURSOR OUT SYS_REFCURSOR) AS 
BEGIN
  OPEN OUT_CURSOR FOR
    select ... from ...;
END STOREDPROCEDUREA;
then you can use the following Java code to retrieve the results of the cursor:
ResultSet results;
Connection connection = null;
List<Object> columns;
try {
    logger.debug("Getting database connection...");
    connection = DBManager.getDatabaseConnection();
    logger.debug("Preparing call...");
    CallableStatement statement = connection.prepareCall("call STOREDPROCEDUREA(?)");
    logger.debug("Executing query...");
    statement.execute();
    logger.debug("Retrieving results...");
    results = (ResultSet) statement.getObject(1);
    ResultSetMetaData metaData = results.getMetaData();
    int columnCount = metaData.getColumnCount();
    while (results.next()) {
        columns = new ArrayList<Object>();
for (int i = 0; i < columnCount; i++) {
    columns.add(results.getObject(i + 1));
}
// do stuff to columns here...
    }
    logger.debug("Closing...");
    results.close();
    statement.close();
}
catch (Exception exception) {
    logger.debug("Exception caught:");
    exception.printStackTrace();
}
finally {
    if (connection != null) {
        try {
    logger.debug("Closing connection...");
    connection.close();
} catch (SQLException e) {
    logger.debug("DB exception caught:");
    e.printStackTrace();
}
    }
}

Simple, right? I though so...

No comments:

Post a Comment