I got an issue where I had to fix the above problem, what we found was we had a VIEW where we were converting a CLOB to String (i.e. VARCHAR2) using DBMS_LOB.SUBSTR, there was no problem till now and we were using this view from long long time, the above situation came when one of the rows in the CLOB column had double-byte characters and it extended VARCHAR2 length i.e. 4000+, now when we were using this VIEW it was failing saying "ORA-06502" error.
DBMS_LOB.SUBSTR we were using as "DBMS_LOB.SUBSTR(COMMENTS, DBMS_LOB.GETLENGTH(COMMENTS))", and we changed it to DBMS_LOB.SUBSTR(COMMENTS, 1000), as the double-byte characters would take 4 bytes.
I think the above fix is a quick one, and the issue with this would be for normal chars we would be getting max 1000 chars from the CLOB where as in that CLOB it might be more then that.
Will post when we I have a fix for it :)
No comments:
Post a Comment