Oracle：如何在PL/SQL异常中记录含变量值的静态SQL语句

要在静态DML执行报错时记录包含PL/SQL变量实际值的SQL语句，你可以通过结合数据字典视图获取绑定变量值，并替换SQL文本中的占位符来实现，无需使用动态SQL或追踪注释。

核心思路 #

Oracle执行静态PL/SQL中的SQL时，会将PL/SQL变量转换为绑定变量（如:B1），这些绑定变量的实际值会被捕获到V$SQL_BIND_CAPTURE视图中。我们可以：

1. 获取当前会话执行的最后一条SQL文本（带绑定占位符）
2. 从V$SQL_BIND_CAPTURE中提取对应绑定变量的实际值
3. 将占位符替换为实际值，生成包含变量值的完整SQL语句

实现代码 #

1. 升级SQL_UTIL包，添加带绑定变量替换的函数 #

CREATE OR REPLACE PACKAGE SQL_UTIL as 
   FUNCTION GET_LAST_SQLTEXT_WITH_BINDS RETURN CLOB;
end SQL_UTIL;
/

CREATE OR REPLACE PACKAGE BODY SQL_UTIL
AS
   FUNCTION GET_LAST_SQLTEXT_WITH_BINDS
      RETURN CLOB
   IS
      l_sql_text     CLOB;
      l_sid          NUMBER := SYS_CONTEXT('USERENV', 'SID');
      l_prev_sql_id  VARCHAR2(13);
      l_child_number NUMBER;
      
      TYPE bind_rec IS RECORD (
          position  NUMBER,
          value     VARCHAR2(4000),
          datatype  NUMBER
      );
      TYPE bind_tab IS TABLE OF bind_rec;
      l_binds        bind_tab;
      l_bind_value   VARCHAR2(4001);
      
      -- 数据类型常量，参考Oracle官方文档
      c_varchar2_type CONSTANT NUMBER := 1;
      c_date_type     CONSTANT NUMBER := 12;
      c_char_type     CONSTANT NUMBER := 96;
      
   BEGIN
      -- 获取会话的上一个SQL ID和子游标编号
      SELECT prev_sql_id, prev_child_number
      INTO l_prev_sql_id, l_child_number
      FROM v$session
      WHERE sid = l_sid;

      -- 获取原始SQL文本（带绑定占位符）
      SELECT sql_fulltext
      INTO l_sql_text
      FROM v$sql
      WHERE sql_id = l_prev_sql_id
        AND child_number = l_child_number;

      -- 获取绑定变量的位置、值和数据类型
      SELECT position, value_string, datatype
      BULK COLLECT INTO l_binds
      FROM v$sql_bind_capture
      WHERE sql_id = l_prev_sql_id
        AND child_number = l_child_number
        AND value_string IS NOT NULL
      ORDER BY position;

      -- 替换绑定占位符为实际值
      FOR i IN 1..l_binds.COUNT LOOP
          l_bind_value := l_binds(i).value;
          
          -- 根据数据类型处理值的格式：字符/日期类型加单引号并转义内部单引号
          IF l_binds(i).datatype IN (c_varchar2_type, c_date_type, c_char_type) THEN
              l_bind_value := '''' || REPLACE(l_bind_value, '''', '''''') || '''';
          END IF;
          
          l_sql_text := REPLACE(l_sql_text, ':B' || l_binds(i).position, l_bind_value);
      END LOOP;

      RETURN l_sql_text;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
          RETURN '未找到对应SQL或绑定变量';
      WHEN OTHERS THEN
          RETURN '获取SQL失败: ' || SQLERRM;
   END;
END SQL_UTIL;
/

2. 在存储过程异常块中调用函数记录日志 #

CREATE OR REPLACE PROCEDURE execute_insert IS 
     l_parameter VARCHAR2(100);
BEGIN
     l_parameter := '5';
     
     INSERT INTO Destination_Table(id)
     SELECT id
       FROM Source_Table st
     WHERE 1=1
       AND st.id = l_parameter;
EXCEPTION
    WHEN OTHERS THEN
         -- 将错误信息和带变量值的SQL写入日志表
         INSERT INTO error_log (error_timestamp, error_msg, full_sql_text)
         VALUES (SYSTIMESTAMP, SQLERRM, SQL_UTIL.GET_LAST_SQLTEXT_WITH_BINDS);
         COMMIT;
END execute_insert;
/

注意事项 #

• 权限配置：需要为执行用户授予SELECT权限访问相关视图，执行以下语句授权：

  GRANT SELECT ON v_$session TO your_user;
  GRANT SELECT ON v_$sql TO your_user;
  GRANT SELECT ON v_$sql_bind_capture TO your_user;
  

• 绑定变量捕获限制：V$SQL_BIND_CAPTURE仅捕获已执行过的游标绑定变量，短生命周期的游标可能被内存回收；value_string字段最大存储4000字符，超长变量值会被截断。
• 并发安全：需确保在异常发生后立即调用函数，避免会话执行其他SQL覆盖prev_sql_id。
• 类型扩展：上述代码已处理字符、日期和数值类型，若需支持CLOB、RAW等其他类型，需扩展datatype判断逻辑。

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