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.
FROM NOTE_ITEMS ni
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 = @tmpVar + note FROM note_items;
Anyways, I would greatly appreciate any help here....
Thanks in advance,