Using CAST/COLLECT with a function
I have a select statement that gathers comments into a report by user ID and Project Name.
This works really well until I get to a point where the total comments exceeds 4000 characters, then I get a "character string buffer too small".
Is there another way to gather the comments and report on them in APEX?
Here is the Select Statement:
/** START SELECT
SELECT
PROJECT_NAME, USER_ID, COLLECT_FUNC(CAST(COLLECT (USER_NOTES) AS VARCHAR2_T)) as COMMENTS
FROM
TBL_PROJECT
where PROJ_DATE >= to_date('5/1/2011','MM/DD/YYYY') and PROJ_DATE <= to_date('6/30/2011','MM/DD/YYYY')
PROJECT_NAME, USER_ID
order by USER_ID;
/** END SELECT
Where the VARCHAR2_T is:
CREATE OR REPLACE TYPE "VARCHAR2_T" as table of varchar2(4000)
and the function that collects the comments is:
/*** START CODE
create or replace function collect_func (t in varchar2_t)
return varchar2
as
ret varchar(4000) := '';
i number;
begin
i := t.first;
while i is not null loop
if ret is not null then
ret := ret || ' -- <BR> ';
end if;
ret := ret || t(i);
i := t.next(i);
end loop;
return ret;
end;
/*** END CODE