[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1534
  • Last Modified:

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



0
mausy
Asked:
mausy
  • 3
1 Solution
 
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
 
mausyAuthor Commented:
fantastic. Thanks. Can't believe it was that straightforward! Cheers again. T
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now