JSON with °(degree) sign / special character is not sent to API as expected

Question

I am using the above code in Oracle, to send a payload to an API. It's working for all the payload except when the comment field has a Degree sign(°).

///////////////////////////////////////////////////

create or replace PROCEDURE send_data_to_API IS
  req utl_http.req;
  l_event_request utl_http.req;
  res utl_http.resp;
  l_event_response utl_http.resp;
  url VARCHAR2(4000) := 'https://reciever-dev.acC1.awscloud.myapp.com/api/token;
  name VARCHAR2(4000);
  l_jwt_token VARCHAR2(4000);
  l_resp_buffer VARCHAR2(4000);
  str_jwt VARCHAR2(4000); 
  content VARCHAR2(4000) := '{"authKey": "aaaaaaaaa="}';
  json_obj JSON_OBJECT_T;
  response_text CLOB;
  json_response CLOB := EMPTY_CLOB();

BEGIN

  -- Detailed logging
  DBMS_OUTPUT.PUT_LINE('Starting procedure send_data_to_API');

  -- Set up wallet for HTTPS requests
  utl_http.set_wallet('file:/local/orabin/admin/DB1/pwstore/cert', NULL);

  -- Begin the first HTTP request to get JWT token
  req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
  --DBMS_OUTPUT.PUT_LINE('Initiated request for JWT token');
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', LENGTH(content));
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  --DBMS_OUTPUT.PUT_LINE('Received response for JWT token');

  -- Fetch the JWT token from the response
  BEGIN
    utl_http.read_text(res, l_jwt_token);
    str_jwt := JSON_VALUE(l_jwt_token, '$.jwt');        
    --DBMS_OUTPUT.PUT_LINE('JWT Token: ' || str_jwt);
    utl_http.end_response(res);
  EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.end_response(res);
    WHEN OTHERS THEN
      utl_http.end_response(res);
      RAISE;
  END;

  DBMS_OUTPUT.PUT_LINE('Starting to loop through the records...');

---------------------------------------
---------------Stage 1: Starts---------
  
  FOR rec IN (
    SELECT CODE, TRANSACTION_ID, 
        JSON_OBJECT(
                      'eventsource' IS 'App',
                      'eventgroup' IS 'App DEVICE',
                      'eventname' IS 'App Txn',
                      'payload' IS REPLACE(JSON_OBJECT(
                        'id' IS 'App-dev-' || CODE, 
                        'schema' IS 'sample.5.json',
                        'systemRef' IS 'urn:system:App-dev:' || CODE,
                        'createdBy' IS USER,
                        'creationDate' IS CREATE_DT,
                        'name' IS CODE,
                        'State' IS STATUS,
                        'batchRef' IS 'urn:batch:App-dev-' || "group",
                        'comment' IS "comment",
                        'type' IS 'LV'
                      ), '"', '"' )
                    ) JSON_TXT
    FROM MyTable
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Processing record: ' || rec.CODE);

    BEGIN
      -- Begin the second HTTP request to send the event data
      l_event_request := utl_http.begin_request('https://reciever-dev.acC1.awscloud.myapp.com/api/event', 'POST', 'HTTP/1.1');
      content := NVL(rec.JSON_TXT, '{}');
      utl_http.set_header(l_event_request, 'Content-Type', 'application/json');
      utl_http.set_header(l_event_request, 'Content-Length', LENGTH(content));
      utl_http.set_header(l_event_request, 'Authorization', str_jwt);

      --DBMS_OUTPUT.PUT_LINE('Sending payload for record: ' || rec.CODE);
      --DBMS_OUTPUT.PUT_LINE('Payload sent to API: ' || content);

      utl_http.write_text(l_event_request, content);
      l_event_response := utl_http.get_response(l_event_request);    

      utl_http.read_line(l_event_response, l_resp_buffer);                
      --DBMS_OUTPUT.PUT_LINE('Response from API for ' || rec.CODE || ': ' || l_resp_buffer);

///////////////////////////////////////////////////

When the request contains a Degree sign(°), the response from API is:

{"type":"https://tools.ietf.org/html/rfc7231#section-6.5.1","title":"One or more validation errors occurred.","status":400,"traceId":"|1b95f862-4e1e3bca176e298a.","errors":{"$.payload":["The JSON value could not be converted to System.String. Path: $.payload | LineNumber: 0 | BytePositionInLine: 566."]}}

The json that is being sent to the API which results in error is :

{"eventsource":"App","eventgroup":"App DEVICE","eventname":"App Txn","payload":"{"id":"App-dev-0009_434-06545_1_0654","schema":"sample.5.json","systemRef":"urn:system:App-dev:0009_434-06545_1_0654","createdBy":"User_ABC","creationDate":"2024-05-24T11:19:11","name":"0009_434-06545_1_0654","State":"stored","batchRef":"urn:batch:App-dev:-G_0009_434-06545_1_0654","comment":" K1 [5°C]","type":"LV"}"}

NLS_CHARACTERSET in the database is AL32UTF8

Answer 1

The JSON you are generating is invalid:

{"eventsource":"App","eventgroup":"App DEVICE","eventname":"App Txn","payload":"{"id":"App-dev-0009_434-06545_1_0654","schema":"sample.5.json","systemRef":"urn:system:App-dev:0009_434-06545_1_0654","createdBy":"User_ABC","creationDate":"2024-05-24T11:19:11","name":"0009_434-06545_1_0654","State":"stored","batchRef":"urn:batch:App-dev:-G_0009_434-06545_1_0654","comment":" K1 [5°C]","type":"LV"}"}

You have "payload":"{"id":...}"} and payload should either be:

