JSP, Oracle, CallableStatement problem . . .

[Brian E]

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);
cs.execute();

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]

if You can post the sql you create the procedure, I think I will can help you fix this problem...

[Yogen Vadnere]

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(?,?,?,?)}";

[Yassuo Toda]

Hi Brian,
Try this:
1)
Replace: String strQuery = "{call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?)}";
With: String strQuery = "{call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?,?)}";
2)
Replace: cs.registerOutParameter(4, java.sql.Types.OTHER);
With: cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);

[Wayne L Johnson]

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:

...
String strQuery = "{ ? = call PKG_RETURNRECORDS.RETURNRECORDS(?,?,?) }";
CallableStatement cs = conn.prepareCall(strQuery);
cs.registerOutParameter(1, java.sql.Types.OTHER);
cs.setString(2, rvs);
cs.setString(3, zip);
cs.setString(4, cpt);
cs.execute();

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.