Hello,
my function "get_bel" returns the following CLOB string.
I am using the following sql statement in my function.
select rtrim(xmlcast(xmlagg(xmlelement(e,lstg_geb_nr,',') order by lstg_geb_nr) as clob),',') bel
from ...
0010,0010,0010,0010,0010,0010,0010,0010,0010,0010,0051,0051,
0120,0120,0120,0120,0120,0120,0120,0120,0120,0120,0211,0211,
1620,1620,1620,1620,1620,1620,1620,1620,1620,2010,2010,2010,
3030,3030,3030,3030,3030,3030,3030,3030,3410,3410,3410,3410,
But I would like to have distinct values such
0010, 0051, 0120, 0211, ...
I follow the posts of https://community.oracle.com/thread/1090215?start=0&tstart=0
I am using CLOB.
--> CREATE OR REPLACE type BelList as table of CLOB
/
But when I am using "select set(cast(collect(lstg_geb_nr) as BelList)) distinct_bel"
I get an ORA-00932: inconsistent datatypes: expected - got CLOB.
~
When I am using the following statement without a collection type and without collect-statement
I get the ORA-00932 too.
select xmlquery('string-join(distinct-values(//text()), ",")'
passing bel returning content
).getCLOBVal() as segment1
from
(
select rtrim(xmlcast(xmlagg(xmlelement(e,lstg_geb_nr,',') order by lstg_geb_nr) as clob),',') bel
...
)
/
Does anybody have an idea?