如何处理callableStatement.registerOutParameter（1，java.sql.Types.BOOLEAN）;

问题描述

在Oracle 10g / 11g中有一个存储过程，如下所示：

Have a stored procedure in Oracle 10g/11g like the following:

CREATE OR REPLACE 
PROCEDURE SP_SOME_PROC (  PRM_ID IN NUMBER , START_DATE IN DATE,  RESULT OUT BOOLEAN) 
is...

使用以下代码调用它并得到结果：

Using the following to code to call it and get the result:

String sql = "{call SP_SOME_PROC(?,?,?) }";

callableStatement = conn.prepareCall(sql);
callableStatement.setLong(1, theid);
callableStatement.setDate(2,  new java.sql.Date(startDate.getTime()));
callableStatement.registerOutParameter(3, java.sql.Types.BOOLEAN);

callableStatement.executeUpdate();

Boolean result = callableStatement.getBoolean(3);

上面的代码产生了一个例外，如下所示

the code above yields, however, an exception like the following

[10/8/13 2:28:24:736 EEST] 0000009a SystemErr     R java.sql.SQLException: Invalid column type: 16
[10/8/13 2:28:24:737 EEST] 0000009a SystemErr     R     at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950)
[10/8/13 2:28:24:737 EEST] 0000009a SystemErr     R     at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135)
[10/8/13 2:28:24:737 EEST] 0000009a SystemErr     R     at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
[10/8/13 2:28:24:737 EEST] 0000009a SystemErr     R     at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
[10/8/13 2:28:24:737 EEST] 0000009a SystemErr     R     at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569)

我试验了其他组合，例如

I experimented other combinations such as

String sql = "{? = call SP_SOME_PROC(?,?) }";
callableStatement = conn.prepareCall(sql);
callableStatement.registerOutParameter(1, OracleTypes.BOOLEAN);

但没有运气！

推荐答案

虽然Oracle有您可以在存储过程中使用的布尔类型，它没有可以通过JDBC接口发送的布尔列类型。您将不得不进行一些阻抗匹配（即返回int 0& 1或char'T'和'F'）。

While Oracle has a boolean type you can use in stored procedures, it does not have a boolean column type that can be sent across the JDBC interface. You are going to have to do some impedance matching (i.e. return int 0 & 1 or char 'T' and 'F').

这种布尔列类型的缺失是ANSI的延续，这是Oracle论坛上一堆嚎叫和咬牙切齿的源头（查找AskTom上的'boolean'。

This lack of a boolean column type is a holdover from ANSI, a source of much wailing and gnashing of teeth on the Oracle forums (look up 'boolean' on AskTom).

这篇关于如何处理callableStatement.registerOutParameter（1，java.sql.Types.BOOLEAN）;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！