oracle 无效的比例尺。不能小于零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/643280/
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
Invalid scale size. Cannot be less than zero
提问by Rob
I am using spring-2.5.6 to connect from a standalone application to an Oracle 10g database (ojdbc14.jar) using the org.apache.commons.dbcp.BasicDataSource. When I try to retrieve a SqlRowSet using the public SqlRowSet queryForRowSet(String sql, Object[] args) throws DataAccessExceptionmethod I am getting an 'java.sql.SQLException: Invalid scale size. Cannot be less than zero'.
我正在使用 spring-2.5.6 从独立应用程序连接到使用 org.apache.commons.dbcp.BasicDataSource 的 Oracle 10g 数据库 (ojdbc14.jar)。当我尝试使用该public SqlRowSet queryForRowSet(String sql, Object[] args) throws DataAccessException方法检索 SqlRowSet 时，我收到了“java.sql.SQLException: Invalid scale size”。不能小于零'。
The sql calling the table is:
调用表的sql是：
select CUSTAREADESC, BEGCOL, COLLENGTH from CUSTOMERAREA where upper(trim(FLEET)) = upper(trim(?)) and CUSTAREANO = ?
The columns BEGCOL and COLLENGTH are of the data type number with no precision defined.
BEGCOL 和 COLLENGTH 列属于未定义精度的数据类型 number。
I found some information on this issue, seems to be an incompatibility between the Oracle drivers and Sun's implementation of the com.sun.rowset.CachedRowSetImpl.
我发现了一些关于这个问题的信息，似乎是 Oracle 驱动程序与 Sun 的 com.sun.rowset.CachedRowSetImpl 实现之间的不兼容。
Java 数据库连接 (JDBC) - 填充 CachedRowSet 会产生 SQLException：无效的缩放大小
Using queryForRowSet with subquery factoring SQL gives errors
将 queryForRowSet 与子查询分解 SQL 一起使用会出现错误
They suggest changing the sql to the following as a work around.
他们建议将 sql 更改为以下内容作为解决方法。
select CUSTAREADESC, (BEGCOL + 0) BEGCOL, (COLLENGTH + 0) COLLENGTH from CUSTOMERAREA where upper(trim(FLEET)) = upper(trim(?)) and CUSTAREANO = ?
Does anyone know of a better generic solution that doesn't involve custom sql for any existing table where a column doesn't have precision defined?
有谁知道一个更好的通用解决方案，它不涉及任何现有表的自定义 sql，其中列没有定义精度？
采纳答案by Rob
I ended up creating an ResultSetExctractor class that implements ResultSetExtractor and used that to get an OracleCachedRowSet implementation of SqlRowSet. To use this you will need to include the oracle driver jar in your project (used ojdbc14.jar).
我最终创建了一个实现 ResultSetExtractor 的 ResultSetExctractor 类，并使用它来获取 SqlRowSet 的 OracleCachedRowSet 实现。要使用它，您需要在项目中包含 oracle 驱动程序 jar（使用 ojdbc14.jar）。
public class SqlRowSetOracleResultSetExtractor implements ResultSetExtractor {
public Object extractData(ResultSet rs) throws SQLException {
return createSqlRowSet(rs);
}
protected SqlRowSet createSqlRowSet(ResultSet rs) throws SQLException {
CachedRowSet rowSet = newCachedRowSet();
rowSet.populate(rs);
return new ResultSetWrappingSqlRowSet(rowSet);
}
protected CachedRowSet newCachedRowSet() throws SQLException {
return new OracleCachedRowSet();
}
}
This is then used by in the following way to extract a SqlRowSet.
然后通过以下方式使用它来提取 SqlRowSet。
SqlRowSet rs = (SqlRowSet) this.jdbcTemplate.query(SELECT_CUSTOMER_AREA,
new Object[]{new SqlParameterValue(Types.VARCHAR, companyId),
new SqlParameterValue(Types.NUMERIC, areaNumber)},
new SqlRowSetOracleResultSetExtractor());
回答by John Stauffer
This error is caused by an incompatibility in the Oracle JDBC drivers. You can fix it by setting the system property oracle.jdbc.J2EE13Compliant to true. For example, on the commandline:
此错误是由 Oracle JDBC 驱动程序不兼容引起的。您可以通过将系统属性 oracle.jdbc.J2EE13Compliant 设置为 true 来修复它。例如，在命令行上：
java -Doracle.jdbc.J2EE13Compliant=true
java -Doracle.jdbc.J2EE13Compliant=true
回答by Furkan
if you are using oracle 10g, and your number columns defined with number and no scale size
如果您使用的是 oracle 10g，并且您的数字列定义为数字且没有比例大小
create table EXAMPLE
(
FILED1 NUMBER(2,0),
FILED2 NUMBER
)
while populating FIELD2, you will receive this exception.
在填充 FIELD2 时，您将收到此异常。
Check your table structure for number fields.
检查数字字段的表结构。
i hope this may help you.
我希望这可以帮助你。
回答by Gandalf
What Oracle Jar are you using? Try upgrading to the newest (assuming you are using JDK 5 or greater). I believe the latest ojdbc5.jar [or ojdbc6.jar] has this issue fixed - it was caused by Oracle's drivers not correctly supporting the CachedRowSet interface.
您使用的是什么 Oracle Jar？尝试升级到最新版本（假设您使用的是 JDK 5 或更高版本）。我相信最新的 ojdbc5.jar [或 ojdbc6.jar] 已经修复了这个问题——这是由 Oracle 的驱动程序没有正确支持 CachedRowSet 接口引起的。
Download Link:Oracle JDBC Drivers
下载链接：Oracle JDBC 驱动程序
回答by Gandalf
Try this query:
试试这个查询：
select NVL(CUSTAREADESC, '0'), (NVL(BEGCOL,0) + 0) BEGCOL, (NVL(COLLENGTH, 0) + 0) COLLENGTH
from CUSTOMERAREA
where NVL(upper(trim(NVL(FLEET, ''))), '') = NVL(upper(trim(NVL(?, ''))), '')
and NVL(CUSTAREANO, '0') = NVL(?, '')
Is the SQL exception still occurring?
SQL异常是否还在发生？
