Concatenate CLOB-rows with PL/SQL

Question

Hi,

I've got a table which has an id and a clob content like:

Create Table v_example_l (
    nip number,
    xmlcontent clob
);

We insert our data:

Insert into V_EXAMPLE_L (NIP,XMLCONTENT) 
Values (17852,'<section><block><name>delta</name><content>548484646846484</content></block></section>');
Insert into V_EXAMPLE_L (NIP,XMLCONTENT) 
Values (17852,'<section><block><name>omega</name><content>545648468484</content></block></section>');
Insert into V_EXAMPLE_L (NIP,XMLCONTENT) 
Values (17852,'<section><block><name>gamma</name><content>54564846qsdqsdqsdqsd8484</content></block></section>');

I'm trying to do a function that concatenates the rows of the clob that gone be the result of a select, i mean without having to give multiple parameter about the name of table or such, i should only give here the column that contain the clobs, and it should handle the rest.

CREATE OR REPLACE function assemble_clob(q varchar2)
return clob
is
v_clob clob;
tmp_lob clob;   
hold VARCHAR2(4000); 
--cursor c2 is  select xmlcontent from V_EXAMPLE_L where id=17852 
  cur sys_refcursor;  
  begin  

  OPEN cur FOR q;
    LOOP
    FETCH cur INTO tmp_lob;
    EXIT WHEN cur%NOTFOUND;
      --v_clob := v_clob ||  XMLTYPE.getClobVal(tmp_lob.xmlcontent); 
        v_clob := v_clob ||  tmp_lob;

    END LOOP;
   return (v_clob);
   --return (dbms_xmlquery.getXml( dbms_xmlquery.set_context("Select 1 from dual")) )
 end assemble_clob;

The function is broken... (if anybody could give me a help, thanks a lot, and i'm noob in sql so ....). Thanks!

Answer 1

You don't really say why it's broken but the DBMS_LOB package has an APPEND function that might be what you're looking for.

Answer 2

You'll have to explain what is happening that is not what you expect. Your example works fine for me. In SQLPlus, note the need to SET LONG to a large enough value to fetch the entire CLOB contents.

dev> set long 2000
dev> select assemble_clob('select xmlcontent from v_example_l') from dual;

ASSEMBLE_CLOB('SELECTXMLCONTENTFROMV_EXAMPLE_L')
--------------------------------------------------------------------------------
<section><block><name>delta</name><content>548484646846484</content></block></se
ction><section><block><name>omega</name><content>545648468484</content></block><
/section><section><block><name>gamma</name><content>54564846qsdqsdqsdqsd8484</co
ntent></block></section>