PL/SQL: UTL_HTTP POST with UTF8 string results in broken characters

Question

I want to send an UTF8 json string via utl_http to a node.js Server via POST. Sending the string

["Sant Julià de Lòria"]

does arrive as

["Sant Juliï¿½ de Lï¿½ria"]

The PL/SQL code goes like:

FUNCTION http_post_varchar(
    p_url          VARCHAR2,
    p_request_body VARCHAR2 )
  RETURN VARCHAR2
AS
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  value VARCHAR2(32767);  -- URL to post to
  v_url VARCHAR2(200) := p_url;
  -- Post Parameters
  v_param VARCHAR2(32767) := p_request_body;
  v_param_length NUMBER := length(v_param);
BEGIN
  req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
  UTL_HTTP.SET_HEADER (r      =>  req,
                       name   =>  'Content-Type',
                       value  =>  'application/json;charset=UTF-8');
  UTL_HTTP.SET_HEADER (r      =>   req,
                       name   =>   'Content-Length',
                       value  =>   v_param_length);
  UTL_HTTP.WRITE_TEXT (r      =>   req,
                       data   =>   v_param);

  resp := UTL_HTTP.GET_RESPONSE(req);
  LOOP
    UTL_HTTP.READ_LINE(resp, value, TRUE);    
  END LOOP;
  UTL_HTTP.END_RESPONSE(resp);
  RETURN 'OK';
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
  RETURN 'OK';
END http_post_varchar;

What is your database charset? What is your session nls_lang charset? — Vincent Malgrat
– Vincent Malgrat, Commented Feb 18, 2014 at 11:07
Are you sure you're reading the response as an UTF8 string? It looks as if you tried to read an UTF8 string with a 8-bit charset (ò => ï¿½) — Vincent Malgrat
– Vincent Malgrat, Commented Feb 18, 2014 at 11:15
Is it possible to achieve this with NLS_CHARACTERSET = WE8MSWIN1252 ? — sotocast
– sotocast, Commented Jun 8, 2023 at 15:44

Luscus · Accepted Answer · 2015-10-27 13:16:06Z

13

You should change your code to:

UTL_HTTP.SET_BODY_CHARSET('UTF-8');
UTL_HTTP.SET_HEADER (r      =>   req,
                     name   =>   'Content-Length',
                     value  =>    LENGTHB(v_param));

UTL_HTTP.WRITE_RAW (r    => req,
                    data => UTL_RAW.CAST_TO_RAW(v_param));

LENGTHB for byte length because of UTF-8. Otherwise the calculated length will be false and you get an error on the target side (unexpected end of input or something).

edited Oct 27, 2015 at 13:16

answered Feb 18, 2014 at 12:16

Luscus

2212 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Federico Elles Over a year ago

I made it finally work by additionally adding "utl_http.set_body_charset('UTF-8');" Attention: Once executed this setting is valid for the whole session, even if you remove it for testing, it will still work.

Luscus Over a year ago

added the line to the solution

Jeremy Caney · Accepted Answer · 2021-02-03 19:43:56Z

4

Same goes for the response body e.g. when you're getting a response from a web service:

UTL_HTTP.SET_BODY_CHARSET(r=> resp, charset=>'UTF-8');

Place this call directly after Get_response.

edited Feb 3, 2021 at 19:43

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Feb 3, 2021 at 15:58

Danny

411 bronze badge

Comments

Amir Hassan · Accepted Answer · 2021-11-15 13:28:22Z

0

Sometimes the problem is not with Unicode. You can use the conversion to ASCII code

 v_param := REPLACE (ASCIISTR (v_param), '\', '\u');

answered Nov 15, 2021 at 13:28

Amir Hassan

11 bronze badge

Collectives™ on Stack Overflow

PL/SQL: UTL_HTTP POST with UTF8 string results in broken characters

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related