Extract specific value from CLOB (containing XML) while creating one delimited string per row of a table. CLOB value may be null on some rows

Question

Some Context: The DB is Oracle. I am trying to create one delimited string per row of a table. Some of the values of the delimited string must come from CLOBs (containing XML). The CLOBs may be null on some rows and this is where the issue lies.

For example, I have a Table: 'Item' with the following rows: 'Item_ID', 'Item_CD', 'Item_TXT' (CLOB). The table has two rows. One row has the following XML stored within 'Item_TXT', the other row's 'Item_TXT' is null.

<OuterTag>      
    <InnerTag>test</InnerTag>   
</OuterTag>     

I created the following SQL to return the first 3 elements of the delimited string:

SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD 
FROM Item; 

This worked successfully:

Item%#12345%#A

Item%#123456%#A

I then tried to add the 4th element (value from CLOB).

SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item; 

This failed with following error:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

I have narrowed this issue down to sometimes the Item_TXT is null and XMLTYPE() cannot handle this. To prove this I ran the following:

SELECT 'Item%#'|| Item_ID ||'%#'|| Item_STAT_CD
EXTRACT(XMLTYPE(Item_TXT), '//OuterTag/InnerTag/text()').getStringVal()
FROM Item
WHERE Item_TXT IS NOT NULL; 

This worked successfully:

Item%#12345%#A%#test

Is there any way of processing entire table, extracting value from clob if available, otherwise adding null/nothing to delimited string?

Answer 1

the reason for your error by the way is that the xmltype constructor is flawed with null lobs. Personally i always use xmltype.createxml(item_txt) instead. you will find it works as well without having to worry about nvl etc.