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?

Exception in thread "main" java.lang.NullPointerException at tester.main(tester.java:49) — codepoetly
– codepoetly, Commented Mar 24, 2017 at 7:31
java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DISPLAY_PLAYERS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored — codepoetly
– codepoetly, Commented Mar 24, 2017 at 7:36

Luke Woodward · Accepted Answer · 2017-03-24 20:27:20Z

2

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);

answered Mar 24, 2017 at 20:27

Luke Woodward

65.4k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting the out parameter in callable statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related