Oracle dbms utility format error backtrace - Исправление ошибок и поиск оптимальных решений проблем

Статьи про Oracle

Программирование

dbms_utility.format_error_backtrace — отслеживание ошибок в pl/sql

v:1.0 03.12.2011

Петрелевич Сергей

Часто при обработке исключений в pl/sql разработчики ограничиваются использованием встроенной функции sqlerrm, однако в Oracle есть и другие интересные
средства для обработки ошибок. Одно из этих средств — функция dbms_utility.format_error_backtrace.

Функция dbms_utility.format_error_backtrace возвращает список вызовов функций и процедур от места генерации исключения до места его обработки.
Для рассмотрения работы dbms_utility.format_error_backtrace создадим тестовый пакет с процедурами, которые подобно матрешкам вызываются одна из другой.

create or replace package Err asprocedure testProc1; procedure testProc2; procedure testProc3; procedure testProc4;end;

Пример 1. dbms_utility.format_error_backtrace в каждом обработчике исключений

В первом примере функция dbms_utility.format_error_backtrace вставлена в обработчики исключений каждой процедуры.

create or replace package body Err as procedure testProc1 is begin dbms_output.put_line('testProc1'); testProc2; exception when others then dbms_output.put_line('testProc1 error:'||dbms_utility.format_error_stack); dbms_output.put_line('stack:'||dbms_utility.format_error_backtrace); end; procedure testProc2 is begin dbms_output.put_line('testProc2'); testProc3; exception when others then dbms_output.put_line('testProc2 error:'||dbms_utility.format_error_stack); dbms_output.put_line('stack:'||dbms_utility.format_error_backtrace); raise; end; procedure testProc3 is begin dbms_output.put_line('testProc3'); testProc4; exception when others then dbms_output.put_line('testProc3 error:'||dbms_utility.format_error_stack); dbms_output.put_line('stack:'||dbms_utility.format_error_backtrace); raise; end; procedure testProc4 is begin dbms_output.put_line('testProc4'); raise no_data_found; exception when others then dbms_output.put_line('testProc4 error:'||dbms_utility.format_error_stack); dbms_output.put_line('stack:'||dbms_utility.format_error_backtrace); raise; end; end;

Выполняем первую процедуру.

Получаем результат:

testProc1
testProc2
testProc3
testProc4
testProc4 error:ORA-01403: no data found

stack:ORA-06512: at "DEV.ERR", line 38

testProc3 error:ORA-01403: no data found

stack:ORA-06512: at "DEV.ERR", line 43
ORA-06512: at "DEV.ERR", line 27

testProc2 error:ORA-01403: no data found

stack:ORA-06512: at "DEV.ERR", line 32
ORA-06512: at "DEV.ERR", line 16

testProc1 error:ORA-01403: no data found

stack:ORA-06512: at "DEV.ERR", line 21
ORA-06512: at "DEV.ERR", line 6

Видно, что функция dbms_utility.format_error_backtrace выводит пакет, в котором сработало исключение и номер строки. Надо отметить, что в данном примере
и строки вида dbms_output.put_line(‘testProc3 error:’||dbms_utility.format_error_stack); отлично формируют стек ошибки, dbms_utility.format_error_backtrace лишь
уточняет конкретную строку.

Пример 2. dbms_utility.format_error_backtrace в стартовой процедуре, во всех процедурах есть обработчики исключений

Рассмотрим другой пример.
На этот раз dbms_utility.format_error_backtrace будет в стартовой процедуре, из которой вызываются другие.
В других процедурах будут обработчики исключений, но в них не будет вызова dbms_utility.format_error_backtrace.

create or replace package body Err as procedure testProc1 is begin dbms_output.put_line('testProc1'); testProc2; exception when others then dbms_output.put_line('testProc1 error:'||dbms_utility.format_error_stack); dbms_output.put_line('stack:'||dbms_utility.format_error_backtrace); end; procedure testProc2 is begin dbms_output.put_line('testProc2'); testProc3; exception when others then raise; end; procedure testProc3 is begin dbms_output.put_line('testProc3'); testProc4; exception when others then raise; end; procedure testProc4 is begin dbms_output.put_line('testProc4'); raise no_data_found; exception when others then raise; end; end;

