DevOps, CI/CD and Automation

Hi

My procedure connects to a webservice and gets the data as XML

This is what my code looks like

HTTP_RESP := UTL_HTTP.GET_RESPONSE (HTTP_REQ);

UTL_HTTP.READ_TEXT (HTTP_RESP, SOAP_RESPOND);

SOAP_RESPOND :=
         XMLTYPE (SOAP_RESPOND).
         EXTRACT (
            '/soap:Envelope/soap:Body/Streets_Events_GetResponse/Streets_Events_GetResult/text()',
            'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/",
                                    xmlns="http://webapi/"').
         GETCLOBVAL ();

      Xml_Result :=
         XMLTYPE (
            DBMS_XMLGEN.CONVERT (SOAP_RESPOND, DBMS_XMLGEN.ENTITY_DECODE));

      INSERT INTO XXHCC_STAGING_XML
           VALUES (Xml_Result, SYSDATE, ' Street_Events_Get SOAP Response');

      COMMIT;

It doesnt work sometime and I have a feeling its reaching its 32K limit and not responding. If i give the same parameters the webservice when run individually runs fine, but when i call from SQL plus or any other oracle procedure i get the following error

ORA-00600: internal error code, arguments: [733], [268431488], [pga heap], [], [], [], [], []

Am i right in assuming that from pl/sql it can only read 32K of data and if I long XML document with records being returned it would fail.

Its happening for only certain records and most of the time it works fine, I bring the data with a date range, and when i bring between 2 years it fails as it has got many records around 40-50 records in a xml document.

What is the best alternative to overcome this.

Thanks

Sri