Pl sql 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 тегов: , , ,
.

Источник

Obsessed with Oracle PL/SQL

For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?

Search This Blog

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September — 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks.

The three ways are:

SQLERRM — The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely to run into: the error stack will be truncated at 512 bytes, and you might lose some error information.

DBMS_UTILITY.FORMAT_ERROR_STACK — Returns the error message / stack, and will not truncate your string like SQLERRM will.

UTL_CALL_STACK API — Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.

Note: check out this LiveSQL script if you’d like to explore more of the nuances between SQLERRM and DBMS_UTILITY.FORMAT_ERROR_STACK.

Other helpful resources regarding error management in PL/SQL:

Finally, here’s the code from our quiz that you can copy/paste into your editor to check out these alternatives.

Источник

Oracle sql error stack

This chapter explains how to handle PL/SQL compile-time warnings and PL/SQL runtime errors. The latter are called exceptions .

The language of warning and error messages depends on the NLS_LANGUAGE parameter. For information about this parameter, see Oracle Database Globalization Support Guide .

If you have problems creating or running PL/SQL code, check the Oracle Database trace files. The DIAGNOSTIC_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER DIAGNOSTIC_DEST or query the V$DIAG_INFO view. For more information about diagnostic data, see Oracle Database AdministratorвЂ™s Guide .

12.1 Compile-Time Warnings

While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilationвЂ”for example, using a deprecated PL/SQL feature.

To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS or, in the SQL*Plus environment, use the command SHOW ERRORS .

The message code of a PL/SQL warning has the form PLW- nnnnn .

Table 12-1 Compile-Time Warning Categories

Condition might cause unexpected action or wrong results.

Aliasing problems with parameters

Condition might cause performance problems.

Passing a VARCHAR2 value to a NUMBER column in an INSERT statement

Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable.

Code that can never run

By setting the compilation parameter PLSQL_WARNINGS , you can:

Enable and disable all warnings, one or more categories of warnings, or specific warnings

Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit)

You can set the value of PLSQL_WARNINGS for:

Your Oracle database instance

Use the ALTER SYSTEM statement, described in Oracle Database SQL Language Reference .

Use the ALTER SESSION statement, described in Oracle Database SQL Language Reference .

A stored PL/SQL unit

Use an ALTER statement from «ALTER Statements» with its compiler_parameters_clause .

In any of the preceding ALTER statements, you set the value of PLSQL_WARNINGS with this syntax:

For the syntax of value_clause , see Oracle Database Reference .

To display the current value of PLSQL_WARNINGS , query the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS .

Oracle Database Reference for more information about the static data dictionary view ALL_PLSQL_OBJECT_SETTINGS

Oracle Database Error Messages Reference for the message codes of all PL/SQL warnings

Oracle Database Reference for more information about the static data dictionary view *_ERRORS

«PL/SQL Units and Compilation Parameters» for more information about PL/SQL units and compiler parameters

Example 12-1 Setting Value of PLSQL_WARNINGS Compilation Parameter

This example shows several ALTER statements that set the value of PLSQL_WARNINGS .

For the session, enable all warningsвЂ”highly recommended during development:

For the session, enable PERFORMANCE warnings:

For the procedure loc_var , enable PERFORMANCE warnings, and reuse settings:

For the session, enable SEVERE warnings, disable PERFORMANCE warnings, and treat PLW-06002 warnings as errors:

For the session, disable all warnings:

12.1.1 DBMS_WARNING Package

If you are writing PL/SQL units in a development environment that compiles them (such as SQL*Plus), you can display and set the value of PLSQL_WARNINGS by invoking subprograms in the DBMS_WARNING package.

Example 12-2 uses an ALTER SESSION statement to disable all warning messages for the session and then compiles a procedure that has unreachable code. The procedure compiles without warnings. Next, the example enables all warnings for the session by invoking DBMS_WARNING.set_warning_setting_string and displays the value of PLSQL_WARNINGS by invoking DBMS_WARNING.get_warning_setting_string . Finally, the example recompiles the procedure, and the compiler generates a warning about the unreachable code.

Unreachable code could represent a mistake or be intentionally hidden by a debug flag.

DBMS_WARNING subprograms are useful when you are compiling a complex application composed of several nested SQL*Plus scripts, where different subprograms need different PLSQL_WARNINGS settings. With DBMS_WARNING subprograms, you can save the current PLSQL_WARNINGS setting, change the setting to compile a particular set of subprograms, and then restore the setting to its original value.

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_WARNING package

Example 12-2 Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms

Disable all warning messages for this session:

With warnings disabled, this procedure compiles with no warnings:

Enable all warning messages for this session:

Check warning setting:

12.2 Overview of Exception Handling

Exceptions (PL/SQL runtime errors) can arise from design faults, coding mistakes, hardware failures, and many other sources. You cannot anticipate all possible exceptions, but you can write exception handlers that let your program to continue to operate in their presence.

Any PL/SQL block can have an exception-handling part, which can have one or more exception handlers. For example, an exception-handling part could have this syntax:

In the preceding syntax example, ex_name_ n is the name of an exception and statements_ n is one or more statements. (For complete syntax and semantics, see «Exception Handler» .)

When an exception is raised in the executable part of the block, the executable part stops and control transfers to the exception-handling part. If ex_name_1 was raised, then statements_1 run. If either ex_name_2 or ex_name_3 was raised, then statements_2 run. If any other exception was raised, then statements_3 run.

After an exception handler runs, control transfers to the next statement of the enclosing block. If there is no enclosing block, then:

If the exception handler is in a subprogram, then control returns to the invoker, at the statement after the invocation.

If the exception handler is in an anonymous block, then control transfers to the host environment (for example, SQL*Plus)

If an exception is raised in a block that has no exception handler for it, then the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a block has a handler for it or there is no enclosing block (for more information, see «Exception Propagation» ). If there is no handler for the exception, then PL/SQL returns an unhandled exception error to the invoker or host environment, which determines the outcome (for more information, see «Unhandled Exceptions» ).

12.2.1 Exception Categories

The exception categories are:

The runtime system raises internally defined exceptions implicitly (automatically). Examples of internally defined exceptions are ORA-00060 (deadlock detected while waiting for resource) and ORA-27102 (out of memory).

An internally defined exception always has an error code, but does not have a name unless PL/SQL gives it one or you give it one.

A predefined exception is an internally defined exception that PL/SQL has given a name. For example, ORA-06500 (PL/SQL: storage error) has the predefined name STORAGE_ERROR .

You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package. For example, you might declare an exception named insufficient_funds to flag overdrawn bank accounts.

You must raise user-defined exceptions explicitly.

Table 12-2 summarizes the exception categories.

Table 12-2 Exception Categories

Category Description Example

Only if you assign one

Only if you assign one

For a named exception, you can write a specific exception handler, instead of handling it with an OTHERS exception handler. A specific exception handler is more efficient than an OTHERS exception handler, because the latter must invoke a function to determine which exception it is handling. For details, see «Retrieving Error Code and Error Message» .

12.2.2 Advantages of Exception Handlers

Using exception handlers for error-handling makes programs easier to write and understand, and reduces the likelihood of unhandled exceptions.

Without exception handlers, you must check for every possible error, everywhere that it might occur, and then handle it. It is easy to overlook a possible error or a place where it might occur, especially if the error is not immediately detectable (for example, bad data might be undetectable until you use it in a calculation). Error-handling code is scattered throughout the program.

With exception handlers, you need not know every possible error or everywhere that it might occur. You need only include an exception-handling part in each block where errors might occur. In the exception-handling part, you can include exception handlers for both specific and unknown errors. If an error occurs anywhere in the block (including inside a sub-block), then an exception handler handles it. Error-handling code is isolated in the exception-handling parts of the blocks.

In Example 12-3, a procedure uses a single exception handler to handle the predefined exception NO_DATA_FOUND , which can occur in either of two SELECT INTO statements.

If multiple statements use the same exception handler, and you want to know which statement failed, you can use locator variables, as in Example 12-4.

You determine the precision of your error-handling code. You can have a single exception handler for all division-by-zero errors, bad array indexes, and so on. You can also check for errors in a single statement by putting that statement inside a block with its own exception handler.

Example 12-3 Single Exception Handler for Multiple Exceptions

Invoke procedure (there is a DEPARTMENTS table, but it does not have a LAST_NAME column):

Invoke procedure (there is no EMP table):

Example 12-4 Locator Variables for Statements that Share Exception Handler

12.2.3 Guidelines for Avoiding and Handling Exceptions

To make your programs as reliable and safe as possible:

Use both error-checking code and exception handlers.

Use error-checking code wherever bad input data can cause an error. Examples of bad input data are incorrect or null actual parameters and queries that return no rows or more rows than you expect. Test your code with different combinations of bad input data to see what potential errors arise.

Sometimes you can use error-checking code to avoid raising an exception, as in Example 12-7.

Add exception handlers wherever errors can occur.

Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors can also arise from problems that are independent of your codeвЂ”for example, disk storage or memory hardware failureвЂ”but your code still must take corrective action.

Design your programs to work when the database is not in the state you expect.

For example, a table you query might have columns added or deleted, or their types might have changed. You can avoid problems by declaring scalar variables with %TYPE qualifiers and record variables to hold query results with %ROWTYPE qualifiers.

Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers.

Learn the names and causes of the predefined exceptions. If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them.

Have your exception handlers output debugging information.

If you store the debugging information in a separate table, do it with an autonomous routine, so that you can commit your debugging information even if you roll back the work that the main subprogram did. For information about autonomous routines, see «AUTONOMOUS_TRANSACTION Pragma» .

For each exception handler, carefully decide whether to have it commit the transaction, roll it back, or let it continue.

Regardless of the severity of the error, you want to leave the database in a consistent state and avoid storing bad data.

Avoid unhandled exceptions by including an OTHERS exception handler at the top level of every PL/SQL program.

Make the last statement in the OTHERS exception handler either RAISE or an invocation of of a subroutine marked with SUPPRESSES_WARNING_6009 pragma. (If you do not follow this practice, and PL/SQL warnings are enabled, then you get PLW-06009.) For information about RAISE or an invocation of the RAISE_APPLICATION_ERROR , see «Raising Exceptions Explicitly» .

12.3 Internally Defined Exceptions

Internally defined exceptions (ORA- n errors) are described in Oracle Database Error Messages Reference . The runtime system raises them implicitly (automatically).

An internally defined exception does not have a name unless either PL/SQL gives it one (see «Predefined Exceptions» ) or you give it one.

If you know that your database operations might raise specific internally defined exceptions that do not have names, then give them names so that you can write exception handlers specifically for them. Otherwise, you can handle them only with OTHERS exception handlers.

To give a name to an internally defined exception, do the following in the declarative part of the appropriate anonymous block, subprogram, or package. (To determine the appropriate block, see «Exception Propagation» .)

Declare the name.

An exception name declaration has this syntax:

For semantic information, see «Exception Declaration» .

Associate the name with the error code of the internally defined exception.

For semantic information, see «EXCEPTION_INIT Pragma» .

An internally defined exception with a user-declared name is still an internally defined exception, not a user-defined exception.

Example 12-5 gives the name deadlock_detected to the internally defined exception ORA-00060 (deadlock detected while waiting for resource) and uses the name in an exception handler.

Example 12-5 Naming Internally Defined Exception

12.4 Predefined Exceptions

Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD . The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.

Table 12-3 lists the names and error codes of the predefined exceptions.

Источник

Adblock
detector

Category Definer Has Error Code Has Name Raised Implicitly Raised Explicitly

Источник

Ваша программа компилируется успешно, но в ходе выполнения тестового сценария происходит сбой: в программе где-то допущена ошибка. Как найти ее причину? Конечно, можно сразу взяться за отладчик (практически в любом редакторе PL/SQL имеется визуальный отладчик с возможностью установки точек прерывания и отслеживания состояния данных), но сначала стоит провести трассировку выполнения программы. Прежде чем рассматривать возможности трассировки, стоит сказать пару слов о различиях между отладкой и трассировкой. Разработчики часто смешивают эти два понятия, однако это далеко не одно и то же. В двух словах: вы сначала выполняете трассировку для получения расширенной информации о поведения приложения, которая помогает выявить источник проблем, а затем используете отладчик для поиска конкретных строк кода, порождающих ошибку.

Ключевое различие между трассировкой и отладкой заключается в том, что трассировка выполняется в «пакетном» режиме, а отладка является интерактивным процессом. Я включаю режим трассировки и выполняю код своего приложения. Когда его выполнение завершится, я открываю журнал и использую полученную информацию для проведения сеанса отладки. Во время отладки я выполняю код строку за строкой (обычно начиная с точки прерывания, близкой к источнику проблемы). Сеансы отладки обычно получаются слишком долгими и утомительными, поэтому стоит сделать все возможное, чтобы свести к минимуму время, проведенное за отладкой. Основательная упреждающая трассировка поможет вам в этом.

Каждое приложение должно включать определенные программистом трассировочные вызовы. В этом разделе представлены основные средства трассировки PL/SQL, но сначала нужно упомянуть принципы, которые должны соблюдаться при реализации трассировки:

Трассировочные вызовы должны оставаться в коде на протяжении всех фаз разработки и развертывания. Иначе говоря, трассировочные вызовы, вставленные в ходе разработки, не следует удалять при переходе приложения в фазу реальной эксплуатации. Трассировка часто помогает понять, что происходит с вашим приложением при выполнении в реальной среде.
Сведите затраты на вызов трассировочных средств к абсолютному минимуму. При отключенной трассировке их присутствие в коде не должно никак отражаться на быстродействии приложения.
Не вызывайте программу DBMS_OUTPUT.PUT_LINE для выполнения трассировки прямо в коде приложения. Эта встроенная программа не обладает достаточной гибкостью или мощью для качественной трассировки.
Вызовите DBMS_UTILITY.FORMAT_CALL_STACK или подпрограмму UTL_CALL_STACK (12c и выше) для сохранения стека вызовов в трассировочных данных.
Обеспечьте возможность простого включения и отключения трассировки в вашем коде. Включение трассировки не должно требовать вмешательства службы поддержки. Также не следует поставлять отдельную версию приложения с включенной трассировкой.
Если кто-то уже создал механизм трассировки, который вы можете использовать в своей работе (и который соответствует этим принципам), не тратьте время на создание собственного механизма трассировки.

Начнем с последнего принципа. Какие средства трассировки PL/SQL существуют в настоящее время?

DBMS_APPLICATION_INFO — этот встроенный пакет предоставляет API, при помощи которого приложения «регистрируют» свое текущее состояние выполнения в базе данных Oracle. Трассировочная информация записывается в динамические представления V$. Подробное описание приводится в следующем разделе.
Log4PLSQL — инфраструктура с открытым исходным кодом, построенная по образцу (и на основе) log4J, очень популярного механизма протоколирования для языка Java.
opp_trace — этот пакет, доступный на сайте книги, предоставляет простую, но эффективную функциональность трассировки.
DBMS_TRACE — это встроенное средство обеспечивает трассировку кода PL/SQL, но не позволяет включать в результаты какие-либо данные приложения. С другой стороны, DBMS_TRACE может использоваться без модификации исходного кода программы.

Вы также можете воспользоваться одним из встроенных профайлеров PL/SQL для получения информации о профилях производительности каждой строки и подпрограммы вашего приложения.

DBMS_UTILITY.FORMAT_CALL_STACK

Функция DBMS_UTILITY.FORMAT_CALL_STACK возвращает отформатированную строку с содержимым стека вызовов: последовательностью вызовов процедур или функций, приведшей к точке вызова функции. Иначе говоря, эта функция отвечает на вопрос: «Как я сюда попал?»

Следующий пример демонстрирует использование этой функции и показывает, как выглядит отформатированная строка:


SQL> CREATE OR REPLACE PROCEDURE proc1
   2   IS
   3   BEGIN
   4      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);
   5   END;
   6   /

Procedure created.

SQL> CREATE OR REPLACE PACKAGE pkg1
   2   IS
   3      PROCEDURE proc2;
   4   END pkg1;
   5   /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg1
   2   IS
   3      PROCEDURE proc2
   4      IS
   5      BEGIN
   6         proc1;
   7      END;
   8   END pkg1;
   9   /

Package body created.

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   /

Procedure created.

SQL> BEGIN
   2      proc3;
   3   END;
   4   /
----- PL/SQL Call Stack -----
         object      line object
         handle      number name
000007FF7EA83240          4 procedure HR.PROC1
000007FF7E9CC3B0          6 package body HR.PKG1
000007FF7EA0A3B0          9 procedure HR.PROC3
000007FF7EA07C00          2 anonymous block

О чем необходимо помнить при использовании dbms_utility.format_call_stack:

При вызове подпрограммы из пакета в отформатированном стеке вызовов выводится только имя пакета, но не имя подпрограммы (после того, как код был откомпилирован, все имена «теряются», то есть становятся недоступными для исполнительного ядра).
Чтобы просмотреть фактический исполняемый код, создайте запрос к ALL_SOURCE с именем программного модуля и номером строки.
Когда вы отслеживаете процесс выполнения (см. подробности далее) или регистрируете ошибки, вызовите эту функцию и сохраните вывод в журнале для последующего просмотра и анализа.
Разработчики уже давно просили Oracle предоставить средства для разбора строки, и в Oracle Database 12c наконец-то появился новый пакет UTL_CALL_STACK. (Если вы еще не перешли на версию 12.1 и выше, обратитесь к файлу callstack.pkg — он предоставляет аналогичную функциональность.)

