Oracle存储过程调用API含°符号时JSON解析失败求助

问题背景 #

我在Oracle中使用send_data_to_API存储过程向API发送payload，大部分请求可正常处理，但当comment字段包含度数符号(°)时，API返回400错误。数据库NLS_CHARACTERSET为AL32UTF8。

存储过程代码 #

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

  -- 详细日志
  DBMS_OUTPUT.PUT_LINE('Starting procedure send_data_to_API');

  -- 配置HTTPS请求的钱包
  utl_http.set_wallet('file:/local/orabin/admin/DB1/pwstore/cert', NULL);

  -- 发起第一个请求获取JWT token
  req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
  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);

  -- 从响应中提取JWT token
  BEGIN
    utl_http.read_text(res, l_jwt_token);
    str_jwt := JSON_VALUE(l_jwt_token, '$.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('开始遍历记录...');

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

    BEGIN
      -- 发起第二个请求发送事件数据
      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);

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

（注：修正了原代码中URL末尾缺失的引号、JSON_OBJECT语法错误（IS改为VALUE））

API错误响应 #

{"type":"RFC7231第6.5.1节","title":"发生一个或多个验证错误","status":400,"traceId":"|1b95f862-4e1e3bca176e298a.","errors":{"$.payload":["JSON值无法转换为System.String。路径: $.payload | 行号: 0 | 行内字节位置: 566。"]}}

触发错误的请求JSON示例 #

{"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"}"}

问题原因分析 #

1. JSON嵌套转义失效：示例payload中payload字段是嵌套JSON字符串，但内部双引号未正确转义，导致整个JSON结构非法。度数符号这类多字节字符会进一步触发API解析器的格式校验错误。
2. HTTP字符编码未明确：虽然数据库是AL32UTF8，但utl_http.write_text未明确指定编码，API端可能无法正确解码多字节字符，引发字符串转换失败。
3. 多余的REPLACE操作：对内部JSON_OBJECT执行的REPLACE(..., '"', '"')完全无效，反而可能破坏JSON的自动转义逻辑。

解决办法 #

1. 修复JSON嵌套结构，依赖Oracle自动转义 #

直接构造嵌套JSON对象，无需手动处理转义：

JSON_OBJECT(
  'eventsource' VALUE 'App',
  'eventgroup' VALUE 'App DEVICE',
  'eventname' VALUE 'App Txn',
  'payload' VALUE JSON_OBJECT(
    'id' VALUE 'App-dev-' || CODE, 
    'schema' VALUE 'sample.5.json',
    'systemRef' VALUE 'urn:system:App-dev:' || CODE,
    'createdBy' VALUE USER,
    'creationDate' VALUE CREATE_DT,
    'name' VALUE CODE,
    'State' VALUE STATUS,
    'batchRef' VALUE 'urn:batch:App-dev-' || "group",
    'comment' VALUE "comment",
    'type' VALUE 'LV'
  )
).TO_CLOB() JSON_TXT

Oracle会自动处理所有嵌套JSON的转义，生成合法结构。

2. 明确HTTP请求的字符编码 #

设置Content-Type时指定UTF-8编码，确保API端正确解析：

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

同时使用utl_http.write_raw配合编码转换函数，保证字符正确传输：

utl_http.write_raw(l_event_request, UTL_I18N.STRING_TO_RAW(content, 'AL32UTF8'));

3. 移除多余的REPLACE操作 #

删除REPLACE(JSON_OBJECT(...), '"', '"')，避免破坏JSON结构。

4. 改用CLOB存储JSON内容 #

VARCHAR2(4000)可能无法容纳长JSON，将相关变量和字段改为CLOB类型：

• 存储过程中content VARCHAR2(4000)改为content CLOB
• 查询中用.TO_CLOB()获取CLOB类型的JSON输出

修复后的核心代码片段 #

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

  BEGIN
    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; charset=UTF-8');
    utl_http.set_header(l_event_request, 'Content-Length', DBMS_LOB.GETLENGTH(content));
    utl_http.set_header(l_event_request, 'Authorization', str_jwt);

    utl_http.write_raw(l_event_request, UTL_I18N.STRING_TO_RAW(content, 'AL32UTF8'));
    l_event_response := utl_http.get_response(l_event_request);    

    utl_http.read_line(l_event_response, l_resp_buffer);                

内容的提问来源于stack exchange，提问作者user407710