This discussion is archived
2 Replies Latest reply: May 10, 2012 3:48 PM by jteme RSS

xml query hungs up with large xml response from utl_http request

jteme Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I didnt answer before because we was on fire I have to test it. It works.
    thank you very much.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points