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, Commented Jul 1, 2021 at 15:14
@astentx I edited, thanks for the tip! I'll do it for my future posts. Cheers — Kabulan0lak
– Kabulan0lak, Commented Jul 1, 2021 at 15:22
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, Commented Jul 1, 2021 at 15:41
@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, Commented Jul 1, 2021 at 15:43

Kabulan0lak · Accepted Answer · 2021-07-02 14:58:43Z

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.

Collectives™ on Stack Overflow

Oracle PL/SQL HTTP request response encoding issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related