Environment:
Oracle 12.1.0.2 on Exadata Linux
A database was converted from 11g single-byte character set to 12c multi-byte character set.
A table with a CLOB column is used to cut/paste various document types (Word, Excel, etc.) into that CLOB column.
As I understand it, in the single-byte character set (e.g. US7ASCII) a multi-byte character would be replaced with a blank in the CLOB.
Several documents have been added in the 12c multi-byte character set database.
The application code does a 'SELECT SUBSTR(,1,4000) STUFF' and when it finds a multi-byte character in the first 4000 it returns more than 4000 bytes and raises the above ORA-64203 error.
I confirmed this by lowering the 4000 characters requested to 3990 and printing out both the LENGTH and the LENGTHB and showed where in several cases the LENGTH was say 3990 and the LENGTHB was 3998.
How should the application get around this error other than lowering the requested length below 4000?
Would DBMS_LOB.SUBSTR work? For some reason I don't have access to the DBMS_LOB package yet or I would have tried it.
Thanks in advance for your comments!!
-gary