concatenate values of a column into CLOB variable WITHOUT USING CURSOR

Hi,

First off, I should preface my query witht he fact that I have minimal experience with Oracle or pl/sql.
I find myself with the following problem:

I have a table called [notes]. There is a one-to-many relationship between it and a [note_items] table. The [note_items] table has a [note] field.
When selecting items from my [notes] table I call a function to concatenate all [note_items] associated with that note into one field.

I have already achieved this with a cursor. However this is proving to be considerably slow.

I found a method using XMLELEMENT and EXTRACT but I am running into problems with the maximum length for a varchar being 4000 bytes.
The specific error I get is ORA-19011 'Character string buffer too small'.

This is the code using  XMLELEMENT:
'tmpVar is a CLOB but I don't think this is relevant as the return type from the EXTRACT is a varchar2 and it seems to be at that point that the error is happening.

SELECT rtrim(xmllagg(
                     XMLELEMENT(e,
                     ni.note)).EXTRACT('//text()'))
                     INTO tmpVar
                     FROM NOTE_ITEMS ni
                     WHERE ni.note_id=iNoteId;



I admit I am from an SQL Server background and it would be great  if there was something similar to the followign  which I have used in the past:

DECLARE @tmpVar BLOB;
SELECT @tmpVar='';
SELECT @tmpVar = @tmpVar + note FROM note_items;

Anyways, I would greatly appreciate any help here....

Thanks in advance,

Tomás



LVL 1
mausyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaju KumbalathDeputy General Manager - ITCommented:
In pl/sql varchar2 limit is 32768 bytes. u can declare the variable upto  32768 bytes
tmpvar varchar2(32768);
0
Shaju KumbalathDeputy General Manager - ITCommented:
sorry..i didnt notice tmpvar is a clob variable
0
Shaju KumbalathDeputy General Manager - ITCommented:
SELECT rtrim(xmlagg(
                     XMLELEMENT(e,
                     ni.note)).EXTRACT('//text()').getclobval())
                     INTO tmpVar
                     FROM NOTE_ITEMS ni
                     WHERE ni.note_id=iNoteId;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mausyAuthor Commented:
fantastic. Thanks. Can't believe it was that straightforward! Cheers again. T
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.