oracle-databasestored-proceduresplsqlcharacter-encodingspecial-characters

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


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


Solution

  • 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.