SQLException: fetched column value was truncated when executing while (rs.getNext())

Question

I have a program that pulls a large dataset from Oracle 10g (10.2.0.3) using a fairly-straightforward query. You could almost call the query/logic an "export" of sorts in that it selects the columns from a table with a minimal where clause (i.e., it returns most of, if not all, the rows).

The [Java] code is boilerplate that we have all seen:

Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
    con = getConnection(Person);
    pstmt = con.prepareStatement(
            "SELECT ID, FIRST_NAME, LAST_NAME, ... FROM PERSON WHERE ...");
    rs = pstmt.executeQuery();
    while (rs.next()) {
        // write the data to a file
    }
}
finally {
    if (rs != null) try { rs.close(); } catch (Exception e) { }
    if (pstmt != null) try {pstmt.close(); } catch (Exception e) { }
    if (con != null) try { con.close() ; } catch (Exception e) { }
}

getConnection is my own method which returns a Connection object to the Oracle database that is used for the "PERSON" table. This feels like a mundane programming question, but I've written code like this numerous times and never had this issue.

I am receiving a java.sql.SQLException: ORA-01406: fetched column value was truncated on the while (rs.next()) line. The Oracle documentation I have read says "a FETCH operation was forced to truncate a character string." It suggests using a larger column buffer to hold the largest column. But this doesn't make sense. In the portion of the above code commented as "write the data to a file" I am simply writing each column as with rs.getBigDecimal("ID"), rs.getString("FIRST_NAME", etc. I can, in fact, reproduce the area using an empty while loop that does nothing with the ResultSet. That is, just iterating through the ResultSet causes the SQLException to be thrown.

The size of the data set returned should be approximately 1million rows. I get the exception after about 600,000 rows/iterations through the loop. Any ideas?

Answer 1

Is PERSON a table or view ?

At some point, under the covers, Java must be working out a data length for the data. And I guess it is doing it wrong. Firstly, remove the columns one-by-one so you can identify which column is going wrong. Then maybe go through the data in chunks (eg 1-100000, 100001-200000 etc), to find the chunk which causes the error. Then narrow down the chunks (100000-150000, etc) until you find a single row that causes the problem. By looking at the row, it may be obvious what the problem is. If not, try the DUMP function to look at the bytes in the data.

It is possible for the data in a numeric column to not correspond to the column definition. That's normally a bug, possibly disk corruption, sometimes weird data created through OCI. You could try doing a

SELECT COUNT(*) FROM PERSON WHERE ID != TRUNC(ID);

If your database is using a multi-byte character set, that may confuse things with strings. Also if there are non-conventional characters in a string (eg ASCII 0-31)

Answer 2

To me, the first thing to do is to look at the "faulty" data, by modifying your loop to display the row which triggers the exception.

Answer 3

First try getMaxFieldSize on the query, that way you know explictly what the max feild size is. I am not an Oracle programmer(PostgreSQL) but I do know that Oracle is supposed to have one of, if not the best, metadata support. Modify your sql query with a limit on the max field size. That at least will get you half way there.

public int getMaxFieldSize() throws SQLException

Returns the maximum number of bytes allowed for any column value.

This limit is the maximum number of bytes that can be returned for any column value. The limit applies only to BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR, and LONGVARCHAR columns. If the limit is exceeded, the excess data is silently discarded.

Returns:
    the current max column size limit; zero means unlimited
Throws:
    SQLException - if a database access error occurs

Answer 4

As an aside, you should consider using Apache Commons DBUtils to close your resources. This would allow you to replace the contents of your finally clause, with a single line:

DBUtils.closeQuietly(con, pstmt, rs);