Oracle PL/SQL HTTP request response encoding issue

Question

Following my previous question, I don't seem to be able to convert a HTTP response from ISO-8859-1 to UTF-8.

I am using APEX_WEB_SERVICE package to to my requests. (I know this package uses UTL_HTTP itself, so it should be similar in usage)

What I do:

apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'text/csv';

l_response := apex_web_service.make_rest_request(
    p_url            => MY_URL || '/download_csv',
    p_http_method    => 'GET'
);

l_response contains the csv data but all 'é' and 'è' are replaced by '¿':

Type;Groupe Acc¿Code;EOTP autoris¿Familles EOTP autoris¿;Nom;Pr¿m;Adresse

Whereas if I access the link directly, my browser downloads it with proper encoding:

Type;Groupe Accès;Code;EOTP autorisés;Familles EOTP autorisées;Nom;Prénom;Adresse

I tried to convert the response with:

l_response := convert(l_response, 'AL16UTF16', 'WE8ISO8859P1');

But it has absolutely no effect.

The website is https://www.stocknet.fr/ and is in ISO-8859-1.

My Oracle NLS parameters (which I obviously can't modify):

+-------------------------+-----------------------------+
|        PARAMETER        |            VALUE            |
+-------------------------+-----------------------------+
| NLS_LANGUAGE            | ENGLISH                     |
| NLS_TERRITORY           | UNITED KINGDOM              |
| NLS_CURRENCY            | £                           |
| NLS_ISO_CURRENCY        | UNITED KINGDOM              |
| NLS_NUMERIC_CHARACTERS  | .,                          |
| NLS_CALENDAR            | GREGORIAN                   |
| NLS_DATE_FORMAT         | DD-MON-RR HH24:MI           |
| NLS_DATE_LANGUAGE       | ENGLISH                     |
| NLS_CHARACTERSET        | WE8MSWIN1252                |
| NLS_SORT                | BINARY                      |
| NLS_TIME_FORMAT         | HH24.MI.SSXFF               |
| NLS_TIMESTAMP_FORMAT    | DD-MON-RR HH24.MI.SSXFF     |
| NLS_TIME_TZ_FORMAT      | HH24.MI.SSXFF TZR           |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH24.MI.SSXFF TZR |
| NLS_DUAL_CURRENCY       | €                           |
| NLS_NCHAR_CHARACTERSET  | AL16UTF16                   |
| NLS_COMP                | BINARY                      |
| NLS_LENGTH_SEMANTICS    | BYTE                        |
| NLS_NCHAR_CONV_EXCP     | FALSE                       |
+-------------------------+-----------------------------+

At this point, I don't know if there is any way to process data from this website from plsql. Any help, tips or suggestion would be appreciated.

Please, do not post images of code or data, paste the data in text format. You may consider ASCII table generator for this or read about tabular format with markdown, which is available in help section of question/answer input window — astentx
@astentx I edited, thanks for the tip! I'll do it for my future posts. Cheers — Kabulan0lak
Do you get anything different if you try specifying a charset in the Content-Type, e.g. 'text/csv; charset=ISO-8859-1' or 'text/csv; charset=UTF-8' — kfinity
@kfinity No I specified in my previous question. Whatever I put as header I get the same result. Somebody answered it's normal cause the server gives ISO-8859-1 no matter what — Kabulan0lak

Kabulan0lak Kabulan0lak · Accepted Answer · 2021-07-02T14:58:43

Turns out I had to use UTL_HTTP and DBMS_LOB in order for Oracle to parse correctly the characters.

This solved my issue:

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);
    l_http_request := utl_http.begin_request(my_url || '/download_csv');
    l_http_response := utl_http.get_response(l_http_request);
    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;

    dbms_output.put_line(l_clob); /* => ENCODING IS FINALLY GOOD ! */
    dbms_lob.freetemporary(l_blob);
EXCEPTION
    WHEN OTHERS THEN
        utl_http.end_response(l_http_response);
        dbms_lob.freetemporary(l_blob);
        RAISE;
END;
/

I hope this could help someone else.

Oracle PL/SQL HTTP request response encoding issue

1 Answers