java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

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 :)

Hibernate Criterion IN Clause 1000 break up

/**
* An utility method to build the Criterion Query IN clause if the number of parameter
* values passed has a size more than 1000. Oracle does not allow more than
* 1000 parameter values in a IN clause. Doing so a {@link SQLException} is
* thrown with error code, 'ORA-01795: maximum number of expressions in a list is 1000'.
* @param propertyName
* @param values
* @return
*/
public static Criterion buildInCriterion(String propertyName, List values)
{
Criterion criterion = null;
int listSize = values.size();
for (int i = 0; i < listSize; i += PARAMETER_LIMIT) {
List subList;
if (listSize > i + PARAMETER_LIMIT) {
subList = values.subList(i, (i + PARAMETER_LIMIT));
}
else {
subList = values.subList(i, listSize);
}
if (criterion != null) {
criterion = Restrictions.or(criterion, Restrictions.in(propertyName, subList));
}
else {
criterion = Restrictions.in(propertyName, subList);
}
}
return criterion;
}