Выполняем первую процедуру.

Получаем результат:

testProc1
testProc2
testProc3
testProc4
testProc1 error:ORA-01403: no data found

stack:ORA-06512: at "DEV.ERR", line 19
ORA-06512: at "DEV.ERR", line 6

Информации не очень-то много. Складывается впечатление, что ошибка возникла в 19 строке кода, хотя правильное место — строка 34.
Причина в том, что dbms_utility.format_error_backtrace показывает стек не с места фактического появления ошибки, а с мест ее последнего перехвата.
В нашем случае это процедура proc2, строка 21.
Получается, в этом случае dbms_utility.format_error_backtrace ни только не помогает, а еще и мешает, запутывает следы.

Пример 3. dbms_utility.format_error_backtrace в стартовой процедуре, в других процедурах нет обработчиков исключений

На этот раз все исключения обрабатываются только в одном месте — вызывающей функции, и только в ней используется dbms_utility.format_error_backtrace.

create or replace package body Err as procedure testProc1 is begin dbms_output.put_line('testProc1'); testProc2; exception when others then dbms_output.put_line('testProc1 error:'||dbms_utility.format_error_stack); dbms_output.put_line('stack:'||dbms_utility.format_error_backtrace); end; procedure testProc2 is begin dbms_output.put_line('testProc2'); testProc3; end; procedure testProc3 is begin dbms_output.put_line('testProc3'); testProc4; end; procedure testProc4 is begin dbms_output.put_line('testProc4'); raise no_data_found; end; end;

Выполняем первую процедуру.

Получаем результат:

testProc1
testProc2
testProc3
testProc4
testProc1 error:ORA-01403: no data found

stack:ORA-06512: at "DEV.ERR", line 32
ORA-06512: at "DEV.ERR", line 25
ORA-06512: at "DEV.ERR", line 18
ORA-06512: at "DEV.ERR", line 7

На этот раз информации куда больше. По выводу функции dbms_utility.format_error_backtrace можно точно отследить, что ошибка появилась в функции testProc4,
строка 32, и какие процедуры выполнялись.

Из этих примеров можно вывести две основные тактики применения dbms_utility.format_error_backtrace.
Эту функцию надо вставлять в каждый обработчик исключений или обрабатывать все исключения в одной точке — стартовой процедуре(функции).

Метки:

PL/SQL

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.

Источник

Часто при обработке исключений в pl/sql разработчики ограничиваются использованием встроенной функции sqlerrm, однако в Oracle есть и другие интересные средства для обработки ошибок. Одно из этих средств — функция dbms_utility.format_error_backtrace.

procedure testProc1;
procedure testProc2;
procedure testProc3;
procedure testProc4;

В первом примере функция dbms_utility.format_error_backtrace вставлена в обработчики исключений каждой процедуры.

Рассмотрим другой пример.
На этот раз dbms_utility.format_error_backtrace будет в стартовой процедуре, из которой вызываются другие. В других процедурах будут обработчики исключений, но в них не будет вызова dbms_utility.format_error_backtrace.

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: , , ,
.

Источник

8 DBMS_UTILITY

The DBMS_UTILITY package provides various utility subprograms.

This chapter contains the following topics:

Us ing DBMS_UTILITY

Security model

DBMS_UTILITY runs with the privileges of the calling user for the NAME_RESOLVE procedure and the COMPILE_SCHEMA procedure. This is necessary so that the SQL works correctly.

The package does not run as SYS .

Constants

The DBMS_UTILITY package uses the constants shown in Table 8-1.

Table 8-1 DBMS_UTILITY constants

This constant is the only legal value for the p_option_flags parameter of the INVALIDATE subprogram.

The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.

The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

Data types

dblink_array

Lists of database links would be stored here. (TimesTen does not support dblinks.)

