本章内容:
1. 利用Oracle提供的包扩展功能(使用UTL_FILE访问文件、使用DBMS_JOB调度作业、DBMS_XPLAN生成解释计划、DBMS_SQL产生隐式语句结果 )
2. 利用Oracle提供的包报告错误(使用DBMS_UTILITY包报告错误、使用UTL_CALL_STACK包报告错误)
代码如下:
1. DYNAMIC_DEPTH (返回调用栈中子程序的数量)
SQL> -- For Example? ch24_10.sql
SQL> CREATE OR REPLACE PROCEDURE first
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
5 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
6 END first;
7 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE second
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
5 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
6 first;
7 END second;
8 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE third
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
5 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
6 second;
7 END third;
8 /
Procedure created.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('anonymous block');
3 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
4 third;
5 END;
6 /
anonymous block
dynamic depth: 1
procedure THIRD
dynamic depth: 2
procedure SECOND
dynamic depth: 3
procedure FIRST
dynamic depth: 4
2. 回溯深度、单元和行号函数
SQL> /* Formatted on 2018/11/19 22:00:34 (QP5 v5.256.13226.35538) */
SQL> -- For Example ch24_11.sql
SQL>
SQL> CREATE OR REPLACE PROCEDURE FIRST
2 IS
3 v_string VARCHAR2 (3);
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
6 DBMS_OUTPUT.PUT_LINE (
7 'dynamic depth: ' || TO_CHAR (UTL_CALL_STACK.DYNAMIC_DEPTH));
8 v_string := 'ABCDEF';
9 END FIRST;
10 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE second
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
5 DBMS_OUTPUT.PUT_LINE (
6 'dynamic depth: ' || TO_CHAR (UTL_CALL_STACK.DYNAMIC_DEPTH));
7 FIRST;
8 END second;
9 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE third
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
5 DBMS_OUTPUT.PUT_LINE (
6 'dynamic depth: ' || TO_CHAR (UTL_CALL_STACK.DYNAMIC_DEPTH));
7 second;
8 END third;
9 /
Procedure created.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('anonymous block');
3 DBMS_OUTPUT.PUT_LINE (
4 'dynamic depth: ' || TO_CHAR (UTL_CALL_STACK.DYNAMIC_DEPTH));
5 third;
6 EXCEPTION
7 WHEN OTHERS
8 THEN
9 DBMS_OUTPUT.PUT_LINE (
10 CHR (10) || 'Backtrace Stack: ' || CHR (10) || RPAD ('-', 15, '-'));
11 DBMS_OUTPUT.PUT_LINE (
12 'Backtrace Depth: ' || TO_CHAR (UTL_CALL_STACK.BACKTRACE_DEPTH));
13 DBMS_OUTPUT.PUT_LINE (
14 'Backtrace Line: '
15 || TO_CHAR (
16 UTL_CALL_STACK.BACKTRACE_LINE (UTL_CALL_STACK.BACKTRACE_DEPTH)));
17 DBMS_OUTPUT.PUT_LINE (
18 'Backtrace Unit: '
19 || UTL_CALL_STACK.BACKTRACE_UNIT (UTL_CALL_STACK.BACKTRACE_DEPTH));
20 END;
21 /
anonymous block
dynamic depth: 1
procedure THIRD
dynamic depth: 2
procedure SECOND
dynamic depth: 3
procedure FIRST
dynamic depth: 4
Backtrace Stack:
---------------
Backtrace Depth: 4
Backtrace Line: 8
Backtrace Unit: C##STUDENT.FIRST
3. 错误深度、消息和编号函数
SQL> --For Example? ch24_12.sql
SQL> CREATE OR REPLACE PROCEDURE first
2 IS
3 v_string VARCHAR2(3);
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE ('procedure FIRST');
6 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
7 v_string := 'ABCDEF';
8 END first;
9 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE second
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure SECOND');
5 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
6 first;
7 END second;
8 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE third
2 IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE ('procedure THIRD');
5 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
6 second;
7 END third;
8 /
Procedure created.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('anonymous block');
3 DBMS_OUTPUT.PUT_LINE ('dynamic depth: '||TO_CHAR(UTL_CALL_STACK.DYNAMIC_DEPTH));
4 third;
5 EXCEPTION
6 WHEN OTHERS
7 THEN
8 DBMS_OUTPUT.PUT_LINE (CHR(10)||'Backtrace Stack: '||CHR(10)||RPAD('-', 15, '-'));
9 DBMS_OUTPUT.PUT_LINE ('Backtrace Depth: '||TO_CHAR(UTL_CALL_STACK.BACKTRACE_DEPTH));
10 DBMS_OUTPUT.PUT_LINE ('Backtrace Line: ' ||
11 TO_CHAR(UTL_CALL_STACK.BACKTRACE_LINE(UTL_CALL_STACK.BACKTRACE_DEPTH)));
12 DBMS_OUTPUT.PUT_LINE ('Backtrace Unit: ' ||
13 UTL_CALL_STACK.BACKTRACE_UNIT(UTL_CALL_STACK.BACKTRACE_DEPTH));
14 DBMS_OUTPUT.PUT_LINE (CHR(10)||'Error Info: '||CHR(10)||RPAD('-', 15, '-'));
15 DBMS_OUTPUT.PUT_LINE ('Error Depth: ' ||TO_CHAR(UTL_CALL_STACK.ERROR_DEPTH));
16 DBMS_OUTPUT.PUT_LINE ('Error Number: ' ||
17 TO_CHAR(UTL_CALL_STACK.ERROR_NUMBER (UTL_CALL_STACK.ERROR_DEPTH)));
18 DBMS_OUTPUT.PUT_LINE ('Error Message: '||
19 UTL_CALL_STACK.ERROR_MSG(UTL_CALL_STACK.ERROR_DEPTH));
20 END;
21 /
anonymous block
dynamic depth: 1
procedure THIRD
dynamic depth: 2
procedure SECOND
dynamic depth: 3
procedure FIRST
dynamic depth: 4
Backtrace Stack:
---------------
Backtrace Depth: 4
Backtrace Line: 7
Backtrace Unit: C##STUDENT.FIRST
Error Info:
---------------
Error Depth: 1
Error Number: 6502
Error Message: PL/SQL: numeric or value error: character string buffer too small