2

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.

Ankita_K
  • 653
  • 2
  • 12
  • 21

2 Answers2

1

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

Codo
  • 75,595
  • 17
  • 168
  • 206
  • If I remove DBMS_LOB.SUBSTR, >SELECT (trpln.dtc_source||' - '||trpln.dtc_destination) union select (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) Error "Expression must have same datatype as corrosponding expression" – Ankita_K Oct 19 '12 at 10:24
  • I want to do union of two select. The first select returns varchar2 SELECT (trpln.dtc_source||' - '||trpln.dtc_destination) and second select result is taken as CLOB – Ankita_K Oct 19 '12 at 10:27
0

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

Community
  • 1
  • 1
Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96