index_table_type

The order in which objects should be generated is returned here.

instance_record

The list of active instance number and instance name.

The starting index of instance_table is 1; instance_table is dense.

lname_array

Lists of long NAME should be stored here, including fully qualified attribute names.

name_array

Lists of NAME should be stored here.

number_array

The order in which objects should be generated is returned here.

uncl_array

Lists of » USER «.» NAME «.» COLUMN «@ LINK should be stored here.

Exceptions

The following table lists the exceptions raised by DBMS_UTILITY .

Table 8-2 Exceptions Raised by DBMS_UTILITY

Name Type Value Description

Raised by the INVALIDATE subprogram when the object_id argument is NULL or invalid, or when the caller does not have CREATE privilege on the object being invalidated.

Raised by the INVALIDATE subprogram if a compiler setting is specified more than once in the p_plsql_object_settings parameter.

Raised by the INVALIDATE subprogram when different combinations of conditions pertaining to the p_object_id parameter are contravened.

Sum mary of DBMS_UTILITY subprograms

Table 8-3 DBMS_UTILITY Package Subprograms

Exception Error Code Description

Canonicalizes a given string.

Converts a comma-delimited list of names into a PL/SQL table of names.

Compiles all procedures, functions, packages, and views in the specified schema.

Returns version information for the database.

Returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE .

Formats the current call stack.

Formats the backtrace from the point of the current error to the exception handler where the error has been caught.

Formats the current error stack.

Returns the current CPU time in hundredths of a second.

Shows the dependencies on the object passed in.

Returns the endianness of your database platform.

Computes a hash value for the given string.

Computes the hash value for a given string using the MD5 algorithm.

Finds out the current time in hundredths of a second.

Invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings.

Checks the setting of a specified bit in a RAW value.

Resolves the given name of the form:

Where a , b , and c are SQL identifiers and dblink is a dblink.

Important : Do not use @ dblink . TimesTen does not support database links.

Calls the parser to parse the given name:

Where a , b , and c are SQL identifiers and dblink is a dblink. Strips double quotes or converts to uppercase if there are no quotes. Ignores comments and does not perform semantic analysis. Missing values are NULL .

Important : Do not use @ dblink . TimesTen does not support database links.

Converts a PL/SQL table of names into a comma-delimited list of names.

Validates the object described either by owner, name, and namespace or by object ID.

CANONICALIZE procedure

This procedure canonicalizes the given string. The procedure handles a single reserved or key word (such as » table «), and strips off white spaces for a single identifier. For example, » table » becomes TABLE .

Table 8-4 CANONICALIZE procedure parameters

Subprogram Description

The string to be canonicalized

The canonicalized string

The length of the string (in bytes) to canonicalize

The first canon_len bytes in canon_name

If the name value is NULL , the canon_name value becomes NULL .

If name is a dotted name (such as a.»b».c ), then for each component in the dotted name where the component begins and ends with a double quote, no transformation is performed on that component. Alternatively, convert to upper case with NLS_UPPER and apply begin and end double quotes to the capitalized form of this component. In such a case, each canonicalized component is concatenated in the input position, separated by «.».

If name is not a dotted name, and if name begins and ends with a double quote, remove both quotes. Alternatively, convert to upper case with NLS_UPPER . Note that this case does not include a name with special characters, such as a space, but is not doubly quoted.

Any other character after a[.b]* is ignored.

The procedure does not handle cases like ‘A B.’

«a».b,c.f becomes «a».»B» with «,c.f» ignored.

COMMA_TO_TABLE procedure

This procedure converts a comma-delimited list of names into a PL/SQL table of names. The second version supports fully qualified attribute names.

Table 8-5 COMMA_TO_TABLE procedure parameters

Parameter Description

Comma-delimited list of names, where a name should have the following format for the first version of the procedure:

Or the following format for the second version of the procedure:

Where a , b , c , and d are simple identifiers (quoted or unquoted).

Number of tables in the PL/SQL table

