This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes wrong number or types of arguments in call to 'P_RETRIEVE_OPPTY_ORDER_STATUS' Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "wrong number or types of arguments in call to Watch "wrong number or types of arguments in call to New topic

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

uday luhar

Joined: Nov 15, 2010
Posts: 1
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

Joined: Aug 22, 2010
Posts: 3719

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.)
I agree. Here's the link:
subject: wrong number or types of arguments in call to 'P_RETRIEVE_OPPTY_ORDER_STATUS'
It's not a secret anymore!