Concatenate CLOB-rows with PL/SQL
- by david K
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!