UTL_CALL_STACK (Oracle Database 12c)

Oracle Database 12c предоставляет пакет UTL_CALL_STACK для получения информации о выполняемых подпрограммах. Хотя имя пакета создает впечатление, что пакет только предоставляет информацию о стеке вызовов, также предоставляется доступ к стеку ошибок и стеку трассировки.

Каждый стек обладает глубиной (индикатором позиции в стеке); вы можете запросить информацию на определенной глубине всех трех видов стеков, с которыми работает пакет. Одним из главных усовершенствований utl_call_stack по сравнению с dbms_utility. FORMAT_CALL_STACK стала возможность получения уточненных имен с именами программных модулей, всех лексических родителей подпрограммы и имени самой подпрограммы. Впрочем, для стека трассировки эта дополнительная информация недоступна.

Ниже приведена таблица подпрограмм пакета с парой примеров.

Имя	Описание
BACKTRACE_DEPTH	Возвращает количество элементов в стеке трассировки
BACKTRACE_LINE	Возвращает номер строки для заданной глубины стека трассировки
BACKTRACE_UNIT	Возвращает имя программного модуля для заданной глубины стека трассировки
CONCATENATE_SUBPROGRAM	Возвращает полное имя, полученное посредством конкатенации
DYNAMIC_DEPTH	Возвращает количество подпрограмм в стеке вызовов, включая SQL, Java и другие не относящиеся к PL/SQL контексты. Например, если A вызывает B, затем B вызывает C и C вызывает B, то стек будет выгля- деть следующим образом (внизу выводится динамическая глубина): A B C B 4 3 2 1
ERROR_DEPTH	Возвращает количество ошибок в стеке вызовов
ERROR_MSG	Возвращает сообщение об ошибке для заданной глубины стека ошибок
ERROR_NUMBER	Возвращает код ошибки для заданной глубины стека ошибок
LEXICAL_DEPTH	Возвращает лексическую глубину вложенности подпрограммы для заданной динамической глубины
OWNER	Возвращает имя владельца программного модуля подпрограммы на заданной динамической глубине
SUBPROGRAM	Возвращает полное имя подпрограммы для заданной динамической глубины
UNIT_LINE	Возвращает номер строки модуля для подпрограммы с заданной динамической глубиной

Несколько типичных применений UTL_CALL_STACK:

Получение полного имени подпрограммы. Вызов utl_call_stack.subprogram возвращает массив строк; функция concatenate_subprogram получает этот массив и возвращает одну строку с именами, разделенными точками. В следующем фрагменте при вызове subprogram передается 1, потому что я хочу получить информацию о программе на вершине стека — текущей выполняемой подпрограмме:

CREATE OR REPLACE PACKAGE pkgl 
IS
   PROCEDURE proc1;
END pkg1;
/

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 
   IS
      PROCEDURE nested_in_proc1
      IS
      BEGIN
         DBMS_OUTPUT.put_line (
            utl_call_stack.concatenate_subprogram (utl_call_stack.subprogram (1)));
      END;
   BEGIN
      nested in proc1; 
   END;
END pkg1;
/
	
BEGIN
   pkg1.proc1;
END;
/
	
PKG1.PROC1.NESTED IN PROC1

Вывод имени программного модуля и номера строки позиции, в которой было инициировано текущее исключение. Сначала создается функция backtrace_to, которая «скрывает» вызовы подпрограмм utl_call_stack. Во всех вызовах backtrace_ unit и backtrace_line передается значение, возвращаемое функцией error_depth. Значение глубины для ошибок отличается от значения для стека вызовов: в стеке вызовов 1 соответствует вершине стека (текущей выполняемой подпрограмме). В стеке ошибок позиция кода, в которой была инициирована ошибка, находится на глубине error_depth, а не на глубине 1:


CREATE OR REPLACE FUNCTION backtrace_to
   RETURN VARCHAR2
IS
BEGIN
   RETURN utl_call_stack.backtrace_unit (utl_call_stack.error_depth)
         || ' line '
         || utl_call_stack.backtrace_line (utl_call_stack.error_depth);
END;

/
CREATE OR REPLACE PACKAGE pkg1
IS
   PROCEDURE proc1;
   PROCEDURE proc2;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg1
IS
   PROCEDURE proc1
   IS
      PROCEDURE nested_in_proc1
      IS
      BEGIN
         RAISE VALUE_ERROR;
      END;
   BEGIN
      nested_in_proc1;
   END;
   PROCEDURE proc2
   IS
   BEGIN
      proc1;
   EXCEPTION
      WHEN OTHERS THEN RAISE NO_DATA_FOUND;
   END;
END pkg1;
/
CREATE OR REPLACE PROCEDURE proc3
IS
BEGIN
   pkg1.proc2;
END;
/
BEGIN
   proc3;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (backtrace_to);
END;
/

HR.PKG1 line 19

Несколько замечаний по поводу использования UTL_CALL_STACK:

Оптимизации компилятора могут изменять глубину (лексическую, динамическую и глубину трассировки), поскольку процесс оптимизации может привести к пропуску вызовов подпрограмм.
Не поддерживаются вызовы UTL_CALL_STACK за границами RPC (Remote Procedure Call). Например, если procl вызывает удаленную процедуру remoteproc2, то remoteproc2 не сможет получить информацию о procl с использованием UTL_CALL_STACK.
Информация о лексических элементах предоставляется средствами условной компиляции PL/SQL, а не через utl_call_stack.

Пакет UTL_CALL_STACK чрезвычайно удобен, но для повседневного использования вам, скорее всего, придется построить собственные инструменты на базе подпрограмм пакета. Я написал вспомогательный пакет (см. файлы 12c_utl_call_stack_helper.sql и 12c_utl_call_stack_helper_demo.sql) с программами, которые, как мне кажется, могут вам пригодиться.

DBMS_APPLICATION_INFO

Встроенный пакет DBMS_APPLICATION_INFO предоставляет API для «регистрации» текущего состояния выполнения приложения в базе данных Oracle. Для внешнего наблюдения за сохраненной информацией о состоянии приложения используются виртуальные таблицы V$. Именно использование виртуальных таблиц V$ в качестве хранилища трассировочных данных отличает этот пакет от других решений.

Регистрация приложений средствами DBMS_APPLICATION_INFO открывает гораздо больше возможностей для анализа приложений на предмет производительности и потребления ресурсов, чем все остальные решения. Это открывает дополнительные возможности для оптимизации приложений и более точной оценки эксплуатационных затрат.

В следующей таблице перечислены подпрограммы этого пакета. Все они являются процедурами и не могут выполняться в коде SQL.

Имя	Описание
DBMS_APPLICATION_INFO.SET_MODULE	Назначает имя выполняемого модуля
DBMS_APPLICATION_INFO.SET_ACTION	Назначает действие внутри модуля
DBMS_APPLICATION_INFO.READ_MODULE	Получает информацию о модуле и действии для текущего сеанса
DBMS_APPLICATION_INFO.SET_CLIENT_INFO	Назначает информацию о клиенте для сеанса
DBMS_APPLICATION_INFO.READ_CLIENT_INFO	Читает информацию о клиенте для сеанса
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS	Записывает строку в таблицу LONGOPS (только в версии 8.0)

Пример использования пакета DBMS_APPLICATION_INFO:


PROCEDURE drop_dept (
   deptno_IN IN employees.department_id%TYPE 
      , reassign_deptno_IN IN employees.department_id%TYPE
)
IS
   l_count PLS_INTEGER;
BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE
      (module_name => 'DEPARTMENT FIXES' 
      ,action_name => null);
   DBMS_APPLICATION_INFO.SET_ACTION (action_name => 'GET COUNT IN DEPT');

   SELECT COUNT(*)
      INTO l_count 
      FROM employees
    WHERE department_id = deptno_IN;
   DBMS_OUTPUT.PUT_LINE ('Reassigning ' || l_count || ' employees');

   IF l_count > 0 
   THEN
      DBMS_APPLICATION_INFO.SET_ACTION (action_name => 'REASSIGN EMPLOYEES');

      UPDATE employees
         SET department_id = reassign_deptno_IN 
      WHERE department_id = deptno_IN;
   END IF;

   DBMS_APPLICATION_INFO.SET_ACTION (action_name => 'DROP DEPT');

   DELETE FROM departments WHERE department_id = deptno_IN;

   COMMIT;

   DBMS_APPLICATION_INFO.SET_MODULE(null,null);

EXCEPTION
   WHEN OTHERS THEN
      DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END drop_dept;

Обратите внимание на три вызова DBMS_APPLICATION_INFO, определяющие три этапа процесса удаления. Это позволяет отслеживать состояние приложения с чрезвычайно высоким уровнем детализации.

