PL/SQL精明的调用栈分析

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了PL/SQL精明的调用栈分析，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4542字，纯文字阅读大概需要7分钟。

内容图文

已经给PL/SQL程序分析和问题解决提供了关键的帮助。12C开始引入的UTL_CALL_STACK包意识到改类数据的重要性并进一步增强以使PL/SQ

PL/SQL精明的调用栈分析

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

内容总结

以上是互联网集市为您收集整理的PL/SQL精明的调用栈分析全部内容，希望文章能够帮你解决PL/SQL精明的调用栈分析所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端