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
相關文章
- PL/SQL呼叫JAVA CLASSSQLJava
- PL/SQL呼叫時使用呼叫者的許可權SQL
- 使用PL/Scope分析PL/SQL程式碼SQL
- oracle dbms_profiles分析pl/sqlOracleSQL
- 批量分析表的一段PL/SqlSQL
- PLSQL Language Referenc-PL/SQL子程式-子程式呼叫SQL
- openGauss關於PL/SQL匿名塊呼叫測試SQL
- 【OracleEBS】 在PL/SQL中呼叫Oracle ERP請求OracleSQL
- Laravel的呼叫棧Laravel
- 在pl/SQL中呼叫logminer相關檢視的問題SQL
- pl/sql developer 分析的執行計劃不可信SQLDeveloper
- PL/SQLSQL
- PLSQL Language Reference-PL/SQL概覽-PL/SQL的優點SQL
- Oracle PL/SQL編寫PL/SQL程式碼的注意事項OracleSQL
- 使用DBMS_PROFILER進行PL/SQL效能分析SQL
- SQL&PL/SQL (轉)SQL
- PL/SQL 宣告SQL
- PL/SQL cursorSQL
- PL/SQL打包SQL
- PL/SQL DEVSQLdev
- Oracle PL/SQLOracleSQL
- JavaScript之呼叫棧JavaScript
- 函式呼叫棧函式
- PLSQL Language Reference-PL/SQL概覽-PL/SQL的主要功能SQL
- PL/SQL初學者必讀:幾十個實用的PL/SQL (轉)SQL
- pl/sql中的row物件SQL物件
- pl/sql的HomeEnd問題SQL
- PLSQL Language Referenc-PL/SQL動態SQL-練習:在動態SQL塊中呼叫子程式。SQL
- PLSQL Language Reference-PL/SQL概覽-PL/SQL架構SQL架構
- [PL/SQL]10g PL/SQL學習筆記(一)SQL筆記
- [PL/SQL]10g PL/SQL學習筆記(二)SQL筆記
- [PL/SQL]10g PL/SQL學習筆記(三)SQL筆記
- 關於shell中的pl/sql指令碼錯誤排查與分析SQL指令碼
- 函式呼叫棧的問題函式
- T-SQL、Jet SQL、PL-SQL 的含義SQL
- PL/SQL 迴圈SQL
- PL/SQL 遊標SQL
- PL/SQL 運算子SQL