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;
}

Day 2 Day Learning

My aim here is to write all the technical hurdles I come across during development. My main area would be on Java and next would be looking on to Groovy & Scala.

This blog rather is for me :) as it would help me remember answers to the hurdles which comer across daily. Some time it would be stupid post though but for me it gold...

Hope I would post better things for myself and hope this would also help others :)