如何在PL/SQL中向Syslog代理发送消息？

要实现Unix命令logger -p local1.info "hello word"的等效功能，确实可以通过UTL_TCP（或UTL_UDP）结合Oracle网络ACL来完成，以下是具体步骤：

1. 配置Oracle网络ACL #

首先需要授予执行PL/SQL的用户连接到Syslog服务器的权限，以Oracle 12c+为例：

BEGIN
  -- 创建ACL文件
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl          => 'syslog_access.xml',
    description  => '授权访问Syslog服务器',
    principal    => 'YOUR_ORACLE_USER', -- 替换为实际执行用户
    is_grant     => TRUE,
    privilege    => 'connect'
  );

  -- 将ACL绑定到Syslog服务器地址和端口
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl          => 'syslog_access.xml',
    host         => 'localhost', -- 替换为Syslog服务器地址
    lower_port   => 514, -- Syslog默认TCP端口
    upper_port   => 514
  );

  COMMIT;
END;
/

验证ACL是否生效：

SELECT host, lower_port, upper_port, acl
FROM dba_network_acls
WHERE host = 'localhost';

同时需要给用户授予UTL_TCP的执行权限：

GRANT EXECUTE ON UTL_TCP TO YOUR_ORACLE_USER;

2. 编写PL/SQL发送Syslog消息的过程 #

以下过程会构造符合传统Syslog格式的消息，并通过TCP发送到Syslog服务器：

CREATE OR REPLACE PROCEDURE send_to_syslog(
  p_facility    IN VARCHAR2 := 'local1',
  p_severity    IN VARCHAR2 := 'info',
  p_message     IN VARCHAR2,
  p_syslog_host IN VARCHAR2 := 'localhost',
  p_syslog_port IN NUMBER := 514
) AS
  l_socket      UTL_TCP.connection;
  l_priority    NUMBER;
  l_facility_num NUMBER;
  l_severity_num NUMBER;
  l_timestamp   VARCHAR2(30);
  l_hostname    VARCHAR2(100);
  l_syslog_msg  VARCHAR2(1000);
BEGIN
  -- 映射Facility到Syslog数值
  CASE p_facility
    WHEN 'local0' THEN l_facility_num := 16;
    WHEN 'local1' THEN l_facility_num := 17;
    WHEN 'local2' THEN l_facility_num := 18;
    WHEN 'local3' THEN l_facility_num := 19;
    WHEN 'local4' THEN l_facility_num := 20;
    WHEN 'local5' THEN l_facility_num := 21;
    WHEN 'local6' THEN l_facility_num := 22;
    WHEN 'local7' THEN l_facility_num := 23;
    ELSE l_facility_num := 1; -- 默认User Facility
  END CASE;

  -- 映射Severity到Syslog数值
  CASE p_severity
    WHEN 'emerg' THEN l_severity_num := 0;
    WHEN 'alert' THEN l_severity_num := 1;
    WHEN 'crit' THEN l_severity_num := 2;
    WHEN 'err' THEN l_severity_num := 3;
    WHEN 'warning' THEN l_severity_num := 4;
    WHEN 'notice' THEN l_severity_num := 5;
    WHEN 'info' THEN l_severity_num := 6;
    WHEN 'debug' THEN l_severity_num := 7;
    ELSE l_severity_num := 6; -- 默认Info级别
  END CASE;

  -- 计算Syslog优先级（Facility*8 + Severity）
  l_priority := l_facility_num * 8 + l_severity_num;

  -- 生成符合Syslog格式的时间戳（MMM dd HH:mm:ss）
  l_timestamp := TO_CHAR(SYSTIMESTAMP, 'MON DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=AMERICAN');
  -- 补全日期单数字为两位（如Jan  1而非Jan 1）
  l_timestamp := REGEXP_REPLACE(l_timestamp, '(\w{3}) (\d{1}) ', '\1  \2 ');

  -- 获取数据库主机名
  SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') INTO l_hostname FROM DUAL;

  -- 构造完整Syslog消息
  l_syslog_msg := '<' || l_priority || '>' || l_timestamp || ' ' || l_hostname || ' oracle[' || DBMS_UTILITY.get_pid || ']: ' || p_message;

  -- 连接并发送消息
  l_socket := UTL_TCP.open_connection(p_syslog_host, p_syslog_port);
  UTL_TCP.write_text(l_socket, l_syslog_msg || CHR(10)); -- 以换行符结束消息
  UTL_TCP.close_connection(l_socket);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_TCP.is_open(l_socket) THEN
      UTL_TCP.close_connection(l_socket);
    END IF;
    RAISE; -- 可根据需求自定义异常处理逻辑
