I am running the following query:
select x.*
from DRMAPPS t
, xmltable(
xmlnamespaces(
'http://www.oracle.com/drm' as "drm"
)
, '
for $d in /drm:MetadataExport/drm:PropertyDefinitions/drm:PropertyDefinition[drm:Parameters/drm:PropDefParam/@Abbrev = "Formula"]
for $p in /drm:MetadataExport/drm:PropertyDefinitions/drm:PropertyDefinition
where contains($d/drm:Parameters/drm:PropDefParam[@Id = "0"]/drm:Value, $p/@Abbrev)
return <r def="{$d/@Abbrev}" used="{$p/@Abbrev}" />
'
passing t.XMLCONTENTS
columns defined_prop varchar2(80) path '@def'
, used_prop varchar2(80) path '@used'
) x
where t.APPNAME = 'drm.xml'
;
and this runs fine when the Value node referred to in the where clause is a short string. But when it is very long, as it sometimes is, I get the error
ORA-01706: user function result value was too large
Lots of searching didn't lead me to any possible workaround. I'm not trying to return a long string, just use it in the contains function. Can anyone suggest a way to make this work for long strings?