Forum Stats

  • 3,875,570 Users
  • 2,266,947 Discussions
  • 7,912,257 Comments

Discussions

Unable to handle accented characters in data of webservice call using UTL_HTTP, ORA-29263,ORA-29273

User_XRS5M
User_XRS5M Member Posts: 5 Red Ribbon
edited Aug 30, 2021 8:59PM in Globalization Support

Hi,

It is observed that if the data sending in webservice call using UTL_HTTP from Oracle has accented characters(like given below), the webservice call is not getting successful from Oracle. But the same call is successful from Postman tool.

Example data:

SACRAÑMENTO101

The content-length in postman and Oracle script includes the multibyte length. Character set is UTF-8.

Most blogs like below highlighted sending the correct length and character set when multibyte characters are involved in the data, which is not working in our system.

https://community.oracle.com/tech/apps-infra/discussion/4296483/unable-to-handle-brazilian-characters-in-a-webservice-call-through-plsql?


Also, when accented characters already exists in front end data, it is received in Oracle as below (for SACRAÑMENTO101, received as SACRAÑMENTO101). In postman response, no issue observed.


Can you please suggest on how to fix the issue. Below is the script. Receiving error after running for 10-15mins at the statement l_http_response := utl_http.get_response(l_http_request);


Errors:

ORA-29273: HTTP request failed

ORA-29263: HTTP protocol error


Script:


set serveroutput on;

DECLARE

  l_http_request   utl_http.req;

  l_http_response  utl_http.resp;

  v_chars_per_chunk integer;

  v_current_chars  INTEGER;

  v_clob_offset integer;

  v_chunk_string  VARCHAR2(32767 BYTE);

  v_clob_length integer;

  v_db_charset    VARCHAR2(30 BYTE);

  l_text       VARCHAR2(32767);

  enable       BOOLEAN;

  name        VARCHAR2(1024);

  value       VARCHAR2(1024);

  v_err       VARCHAR2(4000);

  v_substring_msg  VARCHAR2(512);

  v_raw_data     RAW(512);

  v_buffer_size   NUMBER(10) := 512;

  v_string_request  VARCHAR2(4000);

  v_clob_response  CLOB;

  l_buffer      VARCHAR2(32767);

  V_XML_LENGTH NUMBER;

BEGIN

    utl_http.set_proxy('http://testproxy.app.test.com:80', 'test.com');

  utl_http.set_wallet('file://orabin/gl/oracle/product/12.1.0.2/owm/wallets/test', 'test12');

    L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => 'https://dev.testhost.com/api/suppliers/3298/', METHOD => 'PUT', HTTP_VERSION => 'HTTP/1.1');

  v_string_request := '<?xml version="1.0" encoding="UTF-8"?><supplier><supplier-addresses><supplier-address><id>6919</id><name>SACRAÑMENTO101|287021</name><street1>1515</street1><street2></street2><city>Sacramento</city><state>CA</state><postal-code>44456</postal-code><active type="boolean">true</active><country><code>US</code></country></supplier-address></supplier-addresses></supplier>' ;

  DBMS_LOB.createtemporary(v_clob_response, FALSE);  

  UTL_HTTP.SET_BODY_CHARSET('UTF-8');

  utl_http.set_header(l_http_request, 'CONTENT-TYPE', 'application/xml;charset=UTF-8');

  utl_http.set_header(l_http_request, 'Accept', 'application/xml');    

  utl_http.set_header(l_http_request, 'CONTENT-LENGTH', 369);  --length includes multibyte character length also  

  utl_http.set_header(l_http_request, 'Authorization', 'Bearer 5fa4dc54b859e22ed27773a6ff8fc67be89ebaa04a25510864d3a8ba98491bf6');

  v_raw_data := utl_raw.cast_to_raw(v_string_request);

  utl_http.write_raw(r => l_http_request, data => v_raw_data);

  --utl_http.write_text(l_http_request, v_string_request );

  dbms_output.put_line('before get_response');

  INSERT INTO TESTTAB VALUES('before get_response');

  COMMIT;

  l_http_response := utl_http.get_response(l_http_request); -- it is running forever here

  INSERT INTO TESTTAB VALUES('AFTER get_response');

  COMMIT;

  FOR i IN 1..utl_http.get_header_count(l_http_response) LOOP

    utl_http.get_header(l_http_response, i, name, value);

    dbms_output.put_line(name

               || ': '

               || value);

  END LOOP;


 -- Loop through the response.


  BEGIN

    dbms_output.put_line('Response> status_code: "'

               || l_http_response.status_code

               || '"');

    dbms_output.put_line('Response> reason_phrase: "'

               || l_http_response.reason_phrase

               || '"');

    dbms_output.put_line('Response> http_version: "'

               || l_http_response.http_version

               || '"');

    IF ( l_http_response.status_code = utl_http.http_ok ) THEN

      dbms_output.put_line('response is okay');

    ELSE

      dbms_output.put_line('RESPONSE CODE IS ' || l_http_response.status_code);

     /* BEGIN

        LOOP

          utl_http.read_line(l_http_response, l_text, true);

        DBMS_LOB.writeappend (v_clob_response, LENGTH(l_text), l_text);

        -- v_clob_response := v_clob_response||l_text;


          dbms_output.put_line('buffer-->' || l_text);

        END LOOP;


      END;*/


    END IF;


    LOOP

      utl_http.read_text(l_http_response, l_text, 32766);

       DBMS_LOB.writeappend (v_clob_response, LENGTH(l_text), l_text);

      -- v_clob_response := l_clob_response||l_text;


      dbms_output.put_line('after1');

      DBMS_OUTPUT.put_line (l_text);

      dbms_output.put_line('after2');

    END LOOP;

utl_http.end_response(l_http_response);

  EXCEPTION

    WHEN utl_http.end_of_body THEN

      dbms_output.put_line('after end_of_body');

      utl_http.end_response(l_http_response);

  END;



 --p_xml_type := XMLTYPE(v_clob_response);



 --Insert into XXSYF_COUPA_XML_RESPONSE values (XXSYF_COUPA_XML_RESPONSE_SEQ.Nextval, p_xml_type);


 commit;

 DBMS_LOB.freetemporary(v_clob_response);

EXCEPTION

  WHEN utl_http.end_of_body THEN

    utl_http.end_response(l_http_response);

    dbms_output.put_line('Exception');

     WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN

      UTL_HTTP.END_RESPONSE(l_http_response); 

  WHEN OTHERS THEN


    dbms_output.put_line('OTHERS ' || v_clob_response);

    dbms_output.put_line('EXCEPTION' || sqlerrm);

    dbms_output.put_line('v_err:' || v_err);

    dbms_output.put_line(Utl_Http.Get_Detailed_Sqlerrm);

    dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);

    dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);

    dbms_output.put_line(DBMS_UTILITY.format_call_stack);

END;

/

Tagged: