Oracle APEX调用Twilio发送模板WhatsApp时Content Variables参数报错

调用APEX_WEB_SERVICE.MAKE_REST_REQUEST发送带内容模板的WhatsApp消息时，返回以下错误：

{"code":21656,"message":"The Content Variables parameter is invalid.","more_info":"https://www.twilio.com/docs/errors/21656","status":400}

原代码如下：

DECLARE
  l_url        VARCHAR2(4000) := 'https://api.twilio.com/2010-04-01/Accounts/ACXXXX/Messages.json';
  l_username   VARCHAR2(100) := 'ACXXXX';
  l_password   VARCHAR2(100) := 'edXXX';
  l_clob       CLOB;
BEGIN
  -- Send POST request using APEX_WEB_SERVICE
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url         => l_url,
    p_http_method => 'POST',
    p_username    => l_username,
    p_password    => l_password,
    p_parm_name   => APEX_UTIL.STRING_TO_TABLE('To,From,ContentSid,ContentVariables', ','),
    p_parm_value  => APEX_UTIL.STRING_TO_TABLE(
                      'whatsapp:+905554443322,whatsapp:+905553332211,HX5f23e0b8005cb01e555a3887d17,{"1":"Mustafa Turhan","2":"0d039dea3bf260bda8951e63"}', ','
                    )
  );

  -- Log response
  DBMS_OUTPUT.put_line('Response:');
  DBMS_OUTPUT.put_line(DBMS_LOB.SUBSTR(l_clob, 4000, 1));

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
END;

错误原因是APEX_UTIL.STRING_TO_TABLE按逗号分割参数值时，ContentVariables的JSON字符串内部包含逗号，导致该参数被错误拆分成多个不完整的片段，Twilio无法识别。以下两种方法可以解决：

方法1：直接构造表单格式请求体 #

手动拼接x-www-form-urlencoded格式的请求体，避免参数拆分错误：

DECLARE
  l_url        VARCHAR2(4000) := 'https://api.twilio.com/2010-04-01/Accounts/ACXXXX/Messages.json';
  l_username   VARCHAR2(100) := 'ACXXXX';
  l_password   VARCHAR2(100) := 'edXXX';
  l_clob       CLOB;
  l_body       VARCHAR2(4000);
BEGIN
  -- 构造x-www-form-urlencoded格式请求体
  l_body := 'To=whatsapp:%2B905554443322' ||
            '&From=whatsapp:%2B905553332211' ||
            '&ContentSid=HX5f23e0b8005cb01e555a3887d17' ||
            '&ContentVariables={"1":"Mustafa Turhan","2":"0d039dea3bf260bda8951e63"}';
  
  -- 发送POST请求
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url         => l_url,
    p_http_method => 'POST',
    p_username    => l_username,
    p_password    => l_password,
    p_body        => l_body,
    p_headers     => APEX_UTIL.STRING_TO_TABLE('Content-Type:application/x-www-form-urlencoded', ':')
  );

  -- 输出响应
  DBMS_OUTPUT.put_line('Response:');
  DBMS_OUTPUT.put_line(DBMS_LOB.SUBSTR(l_clob, 4000, 1));

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
END;

方法2：使用请求对象添加参数 #

通过APEX_WEB_SERVICE.T_REQUEST对象逐个添加参数，避免逗号拆分问题：

DECLARE
  l_url        VARCHAR2(4000) := 'https://api.twilio.com/2010-04-01/Accounts/ACXXXX/Messages.json';
  l_username   VARCHAR2(100) := 'ACXXXX';
  l_password   VARCHAR2(100) := 'edXXX';
  l_clob       CLOB;
  l_request    APEX_WEB_SERVICE.T_REQUEST;
BEGIN
  -- 初始化请求对象
  l_request := APEX_WEB_SERVICE.init_request;
  
  -- 逐个添加参数，不会被逗号拆分
  APEX_WEB_SERVICE.add_parameter(
    p_request => l_request,
    p_name    => 'To',
    p_value   => 'whatsapp:+905554443322'
  );
  APEX_WEB_SERVICE.add_parameter(
    p_request => l_request,
    p_name    => 'From',
    p_value   => 'whatsapp:+905553332211'
  );
  APEX_WEB_SERVICE.add_parameter(
    p_request => l_request,
    p_name    => 'ContentSid',
    p_value   => 'HX5f23e0b8005cb01e555a3887d17'
  );
  APEX_WEB_SERVICE.add_parameter(
    p_request => l_request,
    p_name    => 'ContentVariables',
    p_value   => '{"1":"Mustafa Turhan","2":"0d039dea3bf260bda8951e63"}'
  );
  
  -- 发送POST请求
  l_clob := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url         => l_url,
    p_http_method => 'POST',
    p_username    => l_username,
    p_password    => l_password,
    p_request     => l_request
  );

  -- 输出响应
  DBMS_OUTPUT.put_line('Response:');
  DBMS_OUTPUT.put_line(DBMS_LOB.SUBSTR(l_clob, 4000, 1));

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
END;

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