Working with the medium text data type (Oracle)

In working with the MEDIUMTEXT data type, take extra care if you use multi-byte characters, excluding CLOB-based data types such as LONGTEXT, TEXT, or CLOB in the Oracle database. (CLOB stands for Character Large OBject.) On Oracle, Guidewire supports any single-byte character set, or the multi-byte character sets UTF8 and AL32UTF8.

Oracle has a maximum column width, for non-LOB columns, of 4000 bytes. Thus, with a single-byte character set, you can store up to 4000 characters in a single column (because one character requires one byte). However, with a multi-byte character set, you can store fewer characters, depending on the ratio of bytes to characters for that character set. For UTF8, the ratio is at most three-to-one, so you can always safely store up to 4000 / 3 = 1333 characters in a single column.

Thus, Guidewire recommends:

• Limit the number of characters to 4000 if using a single-byte character set.
• Limit the number of characters to 1333 if using UTF8 or AL32UTF8. However, it is possible that some AL32UTF8 characters can be four bytes, and thus 1333 of them can potentially overflow 4000 bytes.