File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes calling stored proc. w/ resultset return? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "calling stored proc. w/ resultset return?" Watch "calling stored proc. w/ resultset return?" New topic

calling stored proc. w/ resultset return?

Leo Marchowski

Joined: Oct 29, 2007
Posts: 6

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
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Leo,

there are examples at

amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 544
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.

You also need to change


I agree. Here's the link:
subject: calling stored proc. w/ resultset return?
It's not a secret anymore!