SQLException: the assigned column value was truncated while executing while (rs.getNext ())

Question

I have a program that retrieves a large dataset from Oracle 10g (10.2.0.3) using a fairly simple query. You can almost invoke the query / logic to “sort” the sortings in that it selects columns from a table with a minimum sentence sentence (that is, returns most, if not all) of the rows.

The [Java] code is the template we all saw:

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, which is used for the "PERSON" table. This seems like a question about worldly programming, but I wrote this code many times and never had this problem.

I get the message java.sql.SQLException: ORA-01406: the received column value was truncated on the while (rs.next ()) line. The Oracle documentation I read says: "The FETCH operation was forced to truncate a character string." He suggests using a larger column buffer to hold the largest column. But that does not make sense. Part of the above code is commented on as "write data to a file", I just write each column using rs.getBigDecimal ("ID"), rs.getString ("FIRST_NAME", etc.) I can reproduce the area using an empty while loop that does nothing with the ResultSet, that is, simply repeating with the ResultSet throws a SQLException.

The size of the returned dataset should be approximately 1 million rows. I get an exception after 600,000 lines / iterations through a loop. Any ideas?

Answer 1

Is PERSON a table or view?

At some point, under the covers, Java must develop a data length for the data. And I guess this is wrong. First, delete the columns one by one to determine which column is going wrong. Then maybe look at the data in chunks (e.g. 1-100000, 100001-200000, etc.) to find the fragment that causes the error. Then narrow the pieces (100000-150000, etc.) until you find one line that causes the problem. Looking at the line, it may be obvious what the problem is. If not, try the DUMP function to view the bytes in the data.

It is possible that the data in the numeric column does not match the column definition. This is usually a mistake, disk corruption is possible, sometimes strange data created through OCI. You can try to do

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

If your database uses a multibyte character set, this can confuse things with strings. Also, if there are non-traditional characters in the string (for example, ASCII 0-31)

Answer 2

For me, the first thing to do is look at the “bad” data by changing your loop to display the line that triggers the exception.

Answer 3

First try getMaxFieldSize in the request, so you know exactly what the maximum feild size is. I'm not an Oracle programmer (PostgreSQL), but I know that Oracle should have one, if not the best, metadata support. Change your SQL query with a limit on the size of the maximum field. This will at least help you halfway.

public int getMaxFieldSize () throws a 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, redundant data is silently discarded.

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

Answer 4

Aside, you should use Apache Commons DBUtils to close your resources. This will allow you to replace the contents of your finally clause with one line:

 DBUtils.closeQuietly(con, pstmt, rs);