SQL & PL/SQL

hi all...

I have a function that has been working great for years. We're running Oracle 11.1.0.7.0 on RedHat. The procedure only fails for 1 specific parameter. I don't think the parameter is the problem, but the data returned. If I pass in other valid parameters, it works, with the parameter 93 it fails. Again, the data returned from the function is very different.

This is the exception I'm getting:

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


Below is the function. When I pass in the value 93, the amount of data returned is huge. Around 375 rows, 50 columns. Most times the function returns around 10 rows and 50 columns. Since this function works with all other valid parameters, I think it's related to the data that is returned. I'm not sure what I can do to the function to enable completion of larger amounts of data.

Ideas are welcome...thanks...


FUNCTION getVisitTemplateActivities (a_visittemplate_id NUMBER) RETURN report_cur
IS

return_cur report_cur;

l_sql_stmt LONG := NULL;
l_sql_pay_cols LONG := NULL;
l_sql_activity_cols LONG := NULL;

CURSOR visit_cur IS
SELECT visitTemplate_Id, name
FROM visit
WHERE visitTemplate_Id = a_visitTemplate_Id
ORDER BY
sequence;

BEGIN

FOR visit_rec IN visit_cur
LOOP

IF (LENGTH(l_sql_pay_cols) > 0) THEN
l_sql_pay_cols := l_sql_pay_cols || ',';
l_sql_activity_cols := l_sql_activity_cols || ',';
END IF;

l_sql_pay_cols := l_sql_pay_cols ||
'(SELECT TO_CHAR(SUM(va1.rev_Amt),''$999,999,990.99'') ' ||
' || DECODE(COUNT(*),0,NULL,'' / '') ' ||
' || TO_CHAR(SUM(va1.exp_Amt),''$999,999,990.99'') ' ||
' || DECODE(COUNT(*),0,NULL,1,NULL,'' ('' || TO_CHAR(COUNT(*)) || '')'') ' ||
' FROM vVisitActivity va1 ' ||
' WHERE va1.visitTemplate_Id = ' || visit_rec.visitTemplate_Id || ' AND ' ||
' va1.visit_Name = ''' || visit_rec.name || ''' AND ' ||
' va1.activity_Description = va2.activity_Description ' || ' AND ' ||
' va1.payItem_Id IS NOT NULL) AS "' || SUBSTR(visit_rec.name,1,30) || '"';

l_sql_activity_cols := l_sql_activity_cols ||
'(SELECT DECODE(COUNT(*),0,NULL,1,''X'',''X'' || '' ('' || TO_CHAR(COUNT(*)) || '')'')' ||
' FROM vVisitActivity va1 ' ||
' WHERE va1.visitTemplate_Id = ' || visit_rec.visitTemplate_Id || ' AND ' ||
' va1.visit_Name = ''' || visit_rec.name || ''' AND ' ||
' va1.activity_Description = va2.activity_Description ' || ' AND ' ||
' va1.payItem_Id IS NULL) AS "' || SUBSTR(visit_rec.name,1,30) || '"';

END LOOP;

IF (l_sql_pay_cols IS NOT NULL)
THEN
l_sql_stmt :=
'SELECT DISTINCT(va2.activity_Description) AS "Activity", ' || l_sql_pay_cols || ' ' ||
'FROM vVisitActivity va2 ' ||
'WHERE va2.visitTemplate_Id = ' || a_visitTemplate_Id || ' AND ' ||
' va2.payItem_Id IS NOT NULL ' ||
'UNION ALL ' ||
'SELECT DISTINCT(va2.activity_Description) AS "Activity", ' || l_sql_activity_cols || ' ' ||
'FROM vVisitActivity va2 ' ||
'WHERE va2.visitTemplate_Id = ' || a_visitTemplate_Id || ' AND ' ||
' va2.payItem_Id IS NULL ' ||
'ORDER BY 1';
ELSE
l_sql_stmt := 'SELECT 1 FROM DUAL WHERE 0 = 1';
END IF;

OPEN return_cur FOR l_sql_stmt;

RETURN return_cur;

END;