Oracle PL/SQL 调试技巧

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

PL/SQL 调试技巧汇总[1]：

详细见：Oracle PL/SQL 调试技巧。

---

2. DBMS_OUTPUT

2.1 基本

BEGIN
  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('Start');
  DBMS_OUTPUT.PUT_LINE('Value: ' || v_var);
  DBMS_OUTPUT.PUT_LINE('End');
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED;

2.2 格式

DBMS_OUTPUT.PUT_LINE('Name=' || v_name || ', Age=' || v_age);
DBMS_OUTPUT.PUT('No newline');
DBMS_OUTPUT.NEW_LINE;

---

3. 异常信息

3.1 SQLCODE / SQLERRM

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Code: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    DBMS_OUTPUT.PUT_LINE('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
    DBMS_OUTPUT.PUT_LINE('Call: ' || DBMS_UTILITY.FORMAT_CALL_STACK);
    RAISE;
END;

3.2 DBMS_UTILITY

函数	说明
FORMAT_ERROR_BACKTRACE	错误位置
FORMAT_ERROR_STACK	错误栈
FORMAT_CALL_STACK	调用栈

---

4. 日志表

4.1 表

CREATE TABLE plsql_log (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  procedure_name VARCHAR2(100),
  line_number NUMBER,
  message VARCHAR2(4000),
  log_time TIMESTAMP
);

4.2 过程

CREATE OR REPLACE PROCEDURE log_msg(
  p_proc VARCHAR2,
  p_line NUMBER,
  p_msg VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO plsql_log (procedure_name, line_number, message, log_time)
  VALUES (p_proc, p_line, p_msg, SYSTIMESTAMP);
  COMMIT;
END;
/

4.3 使用

CREATE OR REPLACE PROCEDURE my_proc IS
  v_count NUMBER;
BEGIN
  log_msg('MY_PROC', $$LINE, 'Start');
  
  SELECT COUNT(*) INTO v_count FROM employees;
  log_msg('MY_PROC', $$LINE, 'Count: ' || v_count);
  
  -- ...
  
  log_msg('MY_PROC', $$LINE, 'End');
EXCEPTION
  WHEN OTHERS THEN
    log_msg('MY_PROC', $$LINE, 'Error: ' || SQLERRM);
    RAISE;
END;
/

---

5. DBMS_PROFILER

5.1 启用

-- 安装
@?/rdbms/admin/proftab.sql
@?/rdbms/admin/profload.sql

-- 启动
EXEC DBMS_PROFILER.START_PROFILER('test');

-- 执行代码

-- 停止
EXEC DBMS_PROFILER.STOP_PROFILER;

5.2 分析

SELECT unit_name, line, total_occur, total_time, min_time, max_time
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = 1
  AND d.unit_number = u.unit_number
ORDER BY total_time DESC;

---

6. DBMS_HPROF

6.1 启用

-- 安装
@?/rdbms/admin/dbmshptab.sql

-- 目录
CREATE DIRECTORY hprof_dir AS '/tmp';

-- 启动
EXEC DBMS_HPROF.START_PROFILING('HPROF_DIR', 'test.txt');

-- 执行

-- 停止
EXEC DBMS_HPROF.STOP_PROFILING;

-- 分析
EXEC DBMS_HPROF.ANALYZE('HPROF_DIR', 'test.txt');

6.2 查看

SELECT * FROM dbmshp_function_info ORDER BY function_elapsed_time DESC;
SELECT * FROM dbmshp_parent_child_info;

---

7. TRACE

7.1 PL/SQL Trace

ALTER SESSION SET plsql_debug = TRUE;

ALTER SESSION SET EVENTS '10938 trace name context forever, level 1';

7.2 10046

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- 执行
ALTER SESSION SET EVENTS '10046 trace name context off';

详细见：Oracle 10046 事件与 SQL Trace。

---

8. 条件编译

8.1 启用调试

CREATE OR REPLACE PROCEDURE my_proc IS
  $IF $$DEBUG $THEN
    DBMS_OUTPUT.PUT_LINE('Debug: Start');
  $END
BEGIN
  ...
END;
/

ALTER SESSION SET PLSQL_CCFLAGS = 'DEBUG:TRUE';

8.2 检查

SELECT name, value FROM user_plsql_object_settings 
WHERE name = 'MY_PROC';

---

9. 断言

CREATE OR REPLACE PROCEDURE assert(p_cond BOOLEAN, p_msg VARCHAR2) IS
BEGIN
  IF NOT p_cond THEN
    RAISE_APPLICATION_ERROR(-20000, 'Assert failed: ' || p_msg);
  END IF;
END;
/

-- 使用
CREATE PROCEDURE my_proc IS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;
  assert(v_count > 0, 'No employees');
END;
/

---

10. UTL_CALL_STACK（12c+）

DECLARE
  v_depth NUMBER;
BEGIN
  v_depth := UTL_CALL_STACK.DYNAMIC_DEPTH;
  
  FOR i IN 1..v_depth LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Unit: ' || UTL_CALL_STACK.CONCATENATED_OWNER_NAME(i) || '.' ||
                  UTL_CALL_STACK.CONCATENATED_NAME(i) ||
      ' Line: ' || UTL_CALL_STACK.UNIT_LINE(i) ||
      ' Subprogram: ' || UTL_CALL_STACK.SUBPROGRAM(i)
    );
  END LOOP;
END;
/

---

11. 调试模式

11.1 编译调试

ALTER PROCEDURE my_proc COMPILE DEBUG;

11.2 DBMS_DEBUG

-- 旧 API
-- 一般用 IDE

11.3 IDE

- SQL Developer
- PL/SQL Developer
- TOAD
- VS Code

---

12. 常见调试场景

12.1 变量值

DBMS_OUTPUT.PUT_LINE('v_count=' || v_count);
DBMS_OUTPUT.PUT_LINE('v_name=' || v_name);

12.2 流程

DBMS_OUTPUT.PUT_LINE('Step 1');
-- 代码
DBMS_OUTPUT.PUT_LINE('Step 2');

12.3 异常

EXCEPTION
  WHEN OTHERS THEN
    log_msg(...);
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;

12.4 性能

v_start := DBMS_UTILITY.GET_TIME;
-- 代码
v_end := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Elapsed: ' || (v_end - v_start) || ' hsec');

---

13. 工具

13.1 SQL Developer

- Debug 按钮
- 断点
- 变量监视
- 调用栈

13.2 PL/SQL Developer

- Test 模式
- 断点
- 变量
- 监视

---

14. 常见坑与排错

14.1 SERVEROUTPUT OFF

- 无输出
- SET SERVEROUTPUT ON

14.2 缓冲区满

- SIZE UNLIMITED
- 或 SIZE 1000000

14.3 自治事务

- 日志独立
- COMMIT
- 不影响主事务

---

15. 最佳实践

1. DBMS_OUTPUT：简单
2. 日志表：生产
3. EXCEPTION：完整
4. BACKTRACE：位置
5. 条件编译：开关
6. 断言：验证
7. Profiler：性能
8. HPROF：层次
9. IDE：交互
10. 测试：验证

---

16. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Profiling PL/SQL” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html