This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,877 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Categories

Transfer Timeout using UTL_HTTP to certain Endpoints

CInglezSTC
CInglezSTC Member Posts: 17 Green Ribbon
in SQL & PL/SQL

Hi.

I am having trouble trying to consume a simple SOAP webservice using UTL_HTTP (I've tried APEX_WEB_SERVICE too, with the same results).

I am using Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production

This is the function I am trying:

create or replace FUNCTION STC_WS_SOAP_UTL RETURN varchar2


AS


  l_envelope      VARCHAR2(32767);
  l_result        VARCHAR2(32767) := null;


   http_req        UTL_HTTP.req;
   http_resp       UTL_HTTP.resp;


BEGIN


    -- Build a SOAP document appropriate for the web service.


    l_envelope := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:nfes="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4">
   <soap:Header/>
   <soap:Body>
      <nfes:nfeDadosMsg>
         <consStatServ xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00"><tpAmb>2</tpAmb><cUF>35</cUF><xServ>STATUS</xServ></consStatServ>
      </nfes:nfeDadosMsg>
   </soap:Body>
</soap:Envelope>';


    UTL_HTTP.set_wallet('file:C:\Oracle\wallet_xxxxxxx', 'XXXXXXXX');
    http_req := UTL_HTTP.begin_request('https://homologacao.nfe.fazenda.sp.gov.br/ws/nfestatusservico4.asmx', 'POST', 'HTTP/1.1');
    UTL_HTTP.set_header(http_req, 'Accept-Encoding', 'gzip,deflate');
    UTL_HTTP.set_header(http_req, 'Content-Type', 'application/soap+xml;charset=UTF-8');
    UTL_HTTP.set_header(http_req, 'SOAPAction', 'http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4/nfeStatusServicoNF');
--    UTL_HTTP.set_header(http_req, 'transfer-encoding', 'chunked');
    UTL_HTTP.set_header(http_req, 'Content-Length', '408');
--    UTL_HTTP.set_header(http_req, 'Host', 'homologacao.nfe.fazenda.sp.gov.br');
    UTL_HTTP.set_header(http_req, 'Connection', 'Keep-Alive');
    UTL_HTTP.set_header(http_req, 'User-Agent', 'Apache-HttpClient/4.5.5 (Java/16.0.1)');
--    UTL_HTTP.set_transfer_timeout(http_req, 300);
    UTL_HTTP.write_text(http_req, l_envelope);
    
    http_resp := UTL_HTTP.get_response(http_req);
    UTL_HTTP.read_text (http_resp, l_result);
    UTL_HTTP.end_response (http_resp);


    RETURN l_result;

END;

When I try to consume the webservice on other endpoints it works. The problem is: I need to run it on this endpoint. The error is:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1288
ORA-29276: transfer timeout
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512: at "SYS.UTL_HTTP", line 1278
ORA-06512: at "SETICIS.STC_WS_SOAP_UTL", line 37


When I post the following request on SoapUI, it works:

POST https://homologacao.nfe.fazenda.sp.gov.br/ws/nfestatusservico4.asmx HTTP/1.1
Accept-Encoding: gzip,deflate
Content-Type: application/soap+xml;charset=UTF-8;action="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4/nfeStatusServicoNF"
Content-Length: 408
Host: homologacao.nfe.fazenda.sp.gov.br
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.5.5 (Java/16.0.1)


<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:nfes="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4">
   <soap:Header/>
   <soap:Body>
      <nfes:nfeDadosMsg>
         <consStatServ xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00"><tpAmb>2</tpAmb><cUF>35</cUF><xServ>STATUS</xServ></consStatServ>
      </nfes:nfeDadosMsg>
   </soap:Body>
</soap:Envelope>


I have already set ACE, wallet, etc.

Any help would be appreciated.

Thanks in advance

Tagged:
· Share on TwitterShare on Facebook
«13

Answers

  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Mar 9, 2022 2:39AM

    How big is the response when called successfully from SoapUI? Perhaps it exceeds your UTL_HTTP.read_text call, which means it will only read one chunk and not all of it, so the endpoint might balk. You should never assume responses are just 32k. Write your code to be robust by allowing for any size. You loop and iterate read_text concatenating onto a response clob until utl_http.end_of_body exception is raised.

    Use a proxy to grab and exactly compare the http headers between Oracle and SoapUI. Perhaps there's something different.

    · Share on TwitterShare on Facebook
  • CInglezSTC
    CInglezSTC Member Posts: 17 Green Ribbon

    Hi Paul. Thanks for your reply.

    In this test case, the reponse is certainly smaller than 32k. For the real world, I'll prepare for it, since response messages will be much larger.

    Can you point me to some example on how to see the real request (with headers) being sent? Is the a way to log it somewhere? O use any "sniffer"? This is not my area of expertise.....

    Thanks again.

    · Share on TwitterShare on Facebook
  • Anton Scheffer
    Anton Scheffer Senior Java Developer NieuwegeinMember Posts: 1,950 Gold Trophy

    Are you sure your Content-Length is correct?

    Any reason why for the SoapUI request you use an action inside the Content-Type, and a separate SoapAction header for the PL/SQL request?

    · Share on TwitterShare on Facebook
  • CInglezSTC
    CInglezSTC Member Posts: 17 Green Ribbon

    Hi Anton. Thanks for your reply.

    Are you sure your Content-Length is correct?

    I have already tried it both ways. Somebody suggested I should try to separate the headers.

    Any reason why for the SoapUI request you use an action inside the Content-Type, and a separate SoapAction header for the PL/SQL request?

    I am using lengthb(l_envelope) to make sure the size is correct. See the code now:

    create or replace FUNCTION STC_WS_SOAP_UTL RETURN varchar2


AS


  l_envelope      VARCHAR2(32767);
  l_result        VARCHAR2(32767) := null;


   http_req        UTL_HTTP.req;
   http_resp       UTL_HTTP.resp;


BEGIN


    -- Build a SOAP document appropriate for the web service.


    l_envelope := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:nfes="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4">
   <soap:Header/>
   <soap:Body>
      <nfes:nfeDadosMsg>
         <consStatServ xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00"><tpAmb>2</tpAmb><cUF>35</cUF><xServ>STATUS</xServ></consStatServ>
      </nfes:nfeDadosMsg>
   </soap:Body>
</soap:Envelope>';


    UTL_HTTP.set_wallet('file:C:\Oracle\wallet_XXXXXX', 'XXXXXX');
    http_req := UTL_HTTP.begin_request('https://homologacao.nfe.fazenda.sp.gov.br/ws/nfestatusservico4.asmx', 'POST', 'HTTP/1.1');
    UTL_HTTP.set_header(http_req, 'Accept-Encoding', 'gzip,deflate');
    UTL_HTTP.set_header(http_req, 'Content-Type', 'application/soap+xml;charset=UTF-8; action="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4/nfeStatusServicoNF"');
--    UTL_HTTP.set_header(http_req, 'SOAPAction', 'http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4/nfeStatusServicoNF');
--    UTL_HTTP.set_header(http_req, 'transfer-encoding', 'chunked');
    UTL_HTTP.set_header(http_req, 'Content-Length', lengthb(l_envelope));
--    UTL_HTTP.set_header(http_req, 'Host', 'homologacao.nfe.fazenda.sp.gov.br');
    UTL_HTTP.set_header(http_req, 'Connection', 'Keep-Alive');
--    UTL_HTTP.set_header(http_req, 'User-Agent', 'Apache-HttpClient/4.5.5 (Java/16.0.1)');
--    UTL_HTTP.set_transfer_timeout(http_req, 300);
    UTL_HTTP.write_text(http_req, l_envelope);
    
    http_resp := UTL_HTTP.get_response(http_req);
    UTL_HTTP.read_text (http_resp, l_result);
    UTL_HTTP.end_response (http_resp);


    RETURN l_result;


END;

    And the same results:

    ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1288
ORA-29276: transfer timeout
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512: at "SYS.UTL_HTTP", line 1278
ORA-06512: at "SETICIS.STC_WS_SOAP_UTL", line 37
    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Mar 9, 2022 2:35PM

    A few of suggestions :

    • Standards say User-Agent SHOULD be sent with requests. Try with "Mozilla/5.0".
    • Try setting Accept-Encoding to */* , I've found some encodings cause issues, as sometimes they're not supported.
    • Try removing Content-Type "action".
    • Try setting HTTP header cache-control to no-cache, in case it's a caching issue and the cache is mangled.
    • Check whether this endpoint uses authentication.
    • Use UTL_HTTP.GET_DETAILED_SQLERRM

    How many bytes is the response from SoapUI?

    · Share on TwitterShare on Facebook
  • CInglezSTC
    CInglezSTC Member Posts: 17 Green Ribbon

    Hi Paul. I did as you suggested, with the sabe results:

    create or replace FUNCTION STC_WS_SOAP_UTL RETURN varchar2


AS


  l_envelope      VARCHAR2(32767);
  l_result        VARCHAR2(32767) := null;


   http_req        UTL_HTTP.req;
   http_resp       UTL_HTTP.resp;


BEGIN


    -- Build a SOAP document appropriate for the web service.


    l_envelope := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:nfes="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4">
   <soap:Header/>
   <soap:Body>
      <nfes:nfeDadosMsg>
         <consStatServ xmlns="http://www.portalfiscal.inf.br/nfe" versao="4.00"><tpAmb>2</tpAmb><cUF>35</cUF><xServ>STATUS</xServ></consStatServ>
      </nfes:nfeDadosMsg>
   </soap:Body>
</soap:Envelope>';


    UTL_HTTP.set_wallet('file:C:\Oracle\wallet_XXXXXX', 'XXXXXX');
    http_req := UTL_HTTP.begin_request('https://homologacao.nfe.fazenda.sp.gov.br/ws/nfestatusservico4.asmx', 'POST', 'HTTP/1.1');
    UTL_HTTP.set_header(http_req, 'Cache-Control', 'no-cache');
    UTL_HTTP.set_header(http_req, 'Accept-Encoding', '*');
    UTL_HTTP.set_header(http_req, 'Content-Type', 'application/soap+xml;charset=us-ascii');
--    UTL_HTTP.set_header(http_req, 'SOAPAction', 'http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4/nfeStatusServicoNF');
    UTL_HTTP.set_header(http_req, 'Content-Length', lengthb(l_envelope));
--    UTL_HTTP.set_header(http_req, 'Host', 'homologacao.nfe.fazenda.sp.gov.br');
    UTL_HTTP.set_header(http_req, 'Connection', 'Keep-Alive');
    UTL_HTTP.set_header(http_req, 'User-Agent', 'Mozilla/5.0');
--    UTL_HTTP.set_transfer_timeout(http_req, 300);
    UTL_HTTP.write_text(http_req, l_envelope);
    
    http_resp := UTL_HTTP.get_response(http_req);
    UTL_HTTP.read_text (http_resp, l_result);
    UTL_HTTP.end_response (http_resp);


    RETURN l_result;


EXCEPTION
    when others then
        return('Error: ' || utl_http.get_detailed_sqlerrm);


END;

    Result:

    Error: ORA-29276: transfer timeout

    The response from SoapUI is 615 bytes long:

    HTTP/1.1 200 OK
Cache-Control: private, max-age=0
Content-Type: application/soap+xml; charset=utf-8
Server: Microsoft-IIS/10.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Date: Wed, 09 Mar 2022 14:55:29 GMT
Content-Length: 615


<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><nfeResultMsg xmlns="http://www.portalfiscal.inf.br/nfe/wsdl/NFeStatusServico4"><retConsStatServ versao="4.00" xmlns="http://www.portalfiscal.inf.br/nfe"><tpAmb>2</tpAmb><verAplic>SP_NFE_PL009_V4</verAplic><cStat>107</cStat><xMotivo>Serviço em Operação</xMotivo><cUF>35</cUF><dhRecbto>2022-03-09T11:55:29-03:00</dhRecbto><tMed>1</tMed></retConsStatServ></nfeResultMsg></soap:Body></soap:Envelope>

    

    This webservice uses 2-way authetication, with a user certificate. It is already in the wallet and works with other endpoints with the same requirement.


    Thanks again

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond
    edited Mar 9, 2022 6:57PM

    If you specifyConnection : Keep-Alive you should also specify a Keep-Alive header with timeout and max connections like this Keep-Alive: timeout=5, max=1000 so you aren't following the standards there and it could be the culprit. Connection-specific header fields such as Connection and Keep-Alive are prohibited in later versions of HTTP anyway, so I wouldn't bother with either.

    How long does SoapUI take to instantiate the connection and return a response? Perhaps it is a valid timeout. Put timings around your UTL_HTTP.get_response call.

    People often wrongly think UTL_HTTP.set_transfer_timeout sets the timeout for a call, but it's actually for subsequent HTTP request-responses over that same connection ONCE you have successfully established a connection to the web service. It's not for initial connection, which yours would be as a fresh call each time. You may need to set SQL*Net parameters SQLNET.OUTBOUND_CONNECT_TIMEOUT or TCP.CONNECT_TIMEOUT if they are too low.

    More info in Doc ID 760664.1 : https://support.oracle.com/knowledge/Oracle%20Database%20Products/760664_1.html

    Another option is to try sending the request as blob data using  UTL_HTTP.WRITE_RAW in case it is some char encoding issue, likewise with the response using UTL_HTTP.READ_RAW.

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond

    Forgot to say, there's an undocumented facility to low level trace UTL_HTTP socket actions and ACE/ACL actions, and that has helped me in the past. Call this...

    ALTER SESSION SET EVENTS = '<event> TRACE NAME CONTEXT FOREVER, LEVEL <level>';

    before running your code. Switch off using a similar statement again but ending with CONTEXT OFF. 

    For UTL_HTTP : Event = 10937, Level = 4

    For ACE / ACL: Event = 10937, Level = 6

    Then check your session trace file.

    · Share on TwitterShare on Facebook
  • CInglezSTC
    CInglezSTC Member Posts: 17 Green Ribbon

    Hi Paul. Thanks again for your time and patience.

    Removed the Keep-Alive header (even though the SoapUI client uses it) - same results

    When I call the service, the response is immediate. Much less then 1 second, and it gives me the error.

    Tried UTL_RAW. Same error.

    I've set the trace events, and the resulting trace is

    *** 2022-03-09T17:01:22.842789-03:00 (CDB$ROOT(1))
2022-03-09 17:01:22.855 : fsd_notify_cb: FsDirect not implemented


*** 2022-03-09T17:01:28.228754-03:00 (XEPDB1(3))
*** SESSION ID:(135.13346) 2022-03-09T17:01:28.228754-03:00
*** SERVICE NAME:(xepdb1) 2022-03-09T17:01:28.228754-03:00
*** MODULE NAME:(SQL Developer) 2022-03-09T17:01:28.228754-03:00
*** ACTION NAME:() 2022-03-09T17:01:28.228754-03:00
*** CLIENT DRIVER:(jdbcthin : 21.1.0.0.0) 2022-03-09T17:01:28.228754-03:00
*** CONTAINER ID:(3) 2022-03-09T17:01:28.228754-03:00
*** CLIENT IP:(10.0.0.16) 2022-03-09T17:01:28.228754-03:00
 
psdnop: SETICIS 121 3 homologacao.nfe.fazenda.sp.gov.br:443
psdnopCheckAcl: priv 2147483693 cnt 2
psdnopCheckAcl: aclid 2147493730 status 3
psdnopCheckAcl: aclid 2147493668 status 3
psdnop: denied
psdnop: SETICIS 121 2 homologacao.nfe.fazenda.sp.gov.br:443
psdnopCheckAcl: priv 2147483690 cnt 2
psdnopCheckAcl: aclid 2147493730 status 1
psdnop: granted
psdnopWalletAuth: SETICIS 121 file:C:\Oracle\wallet_XXXXXX 0
psdnopCheckAcl: priv 2147483691 cnt 1
psdnopCheckAcl: aclid 2147493731 status 1
psdnopWalletAuth: granted


*** 2022-03-09T17:01:36.154552-03:00 (CDB$ROOT(1))
ksmddg: DGA destroyed

    This is really awkward. From several endpoints, each from one Brazil State, a few have this behaviour.

    Thanks again

    · Share on TwitterShare on Facebook
  • Paulzip
    Paulzip Member Posts: 8,867 Blue Diamond

    That looks like the ACE trace (which looks ok), did you do the UTL_HTTP one?

    Another suggestion is install Wireshark and look at the TCP conversation between your PC and the endpoint. It's quite low level, but there's some good YouTube guides on how to use it.

    It could be an Oracle bug, but often it's something else.

    · Share on TwitterShare on Facebook