Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JSP, Oracle, CallableStatement problem . . .

 
Brian E
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if You can post the sql you create the procedure, I think I will can help you fix this problem...
 
Yogen Vadnere
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic