SQL & PL/SQL

Hi All,

I am a newbie in using UTL_HTTP.

I am trying to send a request using UTL_HTTP.

However, when the specific data is received at the destination , special characters of the text change to Junk.

I tried going through different forums and following their suggestions, but nothing helped.

Example : Åtril -- changes to --  ?tril

I have tried changing the Characterset to UTF-8 as the destination needs it in UTF-8, but issue still exists.

I have tried using

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

      utl_http.set_header(l_req, 'Content-Length', lengthb(convert(l_soap_request,'UTF8')));  

as well . But, in this case SMS itself is not triggered and there is error

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1367

.  

Below is my request and code ... Any suggestion are appreciated.

What am I doing wrong..

XML Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 

xmlns:test="http://xmlns.test.de/bss/testService"

xmlns:test1="http://xmlns.test.de/bss/test">

<soapenv:Header/>

  <soapenv:Body>

  <test:createBatch>

  <test:messageName>USR3R002</test:messageName>

  <test:userName>USR3R</test:userName>

  <test:globalParameters>

  <test1:parameter>

  <test1:name>store_name</test1:name>

  <test1:value>Åtril Store</test1:value>

  </test1:parameter>

  <test1:parameter>

  <test1:name>store_number</test1:name>

  <test1:value>456678905</test1:value>

  </test1:parameter>

  </test:globalParameters>

  <test:receivers>

  <test1:receiver>

  <test1:receiverNr>4511111111</test1:receiverNr>

  <test1:receiverType>M</test1:receiverType>

  </test1:receiver>

  </test:receivers>

  </test:createBatch>

  </soapenv:Body>

</soapenv:Envelope>

----------------------

Database Parameters

----------------------

Code

FUNCTION xml_request(p_request_xml IN VARCHAR2, l_who IN VARCHAR2)

    RETURN xmltype IS

    l_req   utl_http.req;

    l_resp  utl_http.resp;

    l_xml   CLOB;

    l_value VARCHAR2(1024) := '';

    l_soap_request   VARCHAR2(10000);

    utl1             VARCHAR2(200) := fnd_profile.VALUE('XX_SERVLET_URL');

    l_sms_res_status VARCHAR2(10);

  BEGIN

    IF l_who = 'XX' THEN

      l_soap_request := lv_soap_header || p_request_xml || lv_soap_footer;

      fnd_file.put_line(fnd_file.output,

                        'l_soap_request ' || l_soap_request);

      dbms_output.put_line('XML Passed');

      dbms_output.put_line(l_soap_request);

      dbms_output.put_line(utl1);

      IF utl1 IS NULL THEN

        dbms_output.put_line('no value');

      END IF;

      utl_http.set_body_charset( 'UTF-8');  -- this doesnot help either

      l_req := utl_http.begin_request(fnd_profile.VALUE('XX_SERVLET_URL'),

                                      'POST',

                                      'HTTP/1.1');

      utl_http.set_transfer_timeout(l_req,

                                    nvl(fnd_profile.VALUE('XX_SERVLET_TIMEOUT'),

                                        300));

      utl_http.set_header(l_req, 'Content-Type', 'text/xml');

  utl_http.set_header(l_req, 'Content-Length', length(l_soap_request));

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

      --utl_http.set_header(l_req, 'Content-Length', lengthb(convert(l_soap_request,'UTF8')));  -- No SMS triggered at all

  utl_http.set_header(l_req, 'SOAPAction', '');

      utl_http.write_text(l_req, l_soap_request);

    END IF;

      l_resp := utl_http.get_response(l_req);

    BEGIN

      utl_http.read_text(l_resp, l_value, 1000);  

      l_xml := l_value;

      fnd_file.put_line(fnd_file.output, 'XML Response ' || l_value);

      SELECT extractvalue(xmltype(l_xml),

                          '//res:status',

                          'xmlns:res="http://xmlns.test.de/common" xmlns:test="http://xmlns.test.de/bss/test"')

        INTO l_sms_res_status

        FROM dual;

      LOOP

        utl_http.read_text(l_resp, l_value, 1000);

        dbms_lob.writeappend(l_xml, length(l_value), l_value);

      END LOOP;

    EXCEPTION

      WHEN utl_http.end_of_body THEN

        utl_http.end_response(l_resp);

    END;

    RETURN xmltype(l_xml);

  EXCEPTION

    WHEN OTHERS THEN

      fnd_file.put_line(fnd_file.output,

                        'XML request ' || l_who || ' failed :' || SQLERRM);

      fnd_file.put_line(fnd_file.output, utl_http.get_detailed_sqlerrm);

      RAISE;

  END xml_request;