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
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');