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 JSP, Oracle, CallableStatement problem . . . 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 "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 . . .
It's not a secret anymore!