How to format oracle clob fields together

Question

So I have a table that has a clob column. And sometimes I have multiple rows that I need to concatenate together which I do doing something like

Select n.textid 
LISTAGG(TRIM(n.text), '||')
WITHIN GROUP (ORDER BY n.createdate DESC) AS allnotes
from notetbl n 
GROUP BY n.noteid;

which gives me 99% of the data formatted as I want. But when the clob field has something like

this is the first line

this is the second

(two lines there is no white space in-between)

when exporting that data into excel or a csv file the second line adds a single double quotation mark to the end of the second line. Like this.

this is the first line

this is the second"

If I manually open the clob and shorten it to one line the " doesn't appear on the export. So is there a way to force all the clob data to be on one line? or somehow nothave that second quotation mark appear? thanks for any advice!

Answer 1

It looks to me like you have at the end of your text column a Chr(10) in some row(s).

--    S a m p l e    D a t a : 
Create Table notetbl (NOTEID Number(3), TEXTID VARCHAR2(3), text CLOB, CREATEDATE Date);
Insert Into notetbl Values ( 1, 'A', 'first line'  || Chr(10), TRUNC(Sysdate + 1));
Insert Into notetbl Values ( 2, 'A', 'second line', TRUNC(Sysdate));

... if you transform your query's resultset into csv (I work with Oracle SQL Developer where commented csv do the job)

Select /*csv*/ *
From   ( Select    n.noteid, 
                   LISTAGG(TRIM(n.text), '||') WITHIN GROUP (ORDER BY n.createdate DESC) AS allnotes
         From      notetbl n 
         Group By  n.noteid
       );

... result would be like :

"NOTEID","ALLNOTES"
1,"first line
"
2,"second line"

In Excel such a cell would have "Wrap Text" option ON.
If there is an unintentional Chr(10) character in the data, check the content of your text column to see if this is the case.

Select n.*
From   notetbl n 
Where  InStr(n.text, Chr(10)) > 0

/*
    NOTEID TEXTID TEXT            CREATEDATE
---------- ------ --------------  ----------
         1 A      first line      17.08.24    */

If this is the cause - get ridd of extra Char(10) characters...