CODEWITHSUNDEEP
oracle10gtype-conversion
Ankita_K
Ankita_K

Reputation: 653

CLOB to varchar2 conversion in oracle

I need to get the result of the below query in varchar2 to display in a view. I tried using dbms_lob.substr as below but then it gives me 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 is returning result in CLOB.

Upvotes: 2

Views: 2720

Answers (2)

Andreas Covidiot
Andreas Covidiot

Reputation: 4755

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

Upvotes: 0

Codo
Codo

Reputation: 78815

WM_CONCAT returns a VARCHAR2, not a CLOB. So you can remove the call to DBMS_LOB.SUBSTR.

Upvotes: 1

Related Questions