Проследите за тем, чтобы имя действия описывало текущую транзакцию или логическую единицу работы внутри модуля.

При завершении транзакции следует вызвать процедуру DBMS_APPLICATION_INFO.SET_ACTION, передавая NULL в качестве значения параметра action_name. Это гарантирует, что последующие транзакции, не зарегистрированные средствами DBMS_APPLICATION_INFO, не будут ошибочно включены в текущее действие. Например, если программа обрабатывает исключения, обработчик исключения, вероятно, должен сбрасывать информацию о текущем действии.

Трассировка с использованием opp_trace

Программа opp_trace доступна на сайте 5-го издания этой книги. Она реализует мощный механизм трассировки, позволяющий направить выходные данные на экран или в таблицу (opp_trace). Выполните сценарий opp_trace.sql, чтобы создать объекты базы данных (для удаления этих объектов используется сценарий opp_trace_uninstall.sql). Читателю разрешается вносить необходимые изменения в код opp_trace для использования его в вашей рабочей среде.

Включение трассировки всех вызовов через opp_trace API происходит так:

opp_trace.turn_on_trace;

В следующем вызове set_tracing трассировка включается только для контекстов, содержащих строку «balance»:

opp_trace.turn_on_trace ('balance');

Теперь посмотрим, как совершаются вызовы opp_trace.trace_activity в ваших хранимых программах.

Процедура q$error_manager.trace почти никогда не вызывается напрямую. Вместо этого ее вызов вкладывается в вызов q$error_manager.trace_enabled, как показано в следующем фрагменте:

IF opp_trace.trace_is_on 
THEN
   opp_trace.trace_activity (
         context_in => 'generate_test_code for program'
         , data_in => qu_program_qp.name_for_id (l_program_key)
   );
END IF;

Такой способ вызова программы trace сводит к минимуму затраты на трассировку. Функция trace_enabled возвращает одно логическое значение; она не передает фактических аргументов и завершает свою работу эффективно. Если функция возвращает TRUE, то база данных Oracle вычисляет все выражения в списке параметров и вызывает процедуру trace, которая также проверит, что для этого конкретного контекста трассировка включена.

Если вызвать процедуру trace напрямую в коде приложения, то при каждом переходе к этой строке исполнительное ядро будет вычислять все фактические аргументы в списке параметров и вызывать процедуру trace, которая будет проверять, что трассировка включена для этого контекста. Если трассировка отключена, то ничего не происходит, — но приложение уже затратило процессорное время на вычисление аргументов и их передачу trace.

Заметит ли пользователь лишние затраты на избыточное вычисление аргументов? Вероятно, не заметит, но с добавлением новых трассировочных вызовов повышается вероятность того, что они повлияют на ощущения пользователя от работы с приложением. Лучше заведите привычку всегда скрывать трассировочные вызовы в команде IF, которая сводит лишние затраты к минимуму. Я использую специальный фрагмент кода, чтобы вставка команды IF с шаблонным вызовом не требовала ввода большого количества символов.

Пакет DBMS_TRACE

Встроенный пакет DBMS_TRACE предоставляет средства для запуска и остановки трассировки PL/SQL в ходе сеанса. При включенной трассировке ядро собирает данные в ходе выполнения программы. Собранная информация выводится в файл на сервере.

Файл, полученный в результате трассировки, содержит информацию о конкретных шагах, выполняемых вашим кодом. DBMS_PR0FILER и DBMS_HPROF (иерархический профайлер), позволяют провести более полный анализ вашего приложения с получением хронометражной информации и данных о количестве выполнений конкретной строки.

Установка пакета DBMS_TRACE

Этот пакет не всегда автоматически устанавливается вместе со всеми остальными встроенными пакетами. Чтобы определить, установлен ли он в вашей системе, подключитесь к базе данных в качестве пользователя с учетной записью SYS (или другой учетной записью с привилегиями SYSDBA) и выполните в SQL*Plus команду:

DESCRIBE DBMS_TRACE

Если на экране появится сообщение об ошибке:

ORA-04043: object dbms_trace does not exist

значит, пакет нужно установить. Для этого, используя ту же учетную запись, выполните следующие файлы в указанном порядке:

$ORACLE_HOME/rdbms/admin/dbmspbt.sql
$ORACLE_HOME/rdbms/admin/prvtpbt.plb

Программы пакета DBMS_TRACE

Пакет DBMS_TRACE содержит три программы.

Имя	Описание
SET_PLSQL_TRACE	Активизирует трассировку в текущем сеансе
CLEAR_PLSQL_TRACE	Останавливает трассировку в текущем сеансе
PLSQL_TRACE_VERSION	Возвращает основной и дополнительный номера версий пакета DBMS_TRACE

Для трассировки кода PL/SQL в текущем сеансе выполняется следующий вызов:

DBMS_TRACE.SET_PLSQL_TRACE (уровень_трассировки INTEGER);

Здесь значением аргумента уровень трассировки служит одна из следующих констант:

Константы, определяющие, какие элементы программы PL/SQL подлежат трассировке:

DBMS_TRACE.trace_all_calls            constant INTEGER := 1;
DBMS_TRACE.trace_enabled_calls        constant INTEGER := 2;
DBMS_TRACE.trace_all_exceptions       constant INTEGER := 4;
DBMS_TRACE.trace_enabled_exceptions   constant INTEGER := 8;
DBMS_TRACE.trace_all_sql              constant INTEGER := 32;
DBMS_TRACE.trace_enabled_sql          constant INTEGER := 64;
DBMS_TRACE.trace_all_lines            constant INTEGER := 128;
DBMS_TRACE.trace_enabled_lines        constant INTEGER := 256;

Константы, управляющие процессом трассировки:

DBMS_TRACE.trace_stop          constant INTEGER := 16384;
DBMS_TRACE.trace_pause         constant INTEGER := 4096;
DBMS_TRACE.trace_resume        constant INTEGER := 8192;
DBMS_TRACE.trace_limit         constant INTEGER := 16;

Комбинации констант из пакета DBMS_TRACE активизируют одновременную трассировку нескольких элементов языка PL/SQL. Учтите, что константы, управляющие процессом трассировки (например, DBMS_TRACE.trace_pause), не должны использоваться в сочетании с другими константами (такими, как DBMS_TRACE. trace_enabled_calls).

Трассировка всех программ, выполняемых в текущем сеансе, активизируется следующим вызовом:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_calls);

Трассировка всех исключений, инициируемых в течение текущего сеанса, активизируется следующим образом:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_all_exceptions);

Далее запускается программный код. Трассировка прекращается вызовом

DBMS_TRACE.CLEAR_PLSQL_TRACE;

После этого можно проанализировать содержимое файла трассировки. Имена файлов генерируются Oracle; нужный файл легко находится по дате модификации. О том, где эти файлы хранятся, рассказывается далее, в разделе «Формат собранных данных». Вы также можете установить идентификатор в файле трассировки, чтобы упростить его поиск в будущем:

SQL> alter session set tracefile_identifier = 'hello_steven!';
Session altered.
SQL> select tracefile from v$process where tracefile like '%hello_steven!%';
TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24446_hello_steven!.trc

Учтите, что трассировка PL/SQL не может использоваться на серверах коллективного доступа (ранее называвшихся многопоточными серверами, MTS).

Управление содержимым файла трассировки

Файлы, генерируемые пакетом DBMS_TRACE, могут быть очень большими. Чтобы уменьшить объем выходных данных, следует трассировать не все подряд, а только конкретные программы (этот прием не может использоваться с удаленными вызовами RPC). Чтобы включить трассировку всех программ, созданных или измененных в ходе сеанса, измените настройки сеанса следующей командой:

ALTER SESSION SET PLSQL_DEBUG=TRUE;

Если вы не хотите менять конфигурацию всего сеанса, перекомпилируйте конкретный программный модуль в режиме отладки следующим образом (способ неприменим к анонимным блокам):

ALTER [PROCEDURE | FUNCTION | PACKAGE BODY] имя_программы COMPILE DEBUG;

Затем инициируйте трассировку только этих программных модулей:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_calls);

Трассировку также можно ограничить только теми исключениями, которые инициируются в доступных для данного процесса программах:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_enabled_exceptions);

Если вы установили два уровня трассировки (для всех программ или исключений, а также для некоторых программ и исключений), будет действовать уровень «для всех».

Приостановление и возобновление трассировки

Процедура SET_PLSQL_TRACE способна не только определять информацию, подлежащую трассировке. С ее помощью также можно приостанавливать и возобновлять процесс трассировки. Например, следующая команда приостанавливает сбор данных вплоть до последующего возобновления трассировки:

DBMS_TRACE.SET_PLSQL_TRACE (DBMS_TRACE.trace_pause);

Пакет DBMS_TRACE включает в файл трассировки записи с информацией о приостановлении и возобновлении трассировки.

При помощи константы DBMS_TRACE.trace_limit можно указать, что в файле трассировки должна сохраняться информация только о 8192 трассируемых событиях. Этот подход защищает базу данных от переполнения трассировочной информацией. По завершении сеанса трассировки будут сохранены лишь 8192 последние записи.

Формат собранных данных

Если вы запросили трассировку только для определенных программных модулей, но не текущей программы, данные трассировки сохраняться не будут. Если же включена трассировка текущей программы, в файл трассировки записываются ее тип, имя и глубина стека. При трассировке исключений определяется номер строки программы, в процессе выполнения которой оно было инициировано, его тип (пользовательское или предопределенное) и в случае предопределенного исключения — его номер (номер ошибки, связанной с пользовательским исключением, всегда равен 1).

Пример выходных данных трассировки для процедуры showemps:

Пример выходных данных трассировки для процедуры showemps:
*** 1999.06.14.09.59.25.394
*** SESSION ID:(9.7) 1999.06.14.09.59.25.344
------------PL/SQL TRACE INFORMATION------------- 	
Levels set :	1
Trace:   ANONYMOUS BLOCK: Stack depth = 1
Trace:    PROCEDURE SCOTT.SHOWEMPS: Call to entry at line 5 Stack depth = 2
Trace:      PACKAGE BODY SYS.DBMS_SQL: Call to entry at line 1 Stack depth = 3
Trace:       PACKAGE BODY SYS.DBMS_SYS_SQL: Call to entry at line 1 Stack depth = 4
Trace:       PACKAGE BODY SYS.DBMS_SYS_SQL: ICD vector index = 21 Stack depth = 4
Trace:      PACKAGE PLVPRO.P: Call to entry at line 26 Stack depth = 3
Trace:      PACKAGE PLVPRO.P: ICD vector index = 6 Stack depth = 3
Trace:      PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
Trace:      PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 3
Trace:       PACKAGE BODY PLVPRO.P: Call to entry at line 1 Stack depth = 4

Отладка программ PL/SQL

Тестирование программы PL/SQL — это поиск ошибок в программном коде, а ее отладка — это определение и устранение причин, обусловивших их появление. Это два совершенно разных процесса, которые не следует путать. Протестировав программу и выявив ошибки, разработчик должен их исправить. С этого момента и начинается отладка.

Многие программисты считают отладку самой трудной частью программирования. Причины ее сложности часто заключаются в следующем:

Недостаточное понимание задачи, решаемой программой. Большинство разработчиков предпочитают заниматься лишь написанием программного кода и не любят тратить время на то, чтобы разбираться в спецификации. Вероятность того, что написанная таким образом программа будет соответствовать требованиям, ничтожно мала.
Плохой стиль программирования. Трудные для чтения программы (плохо документированные, содержащие слишком обширную документацию, плохо отформатированные, с неудачными именами переменных и т. п.), а также программы, неправильно разделенные на модули, отлаживать гораздо сложнее, чем хорошо спроектированный и структурированный код.
Большое количество ошибок в программе. Если разработчик не имеет необходимых навыков анализа и программирования, скорее всего, его код будет содержать множество ошибок. Без четко определенного, пошагового метода исправления ошибок вы, скорее всего, не устоите под их напором.
Слабые навыки отладки. Существует много разных подходов к выявлению ошибок. Некоторые из них только затрудняют работу, поэтому неумение правильно выбрать технологию отладки оборачивается лишними затратами времени, головной болью и недовольством начальства.

В следующих разделах рассматриваются методы отладки, которыми лучше не пользоваться, а также методы, способные значительно облегчить этот процесс и сделать его более эффективным.

Неправильные способы организации отладки в PL/SQL

Вероятно, при виде приведенных ниже описаний почти каждый читатель скажет себе: «Ну разумеется, это же очевидно. Конечно, так поступать нельзя. Я никогда такого не сделаю».

И все же в следующий раз, сев за работу, многие из вас запросто могут последовать этим, таким очевидно неправильным методам отладки.

Надеюсь, если вы хотя бы отчасти узнаете себя в этих описаниях, это поможет вам изменить свой подход к делу.

Хаотичная отладка

Столкнувшись с ошибкой, вы начинаете бурную деятельность по поиску причины ее возникновения. И хотя сам факт ошибки может свидетельствовать о том, что задача плохо проанализирована или что не найден наиболее удачный метод ее решения, вы не пытаетесь разобраться в программе. Вместо этого вы включаете в программу множество команд MESSAGE (в Oracle Forms), SRW.MESSAGE (в Oracle Reports) или DBMS_OUTPUT.PUT_LINE (в хранимых модулях) и надеетесь, что это поможет.

Причем вы даже не сохраняете копию программы перед внесением изменений, чтобы не тратить лишнее время. Вам, как обычно, некогда, поэтому вы уверены, что ответы на все вопросы появятся сами собой, после чего можно будет удалить отладочные команды.

В результате масса времени тратится на просмотр ненужной информации. Вы проверяете все подряд, хотя большинство используемых конструкций давно выверены и отлажены.

Вы пропускаете обед, чтобы не отвлекаться от работы, поглощаете кофе, пытаясь максимально сконцентрировать свое внимание. Даже не имея ни малейшего понятия о причине возникновения проблемы, вы думаете, что если попробовать то-то и то-то, это может помочь. Вы вносите изменения, тратите несколько минут на компиляцию, написание и исполнение теста — и убеждаетесь в бесплодности всех усилий. В результате появляются новые проблемы, поскольку вы не учли влияния вносимых изменений на другие части программы. В итоге вы возвращаете программу в исходное состояние и пытаетесь сделать что- нибудь еще. Ваш коллега, заметив, что у вас уже дрожат руки, предлагает свою помощь. Однако вы не знаете, как описать проблему, и у вас даже нет исходной версии, которую можно было бы показать, — программа превратилась в минное поле, сплошь усеянное командами трассировки. В итоге вы отвергаете помощь лучшего программиста в группе, звоните домой и сообщаете, что к ужину вы сегодня не придете.

Почему? Потому, что нужно исправить ошибку!

Иррациональная отладка

Вы сгенерировали отчет, а он оказался пустым. При этом вы уверены, что вашей вины здесь нет, хотя в течение последнего часа занимались тем, что вносили изменения в структуры данных и программный код для их запроса и форматирования.

Вы звоните в отдел внутренней поддержки и спрашиваете, нет ли проблем с сетью, хотя File Manager показывает, что все сетевые диски доступны. Потом вы интересуетесь, не девалась ли куда-нибудь база данных, хотя только что успешно к ней подключались, отнимаете 10 минут времени у специалиста из отдела поддержки и, ничего не добившись, в раздражении вешаете трубку.

«Они там ничего не понимают», — бормочете вы. Но как бы там ни было, источник проблемы придется искать самостоятельно. Вы углубляетесь в только что измененный код и проверяете каждую строку. Следующие два часа вы вслух разговариваете с самим собой: «А это что? Я вызвал хранимую процедуру в команде IF. Раньше я этого никогда не делал. Может, хранимые программы так вызывать нельзя?» Вы удаляете команду IF, заменяя ее командой GOTO, но это не решает проблему.

Поиски продолжаются: «Вроде бы код правильный. Но он вызывает программу, которую когда-то написал Джо». Джо уже давно уволился, поэтому, конечно же, виноват он: «Программа, наверное, уже не работает, тем более что мы обновили систему голосовой почты». Вы решаете протестировать программу Джо, которая не изменялась уже два года. Программа работает… только не в вашем коде.

Вы постепенно приходите в отчаяние. «Может, этот отчет должен выполняться только на выходных? А если разместить локальный модуль в анонимном блоке? И вроде я что-то слышал об ошибках в этой служебной программе. Надо поискать обходное решение.»

Вскоре вы начинаете понимать своего восьмилетнего сына, который бьет по монитору, когда не может пройти последний уровень в своей любимой компьютерной игре. И уже собравшись домой, вы вдруг осознаете, что подключены к базе данных, предназначенной для разработки и почти не содержащей никаких данных. Вы подключаетесь к другому экземпляру базы данных, запускаете отчет, и все оказывается в порядке.

За исключением того, что ваш отчет теперь содержит лишние команды GOTO и массу других добавленных конструкций…

Полезные советы и стратегии отладки

Эта статья не претендует на звание исчерпывающего руководства по отладке. Однако приведенные здесь советы и приемы помогут вам улучшить навыки исправления ошибок.

Пользуйтесь отладчиком исходного кода

Самый эффективный способ сокращения времени отладки кода — использование отладчика. Отладчик присутствует в практически любой интегрированной среде разработки приложений (IDE) PL/SQL. И если вы пользуетесь SQL Navigator или Toad от Quest, PL/SQL Developer от Allround Automations, Oracle SQL Developer или другим сходным инструментом с графическим интерфейсом, то сможете легко устанавливать в программах точки останова, выполнять код в пошаговом режиме, просматривать значения переменных и т. д.

Советы, приведенные в этом разделе, полезны независимо от того, пользуетесь ли вы отладчиком с графическим интерфейсом, но если вы отлаживаете программы старым дедовским методом (вставляя в программный код многочисленные вызовы DBMS_OUTPUT. PUT_LINE), эффективность вашей работы оставляет желать лучшего. (К сожалению, если ваш код развернут на площадке клиента, отладка с использованием графических средств не всегда возможна; в таких случаях приходится использовать различные средства протоколирования.)

собирайте информацию

Соберите как можно больше сведений о том, где, когда и при каких условиях произошла ошибка. Маловероятно, что, столкнувшись с этой ошибкой впервые, вы получите о ней все необходимые данные. И помните, что многие проблемы, которые на первый взгляд кажутся простыми, на самом деле могут потребовать тщательного тестирования и анализа. Так что не бросайтесь сразу же модифицировать код в уверенности, что вы понимаете, в чем здесь дело, а сначала выполните следующие действия:

Запустите программу еще раз и посмотрите, воспроизводится ли ошибка. Если ошибка не повторяется, вам вряд ли удастся понять ее причины и исправить ее. Так что постарайтесь определить условия, при которых ошибка повторяется, — это даст вам много полезной информации.
Ограничьте область тестирования и отладки программы — возможно, так вы значительно быстрее найдете ошибку. Недавно я отлаживал один из своих модулей Oracle Forms, у которого в одном из всплывающих окон происходила потеря данных. На первый взгляд закономерность выглядела так: «Если для нового вызова ввести только один запрос, этот запрос будет потерян». Если бы я остановил тестирование в этот момент, мне пришлось бы проанализировать весь код инициализации записи вызова и отработки логики insert. Вместо этого я опробовал разные варианты ввода данных и вскоре обнаружил, что данные теряются только при переходе к всплывающему окну от конкретного элемента. Тестовый сценарий значительно сузился, и обнаружить ошибку в логике стало совсем несложно.
Проанализируйте обстоятельства, при которых ошибка не возникает. Выявление подобных ситуаций помогает сузить область поиска и осознать причину ошибки. Чем больше информации об ошибке вы соберете, тем легче будет найти и устранить причину ее возникновения. Поэтому не жалейте времени на дополнительное тестирование и анализ поведения программы.

Не теряйте здравый смысл

Символическая логика — хлеб насущный для программистов. Какой бы язык программирования вы ни использовали, все программы строятся по определенным логическим правилам. Язык PL/SQL имеет один синтаксис, язык C — другой. В них применяются разные ключевые слова и команды (хотя есть и общие — например IF, но их спецификации несколько различаются). И уж совсем иначе выглядит программа на языке LISP. Однако за всеми этими языками стоит логика, выражаемая с помощью тех или иных операторов. Именно логическая строгость и облегчает изучение новых языков программирования. Если вы в состоянии четко определить для себя задачу и выработать последовательность ее решения, особенности конкретного языка вторичны.

И все же программисты удивительным образом ухитряются обходить всякую логику и здравый смысл и выбирают самые иррациональные пути решения проблем. Как только что-то идет не так, мы начинаем принимать желаемое за действительное, подозревать и обвинять всех и все вокруг: коллег, компьютер, компилятор, текстовый редактор… кого угодно, только не себя.

Но помните, что не желая признать свой промах, вы отказываетесь от поиска путей решения проблемы. Компьютеры и компиляторы не отличаются интеллектом, но они быстры, надежны и последовательны. Все, что они умеют, — это следовать правилам, записанным в вашей программе. Так что, обнаружив ошибку в программном коде, примите ответственность за нее. Признайте, что именно вы сделали что-то не так — вы, а не компилятор PL/SQL, не Oracle Forms и не текстовый редактор.

Если вы начинаете ставить под сомнение базовые элементы или правила компилятора, которые всегда работали в прошлом, лучше отдохнуть от работы. А еще лучше — показать ваш код кому-нибудь другому. Просто удивительно, как сторонний взгляд порой помогает направить ваши аналитические способности на истинную причину проблемы.

Будьте рациональны и бесстрастны. Соглашайтесь с тем, что логично. Отвергайте то, что не имеет объяснения.

Анализ вместо поспешных действий

Итак, когда вы собрали всю необходимую информацию об ошибке, ее нужно проанализировать. У многих программистов анализ выглядит так: «Хм, наверное, причина в этом. Сейчас внесу изменения, перекомпилирую и посмотрю, что получится».

Чем плох подобный подход? Если вы ищете решение методом проб и ошибок, это означает, что:

вы не уверены в том, действительно ли данное изменение способно решить проблему; имея такую уверенность, вы бы не «проверяли», а просто тестировали внесенное изменение;
вы не проанализировали ошибку, чтобы найти причины ее появления; если бы вы имели представление о том, чем вызвана ошибка, то знали бы, как ее исправить; не зная причин, вы пытаетесь что-то наобум изменить и проанализировать результат; это в высшей степени порочная логика;
даже если внесенное вами изменение устраняет ошибку, вы не знаете наверняка, не приняла ли она другую форму и не появится ли вновь (если вы не понимаете суть проблемы, ее внешнее исчезновение еще не означает, что она не появится снова); все, что можно сказать в данном случае, — это то, что при известных вам условиях данная проблема больше не проявляется.

Чтобы действительно избавиться от проблемы, ее нужно полностью проанализировать и определить ее источник. Только тогда будет найдено правильное и надежное решение. Отыскав потенциальное решение, очень внимательно проанализируйте программный код, не выполняя его. Попробуйте придумать разные сценарии для проверки своей гипотезы. И лишь обретя уверенность в том, что вы действительно поняли проблему и нашли ее решение, внесите изменения и протестируйте новую версию программы. Помните: нужно не пробовать, а исправлять и тестировать.

Проанализируйте ошибку, прежде чем тестировать решения. Если вы говорите себе: «А почему бы не попробовать так?» в надежде, что это решит проблему, вы лишь напрасно тратите время, а отладка проходит неэффективно.

Делайте перерывы и обращайтесь за помощью

В том, что касается решения проблем — будь то ошибка в программе или личный кризис, — самым слабым звеном часто оказывается сам решающий. При слишком сильном погружении в проблему трудно сохранить способность оценивать ее объективно.

Если вы чувствуете, что застряли и не продвигаетесь вперед, если все средства перепробованы, а решения все нет, примите радикальные меры:

Сделайте перерыв.
Обратитесь за помощью.

Если накопились усталость и психологическое напряжение, если вы потеряли цель и начали действовать нерационально, если вы уже много часов не отрываете взгляда от экрана, попробуйте отвлечься и немного передохнуть — возможно, решение появится само собой.

Мой упадок духа нередко напрямую связан с тем временем, которое я провожу в своем эргономичном кресле, положив руки на клавиатуру с подкладками для кистей и уставившись в монитор с пониженным излучением. Часто достаточно отойти от компьютера, чтобы голова прояснилась и в ней забрезжило готовое решение. Вы никогда не просыпались утром после очень трудного рабочего дня, когда внезапно неуловимый ответ приходит сам собой?

Возьмите себе за правило хотя бы один раз в час вставать из-за стола, чтобы немного прогуляться. Расслабьтесь и позвольте нейросетям вашего мозга установить новые соединения — и возможно, у вас появятся новые идеи. Окружающий мир продолжает существовать, даже когда ваши глаза прикованы к экрану. Взгляните, что происходит за окном. Всегда полезно помнить об этом — хотя бы для того, чтобы увидеть, какая погода за пределами вашего кокона с кондиционером.

Еще более эффективный путь — попросить кого-нибудь просмотреть ваш код. Взгляд со стороны порой творит чудеса. Можно часами сражаться с программой, а потом, рассказывая кому-нибудь о своих затруднениях, вдруг понять, в чем дело. Ошибка может быть очень простой: например, это может быть несоответствие имен, неверное предположение или неправильная логика оператора IF. Но даже если на вас не снизойдет озарение, причину ошибки поможет выявить свежий взгляд постороннего человека, который:

не писал этот код и не имеет неверных предположений о том, как он должен работать;
не зациклен на программе и вообще не беспокоится о том, будет она работать или нет. Кроме того, обращаясь за помощью к коллегам, вы демонстрируете им, что уважаете их мнение и квалификацию. Это помогает создать атмосферу доверия, взаимопомощи и сотрудничества, что в конечном счете повышает эффективность работы всей группы.

Изменяйте и тестируйте разные области кода по очереди

