Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

wrong number or types of arguments in call to 'P_RETRIEVE_OPPTY_ORDER_STATUS'

 
uday luhar
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

this is my first post to javaRanch!

I have been strucK at a point in invoking the procedure which has a boolean parameter which when called from java program get error saying that "wrong number or types of arguments in call to 'P_RETRIEVE_OPPTY_ORDER_STATUS'". the procedure is given below along with the calling java code , and the Errors.

If i change the parameter data type to VARCHAR2 (the parameter hilighted in red) in the in the procedure and invoke it with the same method it works fine, even if i change the "callableStatement.setBoolean(1, true); to callableStatement.setInt(1, 1); it works fine.When i call it with setting the boolean to the procedure from my java i get a error. Im really confused whether is this a issue with oracle? or with java code.

Please can any body suggest me any kind of solution! to over come this scenario.

Please refer to the java code and the procedure attached for detail information!







 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Edit: the solution I suggested isn't going to work, sorry. The { CALL ... } statement is a JDBC escape sequence, which is transformed into begin ...; end; PL/SQL block anyway. I've verified this by using SQL trace. Maybe someone else will be able to resolve your issue.

Oracle SQL does not recognize BOOLEAN datatype, it is recognized only by PL/SQL. Procedures and packages are coded in PL/SQL, where boolean is available, but if you use boolean as a parameter or return value, such procedure or function cannot be called from SQL.

I may be wrong, but I think that CALL is an SQL statement, not a PL/SQL statement, therefore it is processed by SQL engine, which does not accept boolean datatype. Maybe (just maybe) it could work with booleans if you code your statement like this:


- here the variable is bound inside PL/SQL block. But this is only a wild guess, please test it in your own environment (and let us know). I may be wrong.

(I personally avoid using booleans as parameters or return types. As boolean data type cannot be used in tables anyway, I have a consistent way to represent booleans in Oracle (as NUMBER(1,0) in my case, but it can be done differently) so the inevitable confusion is minimized. This is also the reason I cannot give you tested solution.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic