Tuesday, July 10, 2012

inserted value too large for column - in a delete?

One of the well know error everyone meets in Oracle is the ORA-01041: inserted value too large for column (while s/he is cursing Oracle why it did not returned ORA-12899: value too large for column ... with the details).
The error is usualy right - you tried to insert a value too large for the column definition, so Oracle rejects the statement. Although it mentions insert, one can understand such error for updates, too.

But recently I got this issue for a delete statement. And no, there was not any trigger nor other advanced feature doing an insert in the background. So why on Earth, why?

The application (apply process of Dbvisit Replicate) was using OCI and the error indeed came from the OCIStmtExecute call. As it turns out, however, the devil is somewhere else.

One of the confusing topic in OCI is string handling - there are four ways how to pass a string to Oracle, depending on whether it's null-terminated or length-specified and whether it should be space-padded (think about CHAR/VARCHAR2 semantics):
  • SQLT_CHR: varchar2-style, length-specified
  • SQLT_STR: varchar2-style, null-terminated
  • SQLT_AFC: char-style, length-specified
  • SQLT_AFV: char-stype, null-terminated
  • (and a few more for LONGs, RAWs and a different API for LOBs)
The application uses SQLT_AFC, as the data it gets are fed directly from the source database, so no padding magic is required (even worse, the values do not match for some data combinations with SQLT_CHR due to the padding). However, for some strange historical reason, the CHAR() datatype is limited to 2000 bytes - and this carries over to SQLT_AFC, although this is not mentioned in the OCI documentation. What's even more interesting, this happened only when the value was specified as UTF16.

And yes, this is exactly the reason for the ORA-01041 - binding a 4000 bytes string to SQLT_AFC and setting OCI_ATTR_CHARSET_ID to OCI_UTF16ID did result in "inserted value too large for column" when the statement was executed...

Versions: Linux 64-bit, Oracle 10.2 and 11.2, using Oracle Instant Client