PL/SQL table that contains list of names

A PL/SQL table with values 1.. n , and n +1 is NULL

The list must be a non-empty, comma-delimited list. Anything other than a comma-delimited list is rejected. Commas inside double quotes do not count.

Entries in the comma-delimited list cannot include multibyte characters.

The values in tab are copied from the original list, with no transformations.

COMPILE_SCHEMA procedure

This procedure compiles all procedures, functions, packages, and views in the specified schema.

Table 8-6 COMPILE_SCHEMA procedure parameters

Parameter Description

Name of the schema

TRUE to compile everything within the schema regardless of whether status is VALID

FALSE to compile only objects with status INVALID

Flag to specify whether the session settings in the objects should be reused, or the current session settings should be adopted instead

Note that this subprogram is a wrapper for the RECOMP_SERIAL procedure included with the UTL_RECOMP package.

After calling this procedure, you should select from view ALL_OBJECTS for items with status INVALID to see if all objects were successfully compiled.

To see the errors associated with invalid objects, you can use the ttIsql show errors command:

Table 8-7 COMPILE_SCHEMA procedure exceptions

Parameter Description

Raised for insufficient privileges for some object in this schema.

Raised if SYS objects cannot be compiled.

Raised if maximum iterations exceeded. Some objects may not have been recompiled.

DB_VERSION procedure

This procedure returns version information for the database.

Returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE .

Table 8-8 DB_VERSION procedure parameters

Exception Description

String that represents the internal software version of the database (for example, 11.2.2.1.0)

The length of this string is variable and is determined by the database version.

Compatibility setting of the database

In TimesTen, DB_VERSION returns NULL for the compatibility setting because TimesTen does not support the system parameter COMPATIBLE .

FORMAT_CALL_STACK function

This function formats the current call stack. It can be used on any stored procedure to access the call stack and is useful for debugging.

The call stack, up to 2000 bytes

FORMAT_ERROR_BACKTRACE function

This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. The output is similar to the output of the SQLERRM function, but not subject to the same size limitation.

The backtrace string (or a null string if no error is currently being handled)

Execute the following script from ttIsql , using the run command.

This shows the results of executing the Top_Naive procedure that is created in the script, assuming user SCOTT ran the script and executed the procedure.

This output shows the call stack at the point where an exception was raised. It shows the backtrace error message as the call stack unwound, starting at the unhandled exception ORA-01476 raised at SCOTT.P0 line 4, back to SCOTT.Top_Naive line 3.

This shows the results of executing the Top_With_Logging() procedure that is created in the script, assuming user SCOTT ran the script and executed the procedure.

This output shows the call stack at the point where an exception was raised. It shows the backtrace error message as the call stack unwound, starting at the unhandled exception ORA-01476 raised at SCOTT.P0 line 4, back to SCOTT.Top_With_Logging line 6.

Oracle Database Error Messages provides the following information about the ORA-06512 error:

FORMAT_ERROR_STACK function

This function formats the current error stack. It can be used in exception handlers to look at the full error stack.

The error stack, up to 2000 bytes (or a null string if no error is currently being handled)

GET_CPU_TIME function

This function returns a measure of current CPU processing time in hundredths of a second. The difference between the times returned from two calls measures the CPU processing time (not the total elapsed time) between those two points.

Also see the GET_TIME function, which has a different intent.

The number of hundredths of a second of CPU processing time from some arbitrary point

This subprogram reports cycles (CPU time) used in performing work and is unrelated to clock time or any other fixed reference. It always returns a positive value. The amount of work performed is calculated by measuring the difference between a start point and end point for a particular operation, using a GET_CPU_TIME call at each point.

GET_DEPENDENCY procedure

This procedure shows the dependencies on the object passed in.

Table 8-9 GET_DEPENDENCY procedure parameters

Parameter Description

The type of the object

For example, if the object is a table, give the type as «TABLE».

The schema name of the object

The name of the object

