calling stored proc. w/ resultset return?

[Leo Marchowski]

Hi,


I am simply trying to call a stored procedure that takes one parameter and returns a result set of 2 colums both of which are varchars. This is my code:

CallableStatement cStmt = null;
cStmt = conn.prepareCall( "{? = call getUsers(?) }" );
cStmt.registerOutParameter(1, OracleTypes.CURSOR);
cStmt.setString(2, "10026425");
cStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
cStmt.registerOutParameter(4, java.sql.Types.VARCHAR);
ResultSet rs = cStmt.executeQuery();

When I run this I get: java.sql.SQLException: ORA-01006: bind variable does not exist.

If I take away the last two registerOutParameter calls I get there error:
java.sql.SQLException: ORA-00900: invalid SQL statement

After searching on the internet I don't see complete consistency on how to do this - I guess one question is in terms of the output -- In theory -- since I am going to get one resultSet with 2 columns of values what do I need to 'registerOutParameter' all 3 or just the resultSet or what?

Many Thanks

[Herman Schelti]

hi Leo,

there are examples at
http://www.exampledepot.com/egs/java.sql/CallFunction.html

Herman

[amit punekar]

Hello,
The binding error is because of line 3 and 4. There no Parameters numbered 3 and 4 as we can make out from the CallableStatement you are preparing.
You get the resultset and then iterate over it to retrieve the VARCHAR values inside it.

cStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
cStmt.registerOutParameter(4, java.sql.Types.VARCHAR);

You also need to change

    
ResultSet rs = cStmt.executeQuery();
Change to 
cStmt.execute();

regards,
Amit