Home » Developer & Programmer » JDeveloper, Java & XML » help with clob concatenation
help with clob concatenation [message #459593] Mon, 07 June 2010 06:21 Go to next message
b_52globemaster
Messages: 51
Registered: July 2005
Member
Hi ,

i have a data represented like that(a simple table containing an ID and the clob):

create table v_EXAMPLE_L (
    nip number,
    xmlcontent clob
);


we fill the table
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>');




my function to concatenate the clob:
CREATE OR REPLACE function assemble_clob(p_deta number,p_omega number)
return clob
is
  v_clob clob;    
  cursor c2 is  select xmlcontent from V_EXAMPLE_L where NIP=17852 ;
begin

  for rec in c2   loop
    --v_clob := v_clob ||  XMLTYPE.getClobVal(rec.xmlcontent);     
    v_clob := v_clob ||  rec.xmlcontent;
  end loop;
  return (v_clob);
   --return   (dbms_xmlquery.getXml( dbms_xmlquery.set_context("Select 1 from dual"))   )
end assemble_clob;



then i simply call it
Quote:
select assemble_clob(1, 3) from dual


the parameter that i pass the function are not significal .... just made so the compilation pass ...


now my problem is that i want a dynamic function , i mean i can concatenate rows of clob from any table/view i want (they all have a field clob named "xmlcontent").


any help with this plz.


and thanks

[Updated on: Mon, 07 June 2010 06:37] by Moderator

Report message to a moderator

Re: help with clob concatenation [message #459598 is a reply to message #459593] Mon, 07 June 2010 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "execute immediate" passing it a string including a dynamic table you pass as a parameter to the function.

Regards
Michel
Re: help with clob concatenation [message #459607 is a reply to message #459593] Mon, 07 June 2010 06:57 Go to previous messageGo to next message
b_52globemaster
Messages: 51
Registered: July 2005
Member
is it possible to simply to call it


select assemble_clob(xmlcontent) from v_something where id=17852
(specifying here a lot of condition , and simply retrieving the corresponding row)
?


i'm gona call the function in a loop , so it wil be difficult to pass the parameter staticly, it it can select the actual row being fetched it solved my problems


and thanks

[Updated on: Mon, 07 June 2010 07:15]

Report message to a moderator

Re: help with clob concatenation [message #459611 is a reply to message #459607] Mon, 07 June 2010 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Static query must contain the table name as it is syntaxically and semantically verified (how Oracle will know there is a column named xmlcontent if you don't tell it the table it has to search in?).
You have to use dynamic query to differ the analysis at run time.

Regards
Michel

[Updated on: Mon, 07 June 2010 07:16]

Report message to a moderator

Re: help with clob concatenation [message #459612 is a reply to message #459593] Mon, 07 June 2010 07:20 Go to previous message
b_52globemaster
Messages: 51
Registered: July 2005
Member
yeah,

may be something like that :

http://www.orafaq.com/wiki/SQL_FAQ

the function rowconcat


Previous Topic: rowconcatenate of clob
Next Topic: need query to extract values from xml data (merged)
Goto Forum:
  


Current Time: Fri Mar 29 00:04:09 CDT 2024