This procedure uses the DBMS_OUTPUT package to display results, so you must declare SET SERVEROUTPUT ON from ttIsql to view dependencies. Alternatively, any application that checks the DBMS_OUTPUT output buffers can invoke this subprogram and then retrieve the output through DBMS_OUTPUT subprograms such as GET_LINES .

GET_ENDIANNESS function

This function indicates the endianness of the database platform.

A NUMBER value indicating the endianness of the database platform: 1 for big-endian or 2 for little-endian

GET_HASH_VALUE function

This function computes a hash value for the given string.

Table 8-10 GET_HASH_VALUE function parameters

Parameter Description

String to be hashed

Base value where the returned hash value is to start

Desired size of the hash table

A hash value based on the input string

For example, to get a hash value on a string where the hash value should be between 1000 and 3047, use 1000 as the base value and 2048 as the hash_size value. Using a power of 2 for hash_size works best.

GET_SQL_HASH function

This function computes a hash value for the given string using the MD5 algorithm.

Table 8-11 GET_SQL_HASH procedure parameters

Parameter Description

String to be hashed

An optional field to store all 16 bytes of returned hash value

An optional field to store a pre-10 g Oracle Database version hash value

A hash value (last four bytes) based on the input string

The MD5 hash algorithm computes a 16-byte hash value, but TimesTen returns only the last four bytes to return an actual number. One could use an optional RAW parameter to get all 16 bytes and to store the pre-10 g Oracle Database hash value of four bytes in the pre10ihash optional parameter.

GET_TIME function

This function returns a measure of current time in hundredths of a second. The difference between the times returned from two calls measures the total elapsed time (not just CPU processing time) between those two points.

Also see the GET_CPU_TIME function, which has a different intent.

The number of hundredths of a second from the time at which the subprogram is invoked

Numbers are returned in the range -2,147,483,648 to 2,147,483,647 depending on platform and system, and your application must take the sign of the number into account in determining the interval. For example, for two negative numbers, application logic must allow for the first (earlier) number to be larger than the second (later) number that is closer to zero. By the same token, your application should also allow for the first (earlier) number to be negative and the second (later) number to be positive.

INVALIDATE procedure

This procedure invalidates a database object and (optionally) modifies its PL/SQL compiler parameter settings. It also invalidates any objects that directly or indirectly depend on the object being invalidated.

Table 8-12 INVALIDATE procedure parameters

Parameter Description

ID number of the object to be invalidated

This equals the value of the OBJECT_ID column from ALL_OBJECTS . If the p_object_id argument is NULL or invalid then the exception inv_not_exist_or_no_priv is raised. The caller of this procedure must have CREATE privilege on the object being invalidated, otherwise the inv_not_exist_or_no_priv exception is raised.

Optional parameter that is ignored if the object specified by p_object_id is not a PL/SQL object

If no value is specified for this parameter, the PL/SQL compiler settings are left unchanged, equivalent to REUSE SETTINGS . If a value is provided, it must specify the values of the PL/SQL compiler settings separated by one or more spaces. If a setting is specified more than once, the inv_malformed_settings exception is raised. The setting values are changed only for the object specified by p_object_id and do not affect dependent objects that may be invalidated. The setting names and values are case insensitive. If a setting is omitted and REUSE SETTINGS is specified, then if a value was specified for the compiler setting in an earlier compilation of this library unit, TimesTen uses that value. If a setting is omitted and REUSE SETTINGS was not specified or no value was specified for the parameter in an earlier compilation, then the database obtains the value for that setting from the session environment.

Optional parameter that defaults to zero (no flags)

Only the inv_error_on_restrictions flag is supported (see «Constants»). With this flag, the subprogram imposes various restrictions on the objects that can be invalidated. For example, the object specified by p_object_id cannot be a table. By default, invalidate quietly returns on these conditions (and does not raise an exception). If the caller sets this flag, the exception inv_restricted_object is raised.

