Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using CAST/COLLECT with a function

SleepDeprivedInSeattleJun 20 2011 — edited Jun 21 2011
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 19 2011
Added on Jun 20 2011
4 comments
638 views