Merge CLOB into one line - ORACLE

Question

I have field in a Oracle database of type CLOB. I need to merge the multiple line of this filed into one line. Here's an example of the content:

"<div class="pi-tier3"><div class="pi-pdpmainbody"><p><b>FIT</b></p><p>Core Indy - Compression </p>
<p><b>PRO</b></p><ul>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
<li></li>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
</ul>
<p><b>PRP</b></p><ul>
<li>100%</li>
<li>DRY</li>
</ul>
<p>ABCDEF:PmId12345RmLn1VlId0</p>
</div></div>"

The result should look like this:

"<div class="pi-tier3"><div class="pi-pdpmainbody"><p><b>FIT</b></p><p>Core Indy - Compression </p> <p><b>PRO</b></p><ul> <li>ABCDEF:PmId12345RmLn1VlId0</li> <li>ABCDEF:PmId12345RmLn1VlId0</li> <li>ABCDEF:PmId12345RmLn1VlId0</li> <li></li> <li>ABCDEF:PmId12345RmLn1VlId0</li> </ul> <p><b>PRP</b></p><ul> <li>100%</li> <li>DRY</li> </ul> <p>ABCDEF:PmId12345RmLn1VlId0</p> </div></div>"

Answer 1

Because you are working with xml-style document. You can use xmlserializer to remove all indent and of lines. Note input data has to be valid xml.

select xmlserialize( content xmltype(your_clob_with_valid_xml) 
                    as clob  NO INDENT )from dual;

And real exmaple.

select xmlserialize( content xmltype('<div class="pi-tier3"><div class="pi-pdpmainbody"><p><b>FIT</b></p><p>Core Indy - Compression </p>
<p><b>PRO</b></p><ul>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
<li></li>
<li>ABCDEF:PmId12345RmLn1VlId0</li>
</ul>
<p><b>PRP</b></p><ul>
<li>100%</li>
<li>DRY</li>
</ul>
<p>ABCDEF:PmId12345RmLn1VlId0</p>
</div></div>')   as clob  NO INDENT )from dual;

Additionally, with this function, you can do pretty printing of XML. Replace no indet with indent size = 2

Answer 2

You are looking for a way to remove EOL chars in an Oracle CLOB.

Try something like this:

UPDATE clob_table 
   SET clob_column = REPLACE(REPLACE(clob_column, chr(10), ''), ch(13),'')
 WHERE <my criteria>
;

Beware the replace function can have impacts on your db performance