• An object "payload":{"id":...}}; or
• A properly escaped string "payload":"{\"id\":...}"}

As well as @AlexPoole's answer, you can remove the REPLACE and try encoding the JSON as ASCII and escaping any multi-byte characters using JSON_SERIALIZE with the ASCII option:

SELECT CODE,
       TRANSACTION_ID, 
       JSON_SERIALIZE(
         JSON_OBJECT(
           'eventsource' IS 'App',
           'eventgroup'  IS 'App DEVICE',
           'eventname'   IS 'App Txn',
           'payload'     IS JSON_OBJECT(
             'id'           IS 'App-dev-' || CODE, 
             'schema'       IS 'sample.5.json',
             'systemRef'    IS 'urn:system:App-dev:' || CODE,
             'createdBy'    IS USER,
             'creationDate' IS CREATE_DT,
             'name'         IS CODE,
             'State'        IS STATUS,
             'batchRef'     IS 'urn:batch:App-dev-' || "group",
             'comment'      IS "comment",
             'type'         IS 'LV'
           )
         )
         RETURNING CLOB ASCII
       ) JSON_TXT
FROM   MyTable

Which, for the sample data:

create table MyTable (code, transaction_id, create_dt, status, "group", "comment") AS
SELECT '0009_434-06545_1_0654',
       42,
       DATE '2024-05-24' + INTERVAL '11:19:11' HOUR TO SECOND,
       'stored',
       'G_0009_434-06545_1_0654',
       ' K1 [5°C]'
FROM   DUAL

Outputs:

CODE	TRANSACTION_ID	JSON_TXT
0009_434-06545_1_0654	42	{"eventsource":"App","eventgroup":"App DEVICE","eventname":"App Txn","payload":{"id":"App-dev-0009_434-06545_1_0654","schema":"sample.5.json","systemRef":"urn:system:App-dev:0009_434-06545_1_0654","createdBy":"FIDDLE_IZJVHCTNTLZQKTJJEVEV","creationDate":"2024-05-24T11:19:11","name":"0009_434-06545_1_0654","State":"stored","batchRef":"urn:batch:App-dev-G_0009_434-06545_1_0654","comment":" K1 [5\u00B0C]","type":"LV"}}

Encoding the comment as K1 \[5\u00B0C].

fiddle

Answer 2

You are sending multibyte characters - or one of them, in this case - and the content length should be the number of bytes, not characters; so instead of:

      utl_http.set_header(l_event_request, 'Content-Length', LENGTH(content));

use

      utl_http.set_header(l_event_request, 'Content-Length', LENGTHB(content));

It should be redundant but you could also specify the character set in the request, with set_body_charset():

    utl_http.set_body_charset('UTF-8');

or

      utl_http.set_header(l_event_request, 'Content-Type', 'application/json;charset=utf-8');

though there is not officially a charset parameter.