PL/SQL精明的调用栈分析
PL/SQL精明的调用栈分析
原文:http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14plsql-2045346.html
The three DBMS_UTILITY functions
(DBMS_UTILITY.FORMAT_CALL_STACK, DBMS_UTILITY.FORMAT_ERROR_STACK, and DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE) have been crucial aids in diagnosing and resolving problems in PL/SQL code. The UTL_CALL_STACK package recognizes the importance of this data and takes a big step forward in giving PL/SQL developers access to more in-depth and useful information
12C曾经的3个工具函数(DBMS_UTILITY.FORMAT_CALL_STACK,DBMS_UTILITY.FORMAT_ERROR_STACK,DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE)
已经给PL/SQL程序分析和问题解决提供了关键的帮助。
12C開始引入的UTL_CALL_STACK包意识到改类数据的重要性并进一步增强以使PL/SQL开发人员能够获得很多其它深层次的实用的信息。
–调用栈 Call Stacks :DBMS_UTILITY.FORMAT_CALL_STACK
回答了 “How did I get here?
” 。我是怎么一步一步到达这里的?比如:
SQL> CREATE OR REPLACE PROCEDURE proc1
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
5 END;
6 /
SQL> CREATE OR REPLACE PACKAGE pkg1
2 IS
3 PROCEDURE proc2;
4 END pkg1;
5 /
SQL> CREATE OR REPLACE PACKAGE BODY pkg1
2 IS
3 PROCEDURE proc2
4 IS
5 BEGIN
6 proc1;
7 END;
8 END pkg1;
9 /
SQL> CREATE OR REPLACE PROCEDURE proc3
2 IS
3 BEGIN
4 FOR indx IN 1 .. 1000
5 LOOP
6 NULL;
7 END LOOP;
8
9 pkg1.proc2;
10 END;
11 /
SQL> BEGIN
2 proc3;
3 END;
4 /
——————— PL/SQL Call Stack ———————
object handle line number object name
000007FF7EA83240 4 procedure HR.PROC1
000007FF7E9CC3B0 6 package body HR.PKG1
000007FF7EA0A3B0 9 procedure HR.PROC3
000007FF7EA07C00 2 anonymous block
–弊端:
If you call a subprogram in a package, the formatted call stack will show only the package name, not the subprogram name and certainly not the names of nested subprograms defined within that packaged subprogram.
假设我们调用包中的子程序,那么此函数只能显示报名。压根不会显示子程序的名称更别提嵌套子程序的名称了。
If you simply want the name of the most recently executed subprogram, you will have to parse the string. This is not hard to do, but it’s more code that you have to write and maintain.
假设我们只想看下近期运行的子程序名称,还得去解析冗长的字符串。尽管这并非难事,但无疑加重了开发人员的负担。
The object handle value is, for all practical purposes, “noise.” PL/SQL developers—outside of Oracle, at least—never use it.
object handle值是个鸡肋,无实际用途。
–错误栈 Error Stacks :DBMS_UTILITY.FORMAT_ERROR_STACK Similar to SQLERRM
The DBMS_UTILITY.FORMAT_ERROR_STACK function differs from SQLERRM in two ways:
It can return an error message as long as 1,899 characters, thereby avoiding (or at least making extremely unlikely) truncation issues when the error stack gets long. (SQLERRM truncates at only 510 characters.)
You cannot pass an error code number to this function, and it cannot be used to return the message for an error code.
–错误回溯 Error Backtraces :DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
returns a formatted string that displays a stack of programs and line numbers tracing back to the line on which the error was originally raised.
12c: UTL_CALL_STACK package
Name Description
BACKTRACE_DEPTH Returns the number of backtrace items in the backtrace
BACKTRACE_LINE Returns the line number of the unit at the specified backtrace depth
BACKTRACE_UNIT Returns the name of the unit at the specified backtrace depth
CONCATENATE_SUBPROGRAM Returns a concatenated form of a unit-qualified name
DYNAMIC_DEPTH Returns the number of subprograms in the call stack, including SQL, Java, and other non-PL/SQL contexts invoked along the way—for example, if A calls B calls C calls B, this stack, written as a line with dynamic depths underneath it, will look like this:
A B C B
4 3 2 1
ERROR_DEPTH Returns the number of errors in the call stack
ERROR_MSG Returns the error message of the error at the specified error depth
ERROR_NUMBER Returns the error number of the error at the specified error depth
LEXICAL_DEPTH Returns the lexical nesting level of the subprogram at the specified dynamic depth
OWNER Returns the owner name of the unit of the subprogram at the specified dynamic depth
UNIT_LINE Returns the line number of the unit of the subprogram at the specified dynamic depth
SUBPROGRAM Returns the unit-qualified name of the subprogram at the specified dynamic depth
SQL> CREATE OR REPLACE PROCEDURE format_call_stack_12c
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line (
5 'LexDepth Depth LineNo Name');
6 DBMS_OUTPUT.put_line (
7 '-------- ----- ------ ----');
8
9 FOR the_depth IN REVERSE 1 ..
10 utl_call_stack.dynamic_depth ()
11 LOOP
12 DBMS_OUTPUT.put_line (
13 RPAD (
14 utl_call_stack.lexical_depth (
15 the_depth),
16 9)
17 || RPAD (the_depth, 5)
18 || RPAD (
19 TO_CHAR (
20 utl_call_stack.unit_line (
21 the_depth),
22 '99'),
23 8)
24 || utl_call_stack.concatenate_subprogram (
25 utl_call_stack.subprogram (
26 the_depth)));
27 END LOOP;
28 END;
29 /
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 PROCEDURE do_stuff;
4 END;
5 /
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 PROCEDURE do_stuff
4 IS
5 PROCEDURE np1
6 IS
7 PROCEDURE np2
8 IS
9 PROCEDURE np3
10 IS
11 BEGIN
12 format_call_stack_12c;
13 END;
14 BEGIN
15 np3;
16 END;
17 BEGIN
18 np2;
19 END;
20 BEGIN
21 np1;
22 END;
23 END;
24 /
SQL> BEGIN
2 pkg.do_stuff;
3 END;
4 /
LexDepth Depth LineNo Name
——————— ——————— ———————— ——————————————————————————
0 6 2 __anonymous_block
1 5 21 PKG.DO_STUFF
2 4 18 PKG.DO_STUFF.NP1
3 3 15 PKG.DO_STUFF.NP1.NP2
4 2 12 PKG.DO_STUFF.NP1.NP2.NP3
0 1 12 FORMAT_CALL_STACK_12C
SQL> CREATE OR REPLACE FUNCTION backtrace_to
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 RETURN
6 utl_call_stack.backtrace_unit (
7 utl_call_stack.error_depth)
8 || ' line '
9 ||
10 utl_call_stack.backtrace_line (
11 utl_call_stack.error_depth);
12 END;
13 /
SQL> CREATE OR REPLACE PACKAGE pkg1
2 IS
3 PROCEDURE proc1;
4 PROCEDURE proc2;
5 END;
6 /
SQL> CREATE OR REPLACE PACKAGE BODY pkg1
2 IS
3 PROCEDURE proc1
4 IS
5 PROCEDURE nested_in_proc1
6 IS
7 BEGIN
8 RAISE VALUE_ERROR;
9 END;
10 BEGIN
11 nested_in_proc1;
12 END;
13
14 PROCEDURE proc2
15 IS
16 BEGIN
17 proc1;
18 EXCEPTION
19 WHEN OTHERS THEN RAISE NO_DATA_FOUND;
20 END;
21 END pkg1;
22 /
SQL> CREATE OR REPLACE PROCEDURE proc3
2 IS
3 BEGIN
4 pkg1.proc2;
5 END;
6 /
SQL> BEGIN
2 proc3;
3 EXCEPTION
4 WHEN OTHERS
5 THEN
6 DBMS_OUTPUT.put_line (backtrace_to);
7 END;
8 /
HR.PKG1 line 19
- 顶
- 0
- 踩
- 0
- 个人资料
- 訪问:1403040次
- 积分:10186
- 等级:
- 排名:第1930名
- 原创:157篇
- 转载:48篇
- 译文:22篇
- 评论:113条
- 文章搜索
- 博客专栏
PL/SQL开发
文章:69篇 阅读:752091 |
- 文章分类
- Java核心(32)
- Java Web开发(8)
- Web前端(6)
- Oracle基础(50)
- PL/SQL开发(43)
- SQL优化(6)
- Oracle备份恢复(7)
- Oracle DBA相关(7)
- Oracle故障排除(19)
- MySql(11)
- SQL Server(0)
- DB2(2)
- Linux(21)
- FTP相关(1)
- 文章存档
- 2018年01月(8)
- 2017年12月(18)
- 2017年11月(2)
- 2017年10月(5)
- 2017年09月(2)
- 2017年08月(1)
- 2015年09月(2)
- 2015年08月(5)
- 2015年07月(6)
- 2015年06月(6)
- 2015年05月(1)
- 2015年04月(1)
- 2015年03月(6)
- 2015年02月(1)
- 2015年01月(11)
- 2014年12月(6)
- 2014年11月(1)
- 2014年10月(1)
- 2014年09月(2)
- 2014年08月(4)
- 2014年07月(8)
- 2014年06月(6)
- 2014年05月(7)
- 2014年04月(2)
- 2014年03月(3)
- 2014年02月(6)
- 2014年01月(6)
- 2013年12月(8)
- 2013年11月(10)
- 2013年10月(3)
- 2013年08月(5)
- 2013年07月(11)
- 2013年05月(3)
- 2013年04月(6)
- 2013年03月(2)
- 2013年01月(3)
- 2012年12月(1)
- 2012年11月(11)
- 2012年10月(5)
- 2012年09月(4)
- 2012年08月(11)
- 2012年07月(17)
-
阅读排行
- ORACLE触发器具体解释(369433)
- mysql报错Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist(69280)
- FTP命令具体解释(含操作实例)(59445)
- ORACLE SEQUENCE 具体解释(41362)
- linux创建软链接命令-ln -s(30302)
- Oracle expdp/impdp工具使用(29919)
- No protocol specified(29442)
- MYSQL服务无法启动:InnoDB: .\ibdata1 can't be opened in read-write mode(26119)
- ORACLE SYNONYM具体解释(24985)
- oracle 游标变量ref cursor具体解释(21819)
- 评论排行
- ORACLE触发器具体解释(50)
- MYSQL服务无法启动:InnoDB: .\ibdata1 can't be opened in read-write mode(9)
- 细说 QUEST CENTRAL FOR DB2 八宗罪(8)
- mysql报错Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist(6)
- 关于动态抽样(Dynamic Sampling)(3)
- PLSQL编译存储过程无响应(3)
- Oracle ref cursor使用(3)
- 怎样配置使用 Log4j(2)
- 数据库范式具体解释(2)
- oracle角色管理(2)
- 最新评论
-
FTP命令具体解释(含操作实例)
u011367208: cmd.exe在远程连接ftpserver时无法输入password,你要我怎么登陆?Xftp 5也没有容量查询工具(...
-
linux创建软链接命令-ln -s
HEJI1103: 学习了,谢谢分享
-
MYSQL服务无法启动:InnoDB: .\ibdata1 can't be opened in read-write mode
weituotian_01: 实用哈
-
MYSQL服务无法启动:InnoDB: .\ibdata1 can't be opened in read-write mode
u013047824: 感谢分享 谢谢
-
ORACLE触发器具体解释
sinat_39324576: 非常好的东西
-
MYSQL服务无法启动:InnoDB: .\ibdata1 can't be opened in read-write mode
Purgatory001: 假设同一时候安装了两个mysql 提示这个错误呢?
-
mysql报错Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
u011456940: 楼主写的那条命令错了 mysql_install_db --user=mysql --datadir...
-
Oracle 记录类型介绍
qq_17043799: 请问在写函数的时候返回一个记录类型,提示声明类型前非法引用。应该怎么解决
-
mysql报错Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
zlj1817: 这个mysql_install_db –usrer=mysql datadir=/var/lib/m...
-
Oracle insert all语句介绍
sinat_37690877: 为什么我不行?我想一次性insert into 多行数据。就这么简单的需求啊