Consuming rest API from Oracle PLSQL is returning ??? for arabic characters

Question

I am trying to call a restful API from my Oracle procedure. First, the API method is of type get and not post, so parameters are sent through header. the main purpose of the API is to send the received message as SMS to some providers and sometimes they are in Arabic format; We realized that Arabic received SMS are incomprehensible;

So I created a test procedure that takes a message and sends it to a test API method that returns the same message as response.

The API call succeeded but the response, only when arabic format is used, looks like ����. What should be added to my procedure so messages can be readable? I have tried to use escape for the message and to set the header format as you can see in below template, but unfortunately nothing succeeded:

PROCEDURE TEST(lang       VARCHAR2, 
               message    VARCHAR2,
               P_RESPONSE OUT VARCHAR2) AS

    v_request         UTL_HTTP.req;
    v_response        UTL_HTTP.resp;
    v_text            VARCHAR2(1024);
    v_url             VARCHAR2(1024);
    v_message         VARCHAR2(1024);
    l_webservice_link VARCHAR2(128); 


  BEGIN
    BEGIN

      P_RESPONSE := '';
     v_message         := utl_url.escape(message);
      --v_message         :=utl_url.escape(message,false,'UTF-8');
      --v_message         :=utl_url.escape(message,false,'windows-1256');
      --v_message         :=utl_url.escape(message,false,'AL32UTF8');
      --v_message         :=utl_url.escape(message,false,'AR8MSWIN1256');

      l_webservice_link := GET_PARAM('REST_API_URL');

      v_url             := l_webservice_link || 
                           'Mytest?strMessage=' || v_message|| 
                           '&strLang=' || lang;

      v_request         := UTL_HTTP.begin_request(v_url);

      --UTL_HTTP.set_header(v_request, 'Content-Type', 'charset=UTF-8');
      --UTL_HTTP.set_header(v_request, 'Content-Type', 'windows-1256');

      v_response := UTL_HTTP.get_response(v_request);

      LOOP
        BEGIN
          UTL_HTTP.read_text(v_response, v_text);
          DBMS_OUTPUT.put_line(v_text);

        EXCEPTION
          WHEN UTL_HTTP.end_of_body THEN
            NULL;
        END;

        EXIT WHEN v_text IS NULL;
      END LOOP;

      UTL_HTTP.end_response(v_response);

      IF v_response.status_code <> 200 THEN
       P_RESPONSE := v_response.reason_phrase; 
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        P_RESPONSE := 'An error has occured: ' || SQLERRM;        
    END;

  END TEST;

Any help is more than appreciated.

Answer 1

Try to insert this code:

Charset VARCHAR2(20);

BEGIN

    SELECT UTL_I18N.MAP_CHARSET(VALUE)
    INTO Charset
    FROM nls_database_parameters
    WHERE parameter = 'NLS_CHARACTERSET';

UTL_HTTP.set_header(v_request, 'Content-Type', 'text/html; charset='||Charset);

I am not familiar with REST, I don't know wether text/html; is required and correct.

Update

I just see your database character set it AR8ASMO8X which does not have any IANA name (at least not according to Oracle UTL_I18N.MAP_CHARSET)

In this case try

UTL_HTTP.set_header(v_request, 'Content-Type', 'text/html; charset=UTF-8');
UTL_HTTP.begin_request(CONVERT(v_url,'AL32UTF8'));

Most likely the server returns response in UTF-8 - would be the most common one, otherwise check the header of the response.

Then try this:

UTL_HTTP.SET_BODY_CHARSET(v_response, 'AL32UTF8');

Apart from all above you may have also a display issue, i.e. inside Oracle everything would be fine, just your client is not able to display the characters properly, see OdbcConnection returning Chinese Characters as "?"