I got a pl/sql process that call a web service and put the xml result in a collection on the clob column. The sql query of the report try to run then throw me ORA-06502: PL/SQL: numeric or value error, but only if the xml is large over 70,000 characters under that for example 10,000 the error not appear.
the sql query I using:
with t as (
select XMLTYPE(d.clob001)
as xml
from wwv_flow_collections d where d.collection_name = 'OUTPUTCOL' )
select x.accountId as "Account"
,x.nameId as "Name"
,x.ssn as "Social Security"
,y.said as "Service Agreement"
,y.premiseId as "Premise Id"
,y.premiseInfo as "Address"
from t
,xmltable('/output/acctList'
passing t.xml
columns accountId varchar2(20) path './accountId',
nameId varchar2(254) path './primaryName',
ssn varchar2(20) path './ssn'
,premList xmltype path './premList'
) x
,xmltable('//premList'
passing x.premList
columns said varchar2(50) path '//said'
,premiseId varchar2(50) path '//premiseId'
,premiseInfo varchar2(4000) path '//premiseInfo'
) y
Thanks for your time.