This week's book giveaway is in the Other Open Source APIs forum.
We're giving away four copies of Storm Applied and have Sean Allen, Peter Pathirana & Matthew Jankowski on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes JSP, Oracle, CallableStatement problem . . . Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Storm Applied this week in the Other Open Source APIs forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JSP, Oracle, CallableStatement problem . . ." Watch "JSP, Oracle, CallableStatement problem . . ." New topic

JSP, Oracle, CallableStatement problem . . .

Brian E

Joined: Sep 07, 2001
Posts: 12
I'm having a hard time trying to invoke a stored procedure in Oracle 8.1.7 using JSP on Tomcat(Apache).
Here is my code:
String rvs = request.getParameter("rvs");
String zip = request.getParameter("zip_code");
String cpt = request.getParameter("cpt");
String strQuery = "{call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?)}";

CallableStatement cs = conn.prepareCall(strQuery);

cs.setString(1, rvs);
cs.setString(2, zip);
cs.setString(3, cpt);
cs.registerOutParameter(4, java.sql.Types.OTHER);

ResultSet rset = (ResultSet)cs.getObject(4);
I get the following error:
Internal Servlet Error:
javax.servlet.ServletException: Invalid column type
...... then ......
Root cause:
java.sql.SQLException: Invalid column type
There must be something simple that I'm missing, but I can't figure it out. Any help would be appreciated.
[This message has been edited by Brian E (edited September 07, 2001).]
[This message has been edited by Brian E (edited September 07, 2001).]
morph wang
Ranch Hand

Joined: Aug 23, 2001
Posts: 38
if You can post the sql you create the procedure, I think I will can help you fix this problem...

Yogen Vadnere
Ranch Hand

Joined: Sep 20, 2001
Posts: 58
Hey Brian,
Just add one more question mark to the statment where ur calling actual store procedure.
Ur registering 4th as a out parameter and there are only three question mark.
Hope this will help u.
String strQuery = "{call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?,?)}";

Yogen Vadnere
Yassuo Toda

Joined: Oct 16, 2003
Posts: 1
Hi Brian,
Try this:
Replace: String strQuery = "{call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?)}";
With: String strQuery = "{call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?,?)}";
Replace: cs.registerOutParameter(4, java.sql.Types.OTHER);
With: cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
As the previous post-ers pointed out, you need to have four parameters. But what is not clear is whether the resultset is the fourth parameter of the call (i.e., "{ call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?,?) }" or whether it is a return value from the procedure (i.e., "{? = call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?) }").
In the first case the you would want to set up the callable statement as you crrently have it. If it's the latter, then you want:

But in either case, if you have four parameters (in, out, in/out) you need to have four variables (question marks) in your call setup.
I agree. Here's the link:
subject: JSP, Oracle, CallableStatement problem . . .