Thanks to guru's on this list, my update is working as expected. However I've decided to see if I can get it to work without using a staging table.
So, in summary, I have a table with a column for each line number and then a narrative for each line.
My goal is to load the narrative into one CLOB column.
1 This is line 1
2 This is line 2
3 This is line 3
with goal for CLOB column containing:
This is line 1 This is line 2 This is line 3 etc.
I can update table fine with this SQL:
update table a
SET COLUMN_CLOB=(select RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", ON_NARRATIVE||chr(32)) order by ON_LINE), '/x/text()').getclobval(),',')
from table b
where a.V_ACCESSION_NUM = b.V_ACCESSION_NUM
But I want to try to get it to display without first loading into a staging table and updating:
RTRIM(EXTRACT(XMLAGG(XMLELEMENT("x", ORDER_NARRATIVE.NARRATIVE||chr(32)) order by ORDER_NARRATIVE.LINE), '/x/text()').getclobval(),',')
ERROR at line 33:
ORA-00932: inconsistent datatypes: expected - got CLOB
I've tried to_char, TO_CLOB, DBMS_LOG.SUBSTR but I can't get it to work.
Any ideas if this is doable?