Один из главных недостатков хорошего программиста заключается в том, что он слишком уверен в своих способностях и поэтому пытается решать одним махом множество проблем. Внеся пять-десять изменений и запустив тест, мы получаем ненадежные и практически бесполезные результаты. Во-первых, некоторые изменения порождают новые проблемы (весьма распространенное явление и верный признак того, что программа требует дополнительной отладки и тестирования); во-вторых, исчезают не все исходные ошибки, и мы понятия не имеем, какие изменения способствовали устранению ошибок, а какие послужили причиной появления новых.

Короче говоря, вся работа по отладке лишь вносит путаницу, поэтому приходится вернуться к исходной точке и действовать более последовательно.

Если только вы не ограничиваетесь очень простыми изменениями, старайтесь решать проблемы по одной, а затем тестировать решения. Возможно, это увеличит затраты времени на компиляцию, генерирование и тестирование кода, но в долгосрочной перспективе ваша работа станет более производительной.

Другой аспект пошагового тестирования и отладки — проведение модульного тестирования отдельных модулей перед тестированием программы, вызывающей эти модули. Если вы тестируете свои программы по отдельности и определяете, что они работают, то при отладке приложения в целом (общесистемный тест) вам не придется беспокоиться о том, возвращают ли эти модули правильные значения и выполняют ли они правильные действия. Вместо этого можно сконцентрироваться на коде, который вызывает модули (за дополнительной информацией о модульном тестировании обращайтесь к этой статье.

Также будет полезно найти систему для хранения информации о ваших усилиях по диагностике ошибок. Дэн Кламадж, рецензент этой книги, сообщает, что он ведет простой текстовый файл с комментариями о его усилиях по воспроизведению проблемы и о том, что было сделано для ее исправления. Обычно в этот файл включается весь код SQL, написанный для анализа ситуации, конфигурационные данные для тестовых сценариев, список проанализированных модулей и все остальные данные, которые могут представлять интерес в будущем. С таким файлом намного проще вернуться к проблеме в будущем (например, когда вы отоспитесь и будете снова готовы к работе) и воспроизвести исходную цепочку рассуждений.

Вас заинтересует / Intresting for you:

Источник

For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces.

When your application raises an error somewhere deep down in the call stack, you don’t get immediate information about the exact source of the error. For large PL/SQL applications, this can be a pain. One workaround is to keep track of statement numbers that were last executed before any error occurred:

DECLARE
  v_statement_no := 0;
BEGIN
  v_statement_no := 1;
  SELECT ...

  v_statement_no := 2;
  INSERT ...

  v_statement_no := 3;
  ...
EXCEPTION
  WHEN OTHERS THEN
    -- Log error message somewhere
    logger.error(module, v_statement_no, sqlerrm);
END;

The above looks an awful lot like println-debugging, a thing that isn’t really known to Java developers

But println-debugging isn’t necessary in PL/SQL either. Use the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, instead! An example:

DECLARE
  PROCEDURE p4 IS BEGIN
    raise_application_error(-20000, 'Some Error');
  END p4;
  PROCEDURE p3 IS BEGIN
    p4;
  END p3;
  PROCEDURE p2 IS BEGIN
    p3;
  END p2;
  PROCEDURE p1 IS BEGIN
    p2;
  END p1;

BEGIN
  p1;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(
      dbms_utility.format_error_backtrace
    );
END;
/

The above PL/SQL block generates the following output:

ORA-20000: Some Error
ORA-06512: at line 3
ORA-06512: at line 6
ORA-06512: at line 9
ORA-06512: at line 12
ORA-06512: at line 16

You can see exactly what line number generated the error. If you’re not using local procedures in anonymous blocks (which you quite likely aren’t), this gets even more useful:

CREATE PROCEDURE p4 IS BEGIN
  raise_application_error(-20000, 'Some Error');
END p4;
/
CREATE PROCEDURE p3 IS BEGIN
  p4;
END p3;
/
CREATE PROCEDURE p2 IS BEGIN
  p3;
END p2;
/
CREATE PROCEDURE p1 IS BEGIN
  p2;
END p1;
/

BEGIN
  p1;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(
      dbms_utility.format_error_backtrace
    );
END;
/

The above now outputs:

ORA-20000: Some Error
ORA-06512: at "PLAYGROUND.P4", line 2
ORA-06512: at "PLAYGROUND.P3", line 2
ORA-06512: at "PLAYGROUND.P2", line 2
ORA-06512: at "PLAYGROUND.P1", line 2
ORA-06512: at line 2

To learn more about the DBMS_UTILITY package, please consider the manual. True to the nature of all things called “UTILITY”, it really contains pretty much random things that you wouldn’t expect there

Источник

When I started writing this post, it was going to be about something else.
This happens occasionally, I have an idea in my head and set to work.
Then I do some research – don’t look so surprised, I do look at the docs occasionally – and, as in this case, I find out that there’s rather more to the topic at hand than I first thought.
What follows is a re-visiting of some of the tools available in Oracle to help with error logging.
It includes stuff that either I’d forgotten or had never considered about some fairly common functions.
Before I dive in, I’d just like to say thanks to William Robertson, who first pointed out to me the similarity between PL/SQL error logging and Quantum Theory. If you’re still unclear of the connection between the two then consider, if you will, the Schrodinger’s Cat Thought Experiment.
It involves locking a cat in a box and possibly poisoning it.
Schrodinger postulates that the cat is both alive and dead…until you open the box to check.
The conclusions we can draw from this experiment are :

According to Quantum Theory, the act of observation changes the nature of the thing being observed
Schrodinger wasn’t a Cat person

NOTE : what follows is an accurate description of how PL/SQL exceptions work up to and including Oracle 11g. From 12c onwards things are a bit different, as illustrated here.
Before going any further, I should point out that most of the stuff I know about Physics comes from watching Star Trek.

Moving on, I now invite you to consider…

Mysteriously moving errors

As with cats – according to Shrodinger at least – the act of “observing”- well, handling – a PL/SQL exception changes the error ( or the location from which it originated at any rate).

For example…

declare
    l_cat number;
begin
    l_cat := 'GREEBO';
end;
/

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

No problem with this. It shows us that the error happened at line 4 in the code, which is correct.
However….

declare
    l_cat number;
begin
    l_cat := 'GREEBO';
exception
    when others then
        -- do some logging stuff....
        raise;
end;
/

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 8

Here, the exception originated on line 4. However, the error is reported at line 8 – inside the exception handler.

This then, is the problem with which we need to wrestle.
Time to take a closer look at the tools that Oracle provides us for the purpose of error handling, starting with the most venerable…

SQLCODE and SQLERRM

SQLCODE and SQLERRM have been around for as long as I can remember.
If you’ve worked on PL/SQL applications for any length of time, you will almost certainly seen a variation of one of the following in an exception handler :

    sqlerrm(sqlcode);
    
    sqlcode||' : '||sqlerrm;
    
    substr(sqlerrm(sqlcode),1,500));

If any of the above ring any bells then it illustrates the point that these functions ( for that is what they are), are not especially well understood.

SQLCODE

SQLCODE returns the number of the last error encountered. Not the message, just the error number :

set serveroutput on
declare
    l_cat_lives number;
begin
    l_cat_lives := 'If the cat belongs to Shrodinger the answer is uncertain. Otherwise 9';
exception 
    when others then
        dbms_output.put_line(sqlcode);
end;
/

-6502

PL/SQL procedure successfully completed.

SQLCODE returns 0 on successful completion…

declare
    l_cat_lives number;
begin
    l_cat_lives := 9;
    dbms_output.put_line(sqlcode);
end;
/
0

PL/SQL procedure successfully completed.

For user-defined errors, it returns 1 by default…

declare
    e_no_cat exception;
begin
    raise e_no_cat;
exception when e_no_cat then
    dbms_output.put_line(sqlcode);
end;
/

1

PL/SQL procedure successfully completed.

…unless you associate the exception with an error number using the EXCEPTION_INIT pragma…

declare
    e_no_cat exception;
    pragma exception_init( e_no_cat, -20000);
begin
    raise e_no_cat;
exception when e_no_cat then
    dbms_output.put_line(sqlcode);
end;
/
-20000

PL/SQL procedure successfully completed.

SQL>

It will also return the relevant error code if you use RAISE_APPLICATION_ERROR…

begin
    raise_application_error(-20001, 'The cat has run off');
exception when others then
    dbms_output.put_line(sqlcode);
end;
/
-20001

PL/SQL procedure successfully completed.

SQL>

On it’s own then, SQLCODE is not much help in terms of working out what went wrong unless you happen to have memorized all of the Oracle error messages.

Fortunately we also have…

SQLERRM

This function takes in an error number and returns the relevant message :

begin
    dbms_output.put_line(sqlerrm(-6502));
end;
/
ORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed.

