If you have an Oracle stored procedure defined:
then you can use the following Java code to retrieve the results of the cursor:
create or replace
PROCEDURE STOREDPROCEDUREA(
OUT_CURSOR OUT SYS_REFCURSOR) AS
BEGIN
OPEN OUT_CURSOR FOR
select ... from ...;
END STOREDPROCEDUREA;
ResultSet results;Simple, right? I though so...
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();
}
}
}
No comments:
Post a Comment