Invalid scale size. Cannot be less than zero

Question

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 DataAccessException method I am getting an 'java.sql.SQLException: Invalid scale size. Cannot be less than zero'.

The sql calling the table is:

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.

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.

Java Database Connectivity (JDBC) - Populating CachedRowSet produces SQLException: Invalid scale size

Using queryForRowSet with subquery factoring SQL gives errors

They suggest changing the sql to the following as a work around.

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?

Answer 1

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.

Download Link:Oracle JDBC Drivers

Answer 2

Hi,

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?

Answer 3

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).

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 rs = (SqlRowSet) this.jdbcTemplate.query(SELECT_CUSTOMER_AREA,
                new Object[]{new SqlParameterValue(Types.VARCHAR, companyId),
                new SqlParameterValue(Types.NUMERIC, areaNumber)},
                new SqlRowSetOracleResultSetExtractor());

Answer 4

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:

java -Doracle.jdbc.J2EE13Compliant=true

Answer 5

pls try to verify that all the elements in that table should have size specified. ie v_num number(2),dont use like this v_num number; i too got the same error and i rectified it like this.