2 Replies Latest reply: May 10, 2012 5:48 PM by jteme RSS

    xml query hungs up with large xml response from utl_http request

    jteme
      We are having very sensitive problem in Production environment with xmlquery.
      When receive a small or medium size xml, the query shown below works. But when the xml is large or very large its hung and slow down all the database.
      We are with Oracle 11gR2
      We are using clob to parse the response from the http request.
      What could be the problem or the solutions?. Please help. Its urgent...


      SELECT opciones_obj (x.strindice,
      x.nombrecompleto,
      x.nombre,
      x.strtipodato,
      x.codigoopcion,
      x.floatval,
      x.strtipo,
      x.strval)
      BULK COLLECT INTO t_opciones
      FROM XMLTABLE (
      xmlnamespaces (
      'http://schemas.xmlsoap.org/soap/envelope/' AS "env",
      'http://wsevaluarreglacondicioncomercial/' AS "ns0",
      'http://wsevaluarreglacondicioncomercial/types/' AS "ns1",
      'http://www.oracle.com/webservices/internal/literal' AS "ns2"),
      '/env:Envelope/env:Body/ns0:listarOpcionesAtributoEventoResponseElement/ns0:result/ns1:listaVariables/ns2:item/ns2:item'
      PASSING rsp_xml
      COLUMNS strindice VARCHAR2 (4000)
      PATH 'ns2:mapEntry[ns2:key="strIndice"]/ns2:value',
      nombrecompleto VARCHAR2 (4000)
      PATH 'ns2:mapEntry[ns2:key="nombreCompleto"]/ns2:value',
      nombre VARCHAR2 (4000)
      PATH 'ns2:mapEntry[ns2:key="nombre"]/ns2:value',
      strtipodato VARCHAR2 (4000)
      PATH 'ns2:mapEntry[ns2:key="strTipoDato"]/ns2:value',
      codigoopcion NUMBER
      PATH 'ns2:mapEntry[ns2:key="codigoOpcion"]/ns2:value',
      floatval FLOAT
      PATH 'ns2:mapEntry[ns2:key="floatVal"]/ns2:value',
      strtipo VARCHAR2 (4000)
      PATH 'ns2:mapEntry[ns2:key="strTipo"]/ns2:value',
      strval VARCHAR2 (4000)
      PATH 'ns2:mapEntry[ns2:key="strVal"]/ns2:value') x;
        • 1. Re: xml query hungs up with large xml response from utl_http request
          odie_63
          What could be the problem or the solutions?
          1) Create an XMLType table (could be temporary) using binary XML storage :
          create table tmp_xml of xmltype
          xmltype store as securefile binary xml;
          2) In your procedure, load the XMLType containing the response (rsp_xml) into the table :
          insert into tmp_xml values (rsp_xml);
          3) Then, execute the query directly from the table :
          SELECT opciones_obj ( ... )
          BULK COLLECT INTO t_opciones
          FROM tmp_xml t
             , XMLTABLE (
                   xmlnamespaces ( ... ),
                   '/env:Envelope/env:Body/...'
                   PASSING t.object_value
                   COLUMNS ...
          4) At the end of the procedure, delete (or truncate) the table or simply let the table delete itself when the session ends (in case you created it TEMPORARY)
          • 2. Re: xml query hungs up with large xml response from utl_http request
            jteme
            I didnt answer before because we was on fire I have to test it. It works.
            thank you very much.