I have the following code which works fine for smaller data it extracts, but when it runs into a record that is larger sized it errors. How would i go about getting around the ORA-01706:user function result value was too large error?
FOR r IN
(SELECT Extract(Value(p),'pmt:PayInfo/pmt:Single', r_namespace) As Address,
ExtractValue(Value(p),'pmt:PayInfo/pmt:Single/pmt:SingExtRef', r_namespace) AS extref,
ExtractValue(Value(p),'pmt:PayInfo/pmt:Single/pmt:SingSequence', r_namespace) AS singseq
FROM TABLE(XMLSequence(Extract(payXml,'/abcd:abcd/abcd:Pmt/pmt:Payments/pmt:PayInfo['||ctr2||']', r_namespace))) p
)
LOOP
FOR row1 IN
(SELECT ExtractValue(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpType/text()', r_namespace) AS singtype,
ExtractValue(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[1]/text()', r_namespace) AS singtext1,
ExtractValue(Value(l),'/pmt:SingAddInfo/cmn:AddInfoCmpText[2]/text()', r_namespace) AS singtext2,
FROM TABLE(XMLSequence(Extract(r.Address,'/pmt:Single/pmt:SingAddInfo', r_namespace))) l
)
LOOP
IF (row1.singtype = 'IATCode')
THEN
r_iatcode := row1.singtext1;
r_iatcode2 := row1.singtext2;
ELSIF (row1.singtype = 'IndivID')
THEN
r_payor_identifier := row1.singtext1;
END IF;
END LOOP;
END LOOP;
The canonical ( for this valid use case) that is passed to the oracle SP itself is 10300 lines( a huge file). But the part that is blowing up (ie the part of the code above that does the extract) comprises 90% of that file i would say. Just think of it as a payroll record, with company info the 10% and payroll info comprising the rest 90%. It is, or should i say, can be a big file and just found out it blows up when the file size gets up there. From what i found out, ExtractValue loads the whole xml document into DOM, but that would not be causing the error because the part that blows up consists of 90% of the file anyways. What would be a good workaround with the extract function gurus?
Thank you for your time!