The object type ( object_type column from ALL_OBJECTS ) of the object that is specified by p_object_id must be a PROCEDURE , FUNCTION , PACKAGE , PACKAGE BODY , LIBRARY , OPERATOR , or SYNONYM . If the object is not one of these types and the flag inv_error_on_restrictions is specified in p_option_flags , the exception inv_restricted_object is raised. If inv_error_on_restrictions is not specified in this situation, then no action is taken.

If the object specified by p_object_id is the package specification of STANDARD or DBMS_STANDARD , or the specification or body of DBMS_UTILITY , and if the flag inv_error_on_restrictions is specified in p_option_flags , then the exception inv_restricted_object is raised. If inv_error_on_restrictions is not specified in this situation, then no action is taken.

If the object specified by p_object_id is an object type specification and there are tables that depend on the type, and if the flag inv_error_on_restrictions is specified in p_option_flags , then the exception inv_restricted_object is raised. If inv_error_on_restrictions is not specified, then no action is taken.

Table 8-13 INVALIDATE exceptions

Parameter Description

Raised when the object_id argument value is NULL or invalid, or when the caller does not have CREATE privilege on the object being invalidated.

Raised if a compiler setting is specified more than once in the p_plsql_object_settings parameter.

Raised when different combinations of conditions pertaining to the p_object_id parameter are contravened.

This example invalidates a procedure created in the example in «FORMAT_ERROR_BACKTRACE function». From examining user_objects , you can see information for the procedures created in that example. The following describes user_objects then queries its contents.

To invalidate the P5 procedure, for example, specify object_id 302 in the INVALIDATE call:

This marks the P5 procedure as invalid and sets its PLSQL_OPTIMIZE_LEVEL compiler setting to 2. The values of other compiler settings remain unchanged because REUSE SETTINGS is specified. Note that in addition to P5 being invalidated, any PL/SQL objects that refer to that object are invalidated. Given that Top_With_Logging and Top_Naive call P5 , here are the results of the INVALIDATE call, querying for all user objects that are now invalid:

A user can explicitly recompile and revalidate an object by calling the VALIDATE procedure discussed later in this chapter, or by executing ALTER PROCEDURE , ALTER FUNCTION , or ALTER PACKAGE , as applicable, on the object. Alternatively, each object is recompiled and revalidated automatically the next time it is executed.

IS_BIT_SET function

This function checks the bit setting for the given bit in the given RAW value.

Источник

Adblock
detector

Exception Description

Источник

This article has also been published on Quest’s PL/SQL Pipelines.

It has always been possible in PL/SQL to identify the source of an exception in a block of code; i.e. to know the precise point at which a block of code failed. However, until Oracle 10g, it has not been possible for developers to intervene in the exception in any way whilst retaining this information (for example to record the exception and its source in application logs). This has been the cause of many a frustration for developers.

Finally, with the release of 10g, Oracle has added provision for PL/SQL developers to trap AND log exceptions accurately for the first time. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function gives us the flexibility we have been demanding for years and the information that the DBMS_UTILITY.FORMAT_ERROR_STACK function simply didn’t provide.

The following simple PL/SQL block demonstrates that Oracle will happily tell us where a procedure, function or anonymous block hit an exception.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  END;
  4  /

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 2

There are many PL/SQL developers who consider this to be adequate. They might, for example, take screen scrapes of their scheduling systems’ output as application logs and be satisfied with the level of information demonstrated above. Or perhaps their front-end applications display the error stack as seen above.

Many systems, however, have a requirement to write application logs to files or tables. Therefore, to ensure that the exception is logged, the following «pseudo-approach» is taken by many developers (note that in the simple examples that follow I’ve substituted DBMS_OUTPUT.PUT_LINE for an application logging package).

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( SQLERRM );
  6        RAISE;
  7  END;
  8  /

ORA-00900: invalid SQL statement
BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

The result is that the point in the code where the exception was raised moves to the explicit RAISE call, as can be seen at the end of the error stack above (highlighted in bold). The application logs would now record the fact that an ORA-00900 was raised, but in a scaled-up application, it wouldn’t know which statement hit the exception. This can be a major problem. For example, I recently had to debug another developer’s procedure, which contained 98 separate UPDATE statements and one of them «in the middle somewhere» failed with an invalid number exception. The only way to identify the actual statement was by removing the WHEN OTHERS so Oracle could tell me the correct line number.