Because of this SQLERRM can be used to create the equivalent of the oerr utility in PL/SQL.
Better still, it takes SQLCODE as it’s default parameter…

declare
    l_cat_lives number;
begin
    l_cat_lives := 'If the cat belongs to Shrodinger the answer is uncertain. Otherwise 9';
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;
/

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

PL/SQL procedure successfully completed.

The maximum length of a varchar returned by SQLERRM is, according to the documentation, “the maximum length of an Oracle Database error message” – 512.

Whilst we’re on the subject, the 11gR2 documentationincludes a note recommending that, generally, DBMS_UTILITY.FORMAT_ERROR_STACK be used instead…

DBMS_UTILITY.FORMAT_ERROR_STACK

So, let’s see what this function gives us when used as a drop-in replacement for SQLERRM….

declare
    l_cat_lives number;
begin
    l_cat_lives := 'If the cat belongs to Shrodinger the answer is uncertain. Otherwise 9';
exception
    when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
end;
/
ORA-06502: PL/SQL: numeric or value error: character to number conversion error


PL/SQL procedure successfully completed.

SQL>

Not much then based on this example. However, there are a couple of differences.
The first is that this function returns up to 2000 characters of the error stack.
The second is that it does not take any arguments. Fair enough I suppose. From the name you’d infer that this function returns the entire error stack rather than the single message that SQLERRM does.
Let’s put that to the test…

create or replace package transporter as
    function find_target return varchar2;
    procedure beam_me_up_scotty;
end transporter;
/

create or replace package body transporter as
    function find_target 
        return varchar2
    is
    begin
        raise_application_error(-20003, 'Location or velocity unknown');
    end find_target;

    procedure beam_me_up_scotty is
        l_target varchar2(30);
    begin
        -- engage the heisenburg compensator...
        l_target := find_target;
        dbms_output.put_line('Energize !');
    end beam_me_up_scotty;
end transporter;
/

This package is an analog of what Star Fleet Engineers would have been working with before they came up with the Heisenburg Compensator.

If we call this without any error handling, we’ll get a “stack” of errors…

begin
    transporter.beam_me_up_scotty;
end;
/

*
ERROR at line 1:
ORA-20003: Location or velocity unknown
ORA-06512: at "MIKE.TRANSPORTER", line 6 
ORA-06512: at "MIKE.TRANSPORTER", line 13
ORA-06512: at line 1

You’d expect something fairly similar if you used the FORMAT_ERROR_STACK function…

set serveroutput on size unlimited
begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
end;
/

ORA-20003: Location or velocity unknown



PL/SQL procedure successfully completed.

SQL>

So, not similar at all then.
NOTE – if you’ve already spotted the deliberate mistake here. Bear with me for a bit.
If we change the package body so that errors are raised at multiple levels…

create or replace package body transporter as
    function find_target 
        return varchar2
    is
    begin
        raise_application_error(-20003, 'Location or velocity unknown');
    end find_target;

    procedure beam_me_up_scotty is
        l_target varchar2(30);
    begin
        -- engage the heisenburg compensator...
        l_target := find_target;
        dbms_output.put_line('Energize !');
    exception when others then
        raise_application_error(-20004, 'I canna change the laws o physics!');
    end beam_me_up_scotty;
end transporter;
/

… we simply get the last error passed…

begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
end;
/

ORA-20004: I canna change the laws o physics!



PL/SQL procedure successfully completed.

SQL>

From all of this, it would appear that DBMS_UTILITY.FORMAT_ERROR_STACK doesn’t really give us much (if anything) over SQLERRM. This is especially true if the documentation is correct and no single Oracle Error Message will exceed 512 bytes.
All of which is rather odd, until you consider…

RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR is usually invoked as in the above example. However, it actually accepts three arguments :

NUM – an error code in the range -20000 to -20999
MSG – an error message up to 1024 characters ( including the error code)
KEEPERRORSTACK – if TRUE then the error code is placed at the top of the error stack. Otherwise it replaces the error stack.
Default is FALSE

The first point to note here is that, unlike SQLERRM, FORMAT_ERROR_STACK can accomodate the full length of a message from RAISE_APPLICATION_ERROR.
More relevant to the issue at hand however, it the KEEPERRORSTACK parameter. If we tweak the package once more to set this parameter to true…

create or replace package body transporter as
    function find_target 
        return varchar2
    is
    begin
        raise_application_error(-20003, 'Location or velocity unknown', true);
    end find_target;

    procedure beam_me_up_scotty is
        l_target varchar2(30);
    begin
        -- engage the heisenburg compensator...
        l_target := find_target;
        dbms_output.put_line('Energize !');
    exception when others then
        raise_application_error(-20004, 'I canna change the laws o physics!', true);
    end beam_me_up_scotty;
end transporter;
/

…and re-run our test…

begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
end;
/
ORA-20004: I canna change the laws o physics!
ORA-06512: at "MIKE.TRANSPORTER", line 16
ORA-20003: Location or velocity unknown

PL/SQL procedure successfully completed.

…we now get a stack. However, we’re still stuck without the line number from where the error originated.
Fortunately, they’ve been burning the candle at both ends over at Star Fleet, or possibly at Redwood Shores…

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Let’s try re-executing our package, this time using FORMAT_ERROR_BACKTRACE…

begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/

ORA-06512: at "MIKE.TRANSPORTER", line 7
ORA-06512: at "MIKE.TRANSPORTER", line 14
ORA-06512: at line 2



PL/SQL procedure successfully completed.

SQL>

Well, that’s different. We get a stack, together with the line number at which the error originated. Unfortunately it doesn’t include the originating error message itself. Let’s try that again, but this time in combination with SQLERRM…

 
begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "MIKE.TRANSPORTER", line 7
ORA-06512: at "MIKE.TRANSPORTER", line 14
ORA-06512: at line 2



PL/SQL procedure successfully completed.

SQL>

Now we have the original error. We also have the line at which it happened. At last, we have our Heisenburg compensator.
Well, in most circumstances. Just before we test it on Admiral Archer’s prize beagle …

create or replace package body transporter as
    function find_target 
        return varchar2
    is
        l_silly number;
    begin
        l_silly :=  'Location or velocity unknown';
        exception when others then
            -- do some logging and...
            raise;
    end find_target;

    procedure beam_me_up_scotty is
        l_target varchar2(30);
    begin
        -- engage the heisenburg compensator...
        l_target := find_target;
        dbms_output.put_line('Energize !');
    end beam_me_up_scotty;
end transporter;
/

Now we’ve added an error handler to the innermost package member…

begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "MIKE.TRANSPORTER", line 10
ORA-06512: at "MIKE.TRANSPORTER", line 17
ORA-06512: at line 2



PL/SQL procedure successfully completed.

…once again the handled error has “moved” to the exception block of the function.

In terms of retrieving the error stack, it would appear that a combination of SQLERRM and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE offer the most comprehensive and reliable information.
However, in order to further pin down where those pesky errors are originating we may need to turn to a time-honoured technique – albeit with a comparatively modern twist…

Location Markers with $$PLSQL_LINE

The $$PLSQL_LINE variable simply returns the line number of the stored program unit or anonymous block you’re currently in…

begin
    dbms_output.put_line('At line : '||$$plsql_line);
end;
/

At line : 2

PL/SQL procedure successfully completed.

SQL>

By sprinkling a few dollars through our code, we should get a better (although still not necessarily exact) idea of where our error is originating.

I’m going to persevere with this transporter code. After all, they managed to get it working in the original Star Trek and that was way back in the 60’s…

create or replace package body transporter as
    function find_target 
        return varchar2
    is
        l_loc pls_integer;
        l_silly number;
    begin
        l_loc := $$plsql_line;
        l_silly :=  'Location or velocity unknown';
        exception when others then
            dbms_output.put_line('Error originating after line '||l_loc);
            raise;
    end find_target;

    procedure beam_me_up_scotty is
        l_target varchar2(30);
    begin
        -- engage the heisenburg compensator...
        l_target := find_target;
        dbms_output.put_line('Energize !');
    end beam_me_up_scotty;
end transporter;
/

Now, we should get a bit more information…

begin
    transporter.beam_me_up_scotty;
exception
    when others then
        dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace); 
end;
/
Error originating after line 8
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "MIKE.TRANSPORTER", line 12
ORA-06512: at "MIKE.TRANSPORTER", line 19
ORA-06512: at line 2



PL/SQL procedure successfully completed.

SQL>

The error actually originates from line 9 so that’s a pretty good approximation.
The downside is the aforementionned sprinkling of the assignment of $$PLSQL_LINE to a variable immediately before you perform any action.

Well, I’ve probably managed to annoy an Physics experts and Star Trek fans that happen to be reading. That’s before you even start thinking about PL/SQL Developers.
On the plus side I can say, hand-on-heart, that no cats were harmed in the writing of this post.

Источник