There are two clob variables defined in sqlplus that need to be assigned to values from SQL statement. The issue is that the values returned by SQL statement are in one column. I tried to use simple trick with decode but it doesn't work with CLOBs:
var v1 clob
var v2 clob
with x as (
select 'x' c1, to_clob('x') c2 from dual
union all
select 'y' c1, to_clob('y') c2 from dual )
select
max(decode(c1,'x',c2)) v1
, max(decode(c1,'y',c2)) v2
into :v1, :v2
from x;
There is error returned: ORA-00932: inconsistent datatypes: expected - got CLOB.
How can these CLOB values from SQL be treated so that SQL values can be placed into sqlplus CLOB variables?