UTF-8 Characters get corrupted in HTTP response pl/sql

Question

I'm using the following code to read HTTP reponse from Mongo DB to Oracle table. Some parts of the source (json-like) is in Chinese are recieved corrupted. The line DBMS_OUTPUT.PUT_LINE(buf); displays these corrupted values.

FUNCTION FN_READ_CONTACTS_MOB (p_id in number) RETURN NUMBER
IS
OracleBatchNumber number := p_id;
buf NVARCHAR2(32767);
  l_param_list     VARCHAR2(512);

  l_http_request   UTL_HTTP.req;
  l_http_response  UTL_HTTP.resp;

  l_response_text CLOB;


BEGIN
 DBMS_OUTPUT.ENABLE(1000000);
  -- service's input parameters
UTL_HTTP.SET_WALLET('file:C:/app/', 'manager1');
  -- preparing Request...
  l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Outlet_Details?where=%7B%22%24and%22%3A%5B%7B%22Oracle_Flag%22%3A+%22Y%22%7D%2C+%7B%22OracleBatchNo%22%3A+%22'||OracleBatchNumber||'%22%7D%5D%7D'
                                          , 'GET'
                                          , 'HTTP/1.1');




  -- ...set header's attributes
  UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
  --UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_param_list));

  -- ...set input parameters
 -- UTL_HTTP.write_text(l_http_request, l_param_list);

  -- get Response and obtain received value
  l_http_response := UTL_HTTP.get_response(l_http_request);

--using a loop read teh response, as UTL_HTTP.read_text hat returns the result as a VARCHAR2 (max 32767) (you have an implicit conversion here).
    BEGIN
      LOOP
        UTL_HTTP.read_text(l_http_response, buf);
        DBMS_OUTPUT.PUT_LINE(buf);
        l_response_text := l_response_text || buf;
      END LOOP;
    EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      NULL;
    END;

 .....

When I run the following:

select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

and it returns : AL32UTF8.

does not mean my DB is configured for UTF-8? or there are other things need to be checked for this pupose?

I know it's a while ago, but did you find any solution to this issue ? I have something very similar: stackoverflow.com/questions/68211873/… — Kabulan0lak
I found the solution and posted it as a (very) late answer. It works :) — Kabulan0lak

Kabulan0lak Kabulan0lak · Accepted Answer · 2021-07-07T13:27:21

A bit late to the party... The solution is to "nest" the data with dbms_lob so Oracle can convert it back properly:

DECLARE
    l_clob            CLOB;
    l_http_request    utl_http.req;
    l_http_response   utl_http.resp;
    l_text            VARCHAR2(32767);
BEGIN
    dbms_lob.createtemporary(l_clob, false);
    /* ... */

    BEGIN
        LOOP
            utl_http.read_text(l_http_response, l_text, 32766);
            dbms_lob.writeappend(l_clob, length(l_text), l_text);
        END LOOP;
    EXCEPTION
        WHEN utl_http.end_of_body THEN
            utl_http.end_response(l_http_response);
    END;

    /* l_clob contains your data now correctly encoded */

    dbms_lob.freetemporary(l_blob);
EXCEPTION
    WHEN OTHERS THEN
        utl_http.end_response(l_http_response);
        dbms_lob.freetemporary(l_blob);
        RAISE;
END;
/

Hope this could help someone else. Got the idea from Donald Burleson's article.

UTF-8 Characters get corrupted in HTTP response pl/sql

2 Answers