Convert CLOB to varchar2 in oracle

Question

I need to get the query result below in varchar2 to display in the view. I tried using dbms_lob.substr as shown below, but then it gives me the error "ORA-22922: nonexistent LOB value".

dbms_lob.substr ((select wm_concat (tr_country) from NEXUS_TRAC_TRAVEL_PLAN_DTL where nexus_year = trdata.nexus_year and nexus_seq_no = trdata.nexus_seq_no), 4000,1),

The select query returns the result in the CLOB.

Answer 1

WM_CONCAT returns a VARCHAR2 , not a CLOB . This way you can remove the DBMS_LOB.SUBSTR call.

Answer 2

If you can upgrade to 11.2 or higher, the listagg(...) function can save your day, as described here: https://stackoverflow.com/a/310677/