END;
/

3. 调用过程实现目标功能 #

执行以下PL/SQL块，即可等效于logger -p local1.info "hello word"命令：

BEGIN
  send_to_syslog(
    p_facility => 'local1',
    p_severity => 'info',
    p_message => 'hello word'
  );
END;
/

可选：UDP协议版本 #

如果Syslog服务器使用UDP协议，可改用UTL_UDP包实现，过程如下：

CREATE OR REPLACE PROCEDURE send_to_syslog_udp(
  p_facility    IN VARCHAR2 := 'local1',
  p_severity    IN VARCHAR2 := 'info',
  p_message     IN VARCHAR2,
  p_syslog_host IN VARCHAR2 := 'localhost',
  p_syslog_port IN NUMBER := 514
) AS
  l_priority    NUMBER;
  l_facility_num NUMBER;
  l_severity_num NUMBER;
  l_timestamp   VARCHAR2(30);
  l_hostname    VARCHAR2(100);
  l_syslog_msg  VARCHAR2(1000);
BEGIN
  -- 重复相同的优先级计算和消息构造逻辑
  CASE p_facility
    WHEN 'local0' THEN l_facility_num := 16;
    WHEN 'local1' THEN l_facility_num := 17;
    WHEN 'local2' THEN l_facility_num := 18;
    WHEN 'local3' THEN l_facility_num := 19;
    WHEN 'local4' THEN l_facility_num := 20;
    WHEN 'local5' THEN l_facility_num := 21;
    WHEN 'local6' THEN l_facility_num := 22;
    WHEN 'local7' THEN l_facility_num := 23;
    ELSE l_facility_num := 1;
  END CASE;

  CASE p_severity
    WHEN 'emerg' THEN l_severity_num := 0;
    WHEN 'alert' THEN l_severity_num := 1;
    WHEN 'crit' THEN l_severity_num := 2;
    WHEN 'err' THEN l_severity_num := 3;
    WHEN 'warning' THEN l_severity_num := 4;
    WHEN 'notice' THEN l_severity_num := 5;
    WHEN 'info' THEN l_severity_num := 6;
    WHEN 'debug' THEN l_severity_num := 7;
    ELSE l_severity_num := 6;
  END CASE;

  l_priority := l_facility_num * 8 + l_severity_num;
  l_timestamp := TO_CHAR(SYSTIMESTAMP, 'MON DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=AMERICAN');
  l_timestamp := REGEXP_REPLACE(l_timestamp, '(\w{3}) (\d{1}) ', '\1  \2 ');
  SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST') INTO l_hostname FROM DUAL;
  l_syslog_msg := '<' || l_priority || '>' || l_timestamp || ' ' || l_hostname || ' oracle[' || DBMS_UTILITY.get_pid || ']: ' || p_message;

  -- 通过UDP发送消息
  UTL_UDP.send(
    remote_host => p_syslog_host,
    remote_port => p_syslog_port,
    data => UTL_RAW.cast_to_raw(l_syslog_msg)
  );
END;
/

使用UDP版本时，需先授予用户UTL_UDP权限：

GRANT EXECUTE ON UTL_UDP TO YOUR_ORACLE_USER;

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