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.

Answer 1

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.