ExtractValue/xmlsequence

Question

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;

Thank you for your time!

Answer 1

When you get ORA-01706: user function result value was too large and you are using EXTRACTVALUE() it is because this function can only return at most a VARCHAR2(4000 CHAR) result.

Below is a simplified generic example.

The following breaks when it encounters a "VALUES" node with more than 4000 characters:

SELECT pk,
       EXTRACTVALUE(VALUE(xml), '*/VALUES') as vals
  FROM tableName a,
       TABLE (XMLSEQUENCE (EXTRACT (a.xmlFieldName, '/SOME/PATH/*'))) xml
 WHERE s.pk = 1         
   AND EXTRACTVALUE(VALUE(xml), '*/VALUES') IS NOT NULL;

Using the following syntax instead, you can define that you expect a CLOB result, which will work:

SELECT pk,
       xml.vals
  FROM tableName a,
       XMLTable('/SOME/PATH/*'
                PASSING a.xmlFieldName
                COLUMNS vals     CLOB    PATH   'VALUES/text()') xml
 WHERE a.pk = 1
   AND xml.vals IS NOT NULL;

The above examples are when the XML is stored in a XMLType column in a table. If you are working with a local PL/SQL variable instead of a table, you can do:

SELECT xml.vals
  FROM XMLTable('/SOME/PATH/*'
                PASSING local_xmltype_variable
                COLUMNS vals     CLOB    PATH   'VALUES/text()') xml
 WHERE xml.vals IS NOT NULL;