As stated earlier, Oracle has supplied the DBMS_UTILITY.FORMAT_ERROR_STACK function for years, but this is of no use in solving this problem, as the following example demonstrates.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK );
  6        RAISE;
  7  END;
  8  /

ORA-00900: invalid SQL statement

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

The DBMS_UTILITY.FORMAT_ERROR_STACK function in this instance provides no information over and above the SQLERRM function used in the previous example. The only difference is that the DBMS_UTILITY.FORMAT_ERROR_STACK function appends a line feed!

The long awaited Oracle enhancement to solve this problem was introduced in the first release of 10g. The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides the error stack all the way back to source. In a simple example such as the following, the output is very simple and provides the accurate information we require.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
  6        RAISE;
  7  END;
  8  /

ORA-06512: at line 2

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

Note, however, that the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function does not supply the error message, just the error’s propagation path. We therefore need to include a call to SQLERRM.

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'garbage';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( SQLERRM );
  6        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
  7        RAISE;
  8  END;
  9  /

ORA-00900: invalid SQL statement
ORA-06512: at line 2

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 7

This example demonstrates that we now have sufficient information for our application logs, while the error stack generated from the RAISE call in line 7 can be discarded (it is included to send the necessary failure signal to the calling program / scheduler / shell).

As the nesting of exception blocks increases, so does the amount of information the new function provides, as the following example demonstrates (note that this time I have not re-raised the exception to keep the output clear).

SQL> CREATE PROCEDURE will_error AS
  2  BEGIN
  3     RAISE PROGRAM_ERROR;
  4  END;
  5  /

Procedure created.

SQL> BEGIN
  2     will_error();
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        DBMS_OUTPUT.PUT_LINE( SQLERRM );
  6        DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
  7  END;
  8  /

ORA-06501: PL/SQL: program error
ORA-06512: at "SCOTT.WILL_ERROR", line 3
ORA-06512: at line 2

At last, we now have a full propagation record of our exception, from its origin through to the outermost caller. Reading the stack from top to bottom, note that the exact points at which the exceptions were encountered are preserved. A major caveat to this is, of course, that if we go back to procedure WILL_ERROR and re-raise the exception in a WHEN OTHERS or such-like, we will once again lose the correct line.

Therefore, an important distinction needs to be made between application code that needs to be logged and that which doesn’t. In my mind it is fairly clear that the various utility packages I include in my overall application will not handle unexpected exceptions in any way. These will be captured and logged by the business-rule packages that process data and need to write to application log files. These processing packages will each contain a call to the new DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function to enable them to log the precise origins and propagation path of an exception. At last!

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, June 2004

Источник

Пример 1. dbms_utility.format_error_backtrace в каждом обработчике исключений

Пример 2. dbms_utility.format_error_backtrace в стартовой процедуре, во всех процедурах есть обработчики исключений

Пример 3. dbms_utility.format_error_backtrace в стартовой процедуре, в других процедурах нет обработчиков исключений

8 DBMS_UTILITY

Us ing DBMS_UTILITY

Security model

Constants

Data types

dblink_array

index_table_type

instance_record

lname_array

name_array

number_array

uncl_array

Exceptions

Sum mary of DBMS_UTILITY subprograms

CANONICALIZE procedure

COMMA_TO_TABLE procedure

COMPILE_SCHEMA procedure

DB_VERSION procedure

FORMAT_CALL_STACK function

FORMAT_ERROR_BACKTRACE function

FORMAT_ERROR_STACK function

GET_CPU_TIME function

GET_DEPENDENCY procedure

GET_ENDIANNESS function

GET_HASH_VALUE function

GET_SQL_HASH function

GET_TIME function

INVALIDATE procedure

IS_BIT_SET function

source code

Читайте также: