3 Replies Latest reply: Aug 5, 2013 1:02 AM by Charly_Z RSS

    PL/SQL webservice call produces extra bytes in utf-8

    Kurt Geens
      We created a packaged procedure that sends a SOAP request using the UTL_HTTP functionality. Part of the original requirement was that the messages are send in UTF-8 format. It appears that either this conversion is not going quite well, or that UTL_HTTP messes the converted values up somewhere. Hopefully one of you can enlighten me on what is really happening here...

      The web service call is coded as:
      PROCEDURE ... ( i_request IN VARCHAR2 ) IS
        l_request utl_http.req;
        l_response utl_http.resp;
        utl_http.set_wallet('file:' || ..., ...);
        l_request := utl_http.begin_request(..., 'POST', 'HTTP/1.1');
        utl_http.set_authentication(l_request, ..., ...);
        utl_http.set_header(l_request, 'Content-Type', 'text/xml; charset=utf-8');
        utl_http.set_header(l_request, 'SOAPAction', '"..."');
        utl_http.set_header(l_request, 'Content-Length', LENGTH(CONVERT(i_request, 'UTF8')));
        utl_http.write_text(l_request, CONVERT(i_request, 'UTF8'));
        l_response := UTL_HTTP.GET_RESPONSE(l_request);
      This works perfectly as long as all used characters are single byte. But as soon as a multi-byte character is introduced, the LENGTH determination of the converted values does not match the actual content length anymore, and the message at the receiving end contains characters I can't explain.

      For example, a message contains the value "Réïsgids". Converted to UTF-8 (hex. notation), this would result in:

      R = 52
      é = C3 A9
      ï = C3 AF
      s = 73
      g = 67
      i = 69
      d = 64
      s = 73

      => 52 C3 A9 C3 AF 73 67 69 64 73

      However, the message that is actually received contains...

      => 52 C3 <font color="red">83 C2</font> A9 C3 <font color="red">83 C2</font> AF 73 67 69 64 73

      I did find similar events occuring in environments different from the Oracle PL/SQL environment, but couldn't tie those to what is actually happening here. Does anyone have an idea on what causes these extra bytes to appear, and/or how to prevent this?
        • 1. Re: PL/SQL webservice call produces extra bytes in utf-8
          looks like a double double to me: The sender is encoding the 2 byte characters twice...

          • 2. Re: PL/SQL webservice call produces extra bytes in utf-8
            I don't know what database version and characterset you are using, but convert probably doesn't do what you are expecting :) That's why the 11.2 documentations says "Oracle discourages the use of the CONVERT function in the current Oracle Database release."
            I suggest to use utl_http.write_raw if you need to do any characterset conversions.
            • 3. Re: PL/SQL webservice call produces extra bytes in utf-8



              We had similar problems when writing data to a service and the root-cause was that per definition the content-length must be in bytes while the length() function returns number of characters which may be different when using different character-sets and umlauts.

              There were strange errors, when the length was not correct.


              You don't need to convert the content, as write_text does the conversion.


              Maybe this helps:

                  http_req := utl_http.begin_request(url, 'POST','HTTP/1.1');

                  utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');

                  utl_http.set_header(http_req, 'Content-Length', lengthb(CONVERT(env, 'UTF8')));

                  utl_http.write_text(http_req, env);

              (our db is MSWIN1252)