使用带有布尔 IN 参数的 CallableStatement 在 Java 中调用 Oracle PL/SQL 过程会产生 PLS-00306 oracle 错误：
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14554251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling an Oracle PL/SQL procedure in Java using a CallableStatement with a boolean IN parameter gives an PLS-00306 oracle error:
提问by ziggy
I have a pl/sql procedure on an Oracle 11g that has the following parameters:
我在 Oracle 11g 上有一个 pl/sql 过程，它具有以下参数：
PROCEDURE validate_product
( product_id_in IN varchar2 ,
username_in in varchar2,
source_in varchar2,
source_id_in varchar2 ,
isEuProduct in boolean ,
error_code out varchar2,
product_type out varchar2
)
I am trying to call the above stored procedure from within java using the following code:
我正在尝试使用以下代码从 java 内部调用上述存储过程：
cstmt = getConnection().prepareCall("begin " + DBUtil.SCHEMANAME + ".PRODUCT_UTILITIES.validate_product(:1,:2,:3,:4,:5,:6,:7); end;");
cstmt.registerOutParameter(6, Types.CHAR);
cstmt.registerOutParameter(7, Types.CHAR);
cstmt.setString(1, productId);
cstmt.setString(2, username);
cstmt.setString(3, sourceName);
cstmt.setString(4, sourceId);
cstmt.setBoolean(5, isEUProduct);
cstmt.execute();
The types of the java variables are all Stringwith the exception of isEUProductwhich is boolean. Whenever i run the above program i get the following error:
该类型的Java变量都String以异常的isEUProduct是boolean。每当我运行上述程序时，都会出现以下错误：
PLS-00306: wrong number or types of arguments in call to validate_product ORA-06550: line 1, column 7: PL/SQL: Statement ignored"
I must have debugged the program a hundred times but everything seem to be the correct type and the number of arguments are correct.
我一定已经调试了一百次程序，但一切似乎都是正确的类型，参数的数量也是正确的。
I am completely stuck as to what it is i am doing wrong. Having googled around i suspect that maybe i am not setting the boolean parameter correctly.
我完全不知道我做错了什么。用谷歌搜索后，我怀疑我可能没有正确设置布尔参数。
Any ideas?
有任何想法吗？
回答by Steve K
I was amazed when we ran into this, but the Oracle JDBC Driver doesn't support passing booleans into Stored Procedures.... Ya, I'm not making that up :)
当我们遇到这个时我很惊讶，但是 Oracle JDBC 驱动程序不支持将布尔值传递到存储过程中......是的，我不是编造的 :)
Boolean Parameters in PL/SQL Stored Procedures
The JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT to BOOLEAN.
JDBC 驱动程序不支持将 BOOLEAN 参数传递给 PL/SQL 存储过程。如果 PL/SQL 过程包含 BOOLEAN 值，您可以通过用第二个 PL/SQL 过程包装 PL/SQL 过程来解决这个限制，第二个 PL/SQL 过程接受参数作为 INT 并将其传递给第一个存储过程。当调用第二个过程时，服务器执行从 INT 到 BOOLEAN 的转换。
回答by GWu
There is a simple workaround for that restriction, which does not require a wrapper procedure, simply wrap the boolean parameter in PL/SQL in a CASEstatement and use an Integer for binding:
该限制有一个简单的解决方法，它不需要包装程序，只需将 PL/SQL 中的布尔参数包装在CASE语句中，并使用 Integer 进行绑定：
stmt = connection.prepareCall("begin"
+" booleanFunc(par_bool => (CASE ? WHEN 1 THEN TRUE ELSE FALSE END)); "
+"end;"
);
// now bind integer, 1 = true, 0 = false
stmt.setInt(1, 0); // example for false
You may wrap the Integer during bind the other way around, if your method uses boolean, for example:
如果您的方法使用布尔值，您可以在绑定期间以相反的方式包装 Integer，例如：
// now bind integer, 1 = true, 0 = false
stmt.setInt(1, myBool ? 1 : 0);
回答by ?ll?ll? l? ?ll?ll?
I think here is the issue
我认为这是问题
cstmt.registerOutParameter(6, Types.CHAR);
cstmt.registerOutParameter(7, Types.CHAR);
you called from java as above, But you declared in procedure out parameter as varchar2,that means there is a mismatch of datatype.
您如上所述从 java 调用，但是您在程序输出参数中声明为varchar2，这意味着数据类型不匹配。
Try this code,
试试这个代码，
cstmt.registerOutParameter(6, Types.VARCHAR);
cstmt.registerOutParameter(7, Types.VARCHAR);
I hope this will work..
我希望这会奏效..
回答by Max
I suspect the out statements might need modification to the following (since the type of the out parameters is varchar2 in the procedure):-
我怀疑 out 语句可能需要修改以下内容（因为 out 参数的类型在过程中是 varchar2）：-
cstmt.registerOutParameter(6, Types.VARCHAR);
cstmt.registerOutParameter(7, Types.VARCHAR);
However, if that does not work, then try modifying the prepareCall statement to the following also :-
但是，如果这不起作用，请尝试将 prepareCall 语句修改为以下内容：-
cstmt = getConnection().prepareCall("{call " + DBUtil.SCHEMANAME + ".PRODUCT_UTILITIES.validate_product(?,?,?,?,?,?,?)}");
