jsonoracle-apex

Oracle APEX send JSON encoding arabic issue


I built a package to handle all the integration with the Signit API for electronic signature. The issue I have is that when I send an Arabic character in JSON.

The API response is that it is a bad request :

{"error_code":"SIGNIT-000000","error_status_code":400,"error_group":"gateway_request_error","message":""}

Here is my code:

SELECT SIGNIT_DOCUMENT_ID
INTO l_doc_id
FROM CM_ATTACHMENTS
WHERE DOCUMENT_ID = p_doc_id;

SELECT MEETING_TITLE
INTO l_meeting_title
FROM CM_MEETINGS A, CM_ATTACHMENTS B
WHERE A.MEETING_ID = B.MEETING_ID
AND DOCUMENT_ID = p_doc_id;

l_meeting_title := REPLACE(l_meeting_title, '"', '\"');  -- escape double quotes
l_meeting_title := REPLACE(l_meeting_title, CHR(10), '');  -- remove line breaks
l_meeting_title := REPLACE(l_meeting_title, CHR(13), '');
-- 2. Build JSON payload with one signer
l_payload := '{
    "document_name": "' || l_doc_id || '",
    "signature_request": {
        "title": "' || l_meeting_title || '",
        "signatories": [
            {
            "full_name": "' || p_signer_name || '",
            "order": 0,
            "verification_method": {
                "email": "' || p_signer_email || '"
                },
            "notification_method": {
                "email": "' || p_signer_email || '"
            },
            "fields": [
            {
                "position": {
                "page": '|| p_page_number ||',
                "x": 150,
                "y": 260,
                "height": 50,
                "width": 100
                },
                "properties": {
                "required": true
                },
                "placeholder": "sign here",
                "kind": "signature"
            },
            {
                "position": {
                "page": '|| p_page_number ||',
                "x": 100,
                "y": 240,
                "height": 20,
                "width": 200
                },
                "properties": {
                "required": true
                },
                "placeholder": "Your Name",
                "kind": "name",
                "appearance": {
                    "text_alignment": "center",
                    "font_family": "Helvetica",
                    "font_size": 13,
                    "color": "#416ADE",
                    "font_style": {
                    "bold": true
                    }
                }
            },
            {
                "position": {
                "page": '|| p_page_number ||',
                "x": 150,
                "y": 220,
                "height": 20,
                "width": 100
                },
                "properties": {
                "required": true,
                "read_only": true,
                "prefilled_value": "'|| to_char(sysdate, 'DD/MM/YYYY') ||'"
                },
                "placeholder": "Signature Date",
                "kind": "signature_date",
                "appearance": {
                    "text_alignment": "center",
                    "font_family": "Helvetica",
                    "font_size": 15,
                    "color": "#416ADE",
                    "font_style": {
                    "bold": true
                    }
                }
            }
            ],
            "language": "ar_SA"
        }
        ]
    }
    
}';
-- 3. Prepare HTTPS connection
UTL_HTTP.SET_WALLET('file:C:/oracle/wallets/signit', NULL);  -- Adjust wallet path as needed
l_http_req := UTL_HTTP.BEGIN_REQUEST(l_url, 'POST', 'HTTP/1.1');
-- 4. Set headers
UTL_HTTP.SET_HEADER(l_http_req, 'Authorization', 'Bearer ' || p_token);
UTL_HTTP.SET_HEADER(l_http_req, 'Content-Type', 'application/json');
UTL_HTTP.SET_HEADER(l_http_req, 'Accept', 'application/json; charset=UTF-8');
UTL_HTTP.SET_HEADER(l_http_req, 'Content-Length', DBMS_LOB.GETLENGTH(l_payload));
-- 5. Write JSON body
UTL_HTTP.WRITE_RAW(l_http_req, UTL_I18N.STRING_TO_RAW(l_payload, 'AL32UTF8'));
-- 6. Read response
l_http_resp := UTL_HTTP.GET_RESPONSE(l_http_req);
DBMS_LOB.CREATETEMPORARY(l_response, TRUE);
BEGIN
    LOOP
        UTL_HTTP.READ_TEXT(l_http_resp, l_temp, 32767);
        DBMS_LOB.WRITEAPPEND(l_response, LENGTH(l_temp), l_temp);
    END LOOP;
EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN NULL;
END;
UTL_HTTP.END_RESPONSE(l_http_resp);

This is my l_payload json output I test it in json lint and it's valid

{
    "document_name": "1fedae4b-1b65-4a9f-a150-dc7ca06371a5",
    "signature_request": {
        "title": "اجتماع فريق مهام اسرتاتيجية تطوير التطبيقات الاجتماع الأول للعام 2025 م",
        "signatories": [
            {
            "full_name": "Karam",
            "order": 0,
            "verification_method": {
                "email": "alemk@ksau-hs.edu.sa"
                },
            "notification_method": {
                "email": "alemk@ksau-hs.edu.sa"
            },
            "fields": [
            {
                "position": {
                "page": 3,
                "x": 150,
                "y": 260,
                "height": 50,
                "width": 100
                },
                "properties": {
                "required": true
                },
                "placeholder": "sign here",
                "kind": "signature"
            },
            {
                "position": {
                "page": 3,
                "x": 100,
                "y": 240,
                "height": 20,
                "width": 200
                },
                "properties": {
                "required": true
                },
                "placeholder": "Your Name",
                "kind": "name",
                "appearance": {
                    "text_alignment": "center",
                    "font_family": "Helvetica",
                    "font_size": 13,
                    "color": "#416ADE",
                    "font_style": {
                        "bold": true
                    }
                }
            },
            {
                "position": {
                "page": 3,
                "x": 150,
                "y": 220,
                "height": 20,
                "width": 100
                },
                "properties": {
                "required": true,
                "read_only": true,
                "prefilled_value": "29/07/2025"
                },
                "placeholder": "Signature Date",
                "kind": "signature_date",
                "appearance": {
                    "text_alignment": "center",
                    "font_family": "Helvetica",
                    "font_size": 15,
                    "color": "#416ADE",
                    "font_style": {
                        "bold": true
                    }
                }
            }
            ],
            "language": "ar_SA"
        }
        ]
    }
    
} 

When I send a Latin letter in the JSON, everything works fine. The issue happens when I send an Arabic character.


Solution

  • I found the answer after visiting official Oracle forum. this line is determine the length of my body. When I send an english character it's accurate but some arabic letters is more than one byte.

    the solution is to replace this line in my code:

    UTL_HTTP.SET_HEADER(l_http_req, 'Content-Length', DBMS_LOB.GETLENGTH(l_payload));
    

    with this line:

    utl_http.set_header( l_http_req, 'Content-Length', utl_raw.length( utl_i18n.string_to_raw( l_payload, 'AL32UTF8' ) ) );
    

    Thank you @Koen Lostrie for your help.