如何在PL/SQL存储过程中使用UTL_HTTP包调用HTTPS API？

问题背景 #

已实现HTTP服务调用，但HTTPS请求失败，现有代码及ACL配置如下：

现有PL/SQL代码 #

CREATE OR REPLACE PROCEDURE call_api (
  p_name IN VARCHAR2,
  p_age IN NUMBER
) IS
  req utl_http.req;
  res utl_http.resp;
  url VARCHAR2(4000) := 'https://qh5rz7xna7.execute-api.ap-south-1.amazonaws.com/sample';
  buffer VARCHAR2(4000);
  content VARCHAR2(4000) := '{"name":"' || p_name || '","age":' || p_age || '}';
BEGIN
  req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
  utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  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);

  -- 处理HTTP响应
  BEGIN
    LOOP
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    END LOOP;
    utl_http.end_response(res);
  EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.end_response(res);
  END;
END call_api;
/

SET SERVEROUTPUT ON;

BEGIN
  call_api('john', 20);
END;
/

现有ACL配置 #

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'local_sx_acl_file.xml', 
    description  => 'ACL功能测试',
    principal    => 'AMILA',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
END;
/
 
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'local_sx_acl_file.xml',
    host        => '*', 
    lower_port  => NULL,
    upper_port  => NULL);    
END; 
/

必需配置步骤 #

1. 创建并配置Oracle Wallet（HTTPS核心） #

HTTPS依赖SSL证书验证，必须创建Oracle Wallet存储信任证书：

• 生成Wallet：使用orapki工具创建自动登录Wallet

  orapki wallet create -wallet /u01/app/oracle/wallet -pwd YourStrongWalletPass123 -auto_login
  

• 导入目标API的根证书：下载目标域名（如AWS API Gateway）的CA根证书，导入到Wallet

  orapki wallet add -wallet /u01/app/oracle/wallet -trusted_cert -cert /path/to/aws_root_cert.crt -pwd YourStrongWalletPass123
  

• 验证Wallet内容：确认证书已成功导入

  orapki wallet display -wallet /u01/app/oracle/wallet
  

2. 补充权限配置 #

• 确保用户拥有UTL_HTTP执行权限：

  GRANT EXECUTE ON UTL_HTTP TO AMILA;
  

修正后的PL/SQL存储过程 #

加入Wallet配置，优化JSON生成方式（避免拼接错误），增加异常处理：

CREATE OR REPLACE PROCEDURE call_api (
  p_name IN VARCHAR2,
  p_age IN NUMBER
) IS
  req utl_http.req;
  res utl_http.resp;
  url VARCHAR2(4000) := 'https://qh5rz7xna7.execute-api.ap-south-1.amazonaws.com/sample';
  buffer VARCHAR2(4000);
  -- 使用JSON_OBJECT安全生成JSON，避免字符串拼接漏洞
  content CLOB := JSON_OBJECT('name' VALUE p_name, 'age' VALUE p_age);
BEGIN
  -- 配置Wallet，必须在begin_request前执行
  utl_http.set_wallet('file:/u01/app/oracle/wallet', 'YourStrongWalletPass123');
  
  req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');
  utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(req, 'content-type', 'application/json'); 
  -- 自动处理Content-Length，可省略手动设置
  utl_http.set_header(req, 'Content-Length', DBMS_LOB.GETLENGTH(content));
 
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);

  -- 处理响应
  BEGIN
    LOOP
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    END LOOP;
    utl_http.end_response(res);
  EXCEPTION
    WHEN utl_http.end_of_body THEN
      utl_http.end_response(res);
  END;
EXCEPTION
  WHEN OTHERS THEN
    -- 捕获异常并输出错误信息，便于排错
    dbms_output.put_line('错误详情: ' || SQLERRM);
    -- 确保资源释放
    IF utl_http.is_open(req) THEN
      utl_http.end_request(req);
    END IF;
    IF utl_http.is_open(res) THEN
      utl_http.end_response(res);
    END IF;
    RAISE;
END call_api;
/

SET SERVEROUTPUT ON;

BEGIN
  call_api('john', 20);
END;
/

排错指南 #

• 证书验证失败（ORA-29024）：检查Wallet中是否导入了目标API的根证书，确认证书未过期
• ACL权限错误（ORA-24247）：查询ACL分配情况，确保用户AMILA有对应host的connect权限

  SELECT host, acl FROM dba_network_acls;
  SELECT principal, privilege FROM dba_network_acl_privileges WHERE acl = 'local_sx_acl_file.xml';
  

• Wallet路径错误：确认Wallet路径正确，Oracle用户对路径有读权限
• 网络连通性问题：在数据库服务器上执行curl https://qh5rz7xna7.execute-api.ap-south-1.amazonaws.com/sample测试网络是否可达
• UTL_HTTP权限缺失：确认已执行GRANT EXECUTE ON UTL_HTTP TO AMILA;

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