aspose file tools*
The moose likes JDBC 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
Bookmark "calling stored proc. w/ resultset return?" Watch "calling stored proc. w/ resultset return?" New topic
Author

calling stored proc. w/ resultset return?

Leo Marchowski
Greenhorn

Joined: Oct 29, 2007
Posts: 6
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
Ranch Hand

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

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

Herman
amit punekar
Ranch Hand

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


You also need to change


regards,
Amit


Regards,
Amit
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: calling stored proc. w/ resultset return?