Setting the out parameter in callable statement

Question

I'm trying to run a stored procedure that returns a resultSet using oracle jdbc. The procedure is as follows.

create or replace procedure display_players (rset OUT sys_refcursor)
as
Begin
open rset for select * from player_data;
End;
/

The java code is as follows

try {
sql = "{call display_players()}";
call = conn.prepareCall(sql);
call.execute();
rs = call.getResultSet();

while(rs.next()){
    System.out.println(rs.getString("name") + " : " + rs.getString("club"));
}

I tried to register the out parameter as

call = conn.prepareCall("{call display_players(?)}");
call.registerOutParameter(1, OracleTypes.CURSOR);

But that dint work nor is the current code working as i get a null pointer exception which means the result set is not being returned. how do i achieve this?

Answer 1

I think you haven't quite worked out how to get the result set from an OUT parameter from a stored procedure call.

Firstly, you need to register the OUT parameter, as in your second code sample:

call = conn.prepareCall("{call display_players(?)}");
call.registerOutParameter(1, OracleTypes.CURSOR);

However, once you've executed the statement, it's not correct to call.getResultSet() to get at the result set in the OUT parameter. For example, suppose you were calling a stored procedure that had two OUT parameters returning cursors. Which one should call.getResultSet() return?

The trick is to use call.getObject(...) to get the value of the parameter from call as an Object and then cast this to a ResultSet. In other words, replace the line

rs = call.getResultSet();

with

rs = (ResultSet)call.getObject(1);