Monday, July 23, 2012

Java call procedure and get cursor back


//getDBUSERCursor is a stored procedure
String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "mkyong");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
 
// execute getDBUSERCursor store procedure
callableStatement.executeUpdate();
 
// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);
 
// loop it like normal
while (rs.next()) {
 String userid = rs.getString("USER_ID");
 String userName = rs.getString("USERNAME");
}