SQL & PL/SQL

Why do you not set Content-Length in the header? Just how large is the envelope? And how do you post the SOAP envelope? It is better to create the enveloper upfront as a 32KB varchar2 string, or as a CLOB - and then pass that (by reference) to the procedure unit that does the HTTP interaction.

Hi Billy,

I thought I might get asked about Content-Length after I posted There isn't really a 'reason' why I don't set the Content-Length, other than that in this case (with chunked transfer-encoding) it appears to make no difference to the performance.

Here is the code that checks the request length/envelope and then sets whether to use chunked transfer-encoding, you'll notice I have now set the Content-Length:

IF l_request_length <= 32767
      THEN
         
         logger.LOG ('Short request.',
                     k_scope,
                     NULL,
                     l_params);
         
         UTL_HTTP.set_header (l_http_req, 'Content-Length', l_request_length);
         UTL_HTTP.write_text (l_http_req, p_soap_request);
         logger.LOG ('Request text written.',
                     k_scope,
                     NULL,
                     l_params);
      ELSE
      
         UTL_HTTP.set_header (l_http_req, 'Content-Length', l_request_length);
         UTL_HTTP.set_header (l_http_req, 'Transfer-Encoding', 'chunked');
         
         logger.LOG ('Long request - To be chunked.',
                     k_scope,
                     NULL,
                     l_params);
                     
         WHILE (l_offset < l_request_length)
         LOOP
            DBMS_LOB.read (p_soap_request,
                           l_amount,
                           l_offset,
                           l_buffer);
            UTL_HTTP.write_text (l_http_req, l_buffer);
            l_offset := l_offset + l_amount;
         END LOOP;
         
         logger.LOG ('Request text written.',
                     k_scope,
                     NULL,
         l_params);
      END IF
     l_http_resp := UTL_HTTP.get_response (l_http_req);

The processing 'only' takes a 1-3 seconds to reach line 43 (in above code listing), which includes generating the envelope, converting the blob to base64clob but then line 43 takes 240 seconds for UTL_HTTP.get_response (l_http_req) to return. However, when it does eventually return the response is valid and I can do what I need.

l_request_length for the test 10Mb file is 14524103, p_soap_request is the envelope that is prepared outside of this routine (based upon the SOAP action being performed) and passed in (IN OUT NOCOPY) but changing this to be just passed IN makes no difference on performance.

Below is the signature for my HTTP interaction subroutine:

FUNCTION communicate_with_api (p_soap_request IN OUT NOCOPY CLOB, p_soap_action IN VARCHAR)
      RETURN XMLTYPE

The question is around the discrepancy in performance between SOAP UI and UTL_HTTP.get_reponse.

I suspect that up to the get_response() call, there is not yet any network transfer to the web server - and that the calls prior like set_header() and write_text(), only preps the internal struct of the db core C module that services the UTL_HTTP API.

If so, then get_response() is a pure TCP write to socket call (and responsible for writing the 10MB payload to the TCP socket) - which means you are looking a networking issue of sorts. Can you test SOAPUI on the actual db server, to ensure that the SOAPUI test uses the same network infrastructure than your UTL_HTTP code?

If not, perhaps wget or curl can be used to make the 10MB SOAP call?

In my view the network is the obvious culprit - and needs to be eliminated as the cause of the problem, before popping the hood on the Oracle server process running the UTL_HTTP code.

Appreciate your reasoning Billy, I agree this is a sensible way to try and isolate this. I don't have access to perform SOAPUI testing directly from the DB server myself but I can certainly request for it to be done before delving further into the Oracle processes. Up until this point SOAPUI testing has been conducted using my client PC so can almost guarantee it doesn't follow the same 'route' on the network, may also be able to Wireshark the requests to give a bit more information.

I will post back with some further additions once these tests have been conducted.

Thanks