Oracle sql error stack

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

Функция dbms_utility.format_error_backtrace возвращает список вызовов функций и процедур от места генерации исключения до места его обработки.
Для рассмотрения работы 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.

На этот раз все исключения обрабатываются только в одном месте — вызывающей функции, и только в ней используется 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:

Источник

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:

Nine Good to Knows for PL/SQL Error Management
PL/SQL Error Handling (doc)

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

CREATE OR REPLACE PROCEDURE plch_check_balance (

   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

CREATE OR REPLACE PROCEDURE plch_check_balance (

   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

CREATE OR REPLACE PROCEDURE plch_check_balance (

   balance_in IN NUMBER)
   AUTHID DEFINER
IS
BEGIN
   IF balance_in < 0
   THEN
      RAISE VALUE_ERROR;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'ORA-'
         || TO_CHAR (UTL_CALL_STACK.error_number (1), 'fm00000')
         || ': '
         || UTL_CALL_STACK.error_msg (1));
END;
/

BEGIN
   plch_check_balance (-1);
END;
/

DROP PROCEDURE plch_check_balance
/

Источник

Статьи про Oracle

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

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

v:1.0 03.12.2011

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

Часто при обработке исключений в pl/sql разработчики ограничиваются использованием встроенной функции sqlerrm, однако в Oracle есть и другие интересные
средства для обработки ошибок. Одно из этих средств — функция 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 в стартовой процедуре, в других процедурах нет обработчиков исключений

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, и какие процедуры выполнялись.

Метки:

PL/SQL

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

Источник

В Oracle 10g возможности для работы со стеком ошибок были расширены. В пакете DBMS_UTILITY появились функции, которые позволяют получить больше информации о том, как ведёт себя программа в случае возникновения исключительной ситуации.

Вот эти функции:

FORMAT_ERROR_BACKTRACE — позволяет получить строку из программы, в которой произошла исключительная ситуация.
FORMAT_CALL_STACK — отображает на экране стэк программы на момент возникновения исключения.
FORMAT_ERROR_STACK — позволяет получить “полное” сообщение об ошибке (дело в том, что привычная всем функция SQLERRM имеет ограничение на длину выводимого текста 512 символов, новая функция имеет ограничение в 2000 символов).

  Самый простой пример, который демонстрирует работу этих функций выглядит так:
   DECLARE
     local_exception EXCEPTION;
     FUNCTION nested_local_function
     RETURN BOOLEAN IS
       retval BOOLEAN := FALSE;
     BEGIN
       RAISE local_exception;
       RETURN retval;
     END;
     BEGIN
       IF nested_local_function THEN
         dbms_output.put_line(‘No raised exception’);
       END IF;
     EXCEPTION
       WHEN others THEN
         dbms_output.put_line(‘DBMS_UTILITY.FORMAT_CALL_STACK’);
         dbms_output.put_line(‘——————————‘);
         dbms_output.put_line(dbms_utility.format_call_stack);
         dbms_output.put_line(‘DBMS_UTILITY.FORMAT_ERROR_BACKTRACE’);
         dbms_output.put_line(‘————————————‘);
         dbms_output.put_line(dbms_utility.format_error_backtrace);
         dbms_output.put_line(‘DBMS_UTILITY.FORMAT_ERROR_STACK’);
         dbms_output.put_line(‘——————————-‘);
         dbms_output.put_line(dbms_utility.format_error_stack);
     END;

   Если его выполнить, то получим:
DBMS_UTILITY.FORMAT_CALL_STACK
——————————
—— PL/SQL Call Stack ——
object    line     object
handle    number   name
20909240      18   anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
————————————
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
——————————-
ORA-06510: PL/SQL: unhandled user-defined exception

Скорее всего, самой востребованной функцией будет — DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, рекомендую ознакомиться с более интересным примеров стека ошибок на сайте документации по Oracle 10g: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_util.htm#i1003874

Источник

Most programmers don’t like exceptions, but you should consider them as your closest friends. They are the ones that honestly say what is wrong with your program. We cannot foresee all possible problematic events, and even the best programmers write bugs. Exceptions There are three kinds of exceptions Internally defined: A system error, defined by Oracle, that occurs. Predefined: The

Exceptions

There are three kinds of exceptions

Internally defined: A system error, defined by Oracle, that occurs.
Predefined: The most common internally defined exceptions that are given predefined names.
User defined: A logical error which you define and raise yourself

System errors could occur from improper coding, like the “ORA-01001: Invalid cursor”, which you should try to fix as soon as possible in your code. And the “TOO_MANY_ROWS”-error might give you clues about bad data quality. To resolve these bugs, it is important to know where, when and why it happened.

But system errors could also occur from hardware failures, like the “ORA-12541: TNS: no listener”, when an ftp-server might be unreachable over the network. In that case, all you can do, and should do, is provide proper error handling and transaction management, and give as detailed information as possible about this situation to the people that need to know.

These system-errors always have an error number assigned, so you can easily identify the error. The 22 predefined exceptions also have a name assigned, which allows for easier, and more readable exception handling. For the other, non-predefined, system-errors, a name can be linked by using the pragma “EXCEPTION_INIT”.

DECLARE

network_error EXCEPTION;

PRAGMA EXCEPTION_INIT(network_error, —12541);

BEGIN

...

EXCEPTION

WHEN too_many_rows THEN

...

WHEN network_error THEN

...

END;

User defined errors we will raise ourselves. They can be given a number and a name.

To raise a user defined error with a chosen number and error message, we call the procedure “RAISE_APPLICATION_ERROR”. Oracle allows us the create error numbers in the range of -20000 to -20999. This allows us to create 1000 unique error codes for our logical errors throughout our application. Just like we did for system errors, we can name our user defined errors by using the pragma “EXCEPTION_INIT”.

BEGIN

RAISE_APPLICATION_ERROR(—20000,’Logical error occured’);

END;

If we do not care about the error code and error message, and we will foresee an exception block to directly handle the error, we could also raise errors by the keyword “RAISE”, followed by the exception name. If we do not handle the error, the error ORA-65000, “Unhandled user exception” is propagated.

DECLARE

logical_error EXCEPTION;

BEGIN

RAISE logical_error;

EXCEPTION

WHEN logical_error THEN

...

END;

Besides user defined errors, we could also raise one of the predefined errors. Although it is recommended to use proper a description for your errors, instead of recycling error codes.
In an exception block, the keyword “RAISE” could also be used without an exception name, which can be useful to add logging or to execute clean-up code, before propagating the error.

BEGIN

RAISE no_data_found;

EXCEPTION

WHEN no_data_found THEN

...

RAISE;

END;

Error Propagation

When an error occurs, further execution of the execution block is halted, and an appropriate exception handler is searched. If no handler is found in the current block, the error is propagated to the calling block. As a side note, errors that occur in the declaration section are also handled in the calling block.

BEGIN

RAISE no_data_found;

END;

EXCEPTION

WHEN no_data_found THEN

...

END;

Transactions stay pending when errors are raised, it is our own responsibility to rollback or commit, although most frameworks will rollback an unhandled exception themselves.

Handling errors

Errors will occur, and when they do, it is important that we know about them, and get as much details from them as possible. When troubleshooting we need the “what”, “where”, “when” and “why”.

For this reason, I add following block to every public function or procedure:

PROCEDURE test(p_param1 IN NUMBER

,p_param2 IN VARCHAR2)

BEGIN

...

EXCEPTION

WHEN OTHERS THEN

log_error($$PLSQL_UNIT,$$PLSQL_LINE,p_param1,p_param2);

RAISE;

END;

The “log_error”-procedure defined as autonomous transaction, writing the information we need for troubleshooting to a table. Simplified, it looks like this:

PROCEDURE log_error(p_object_name IN log_messages.object_name%TYPE

,p_line IN log_messages.line%TYPE

,p_attribute1 IN log_messages.attribute1%TYPE DEFAULT NULL

,p_attribute2 IN log_messages.attribute2%TYPE DEFAULT NULL

,p_attribute3 IN log_messages.attribute3%TYPE DEFAULT NULL

,p_attribute4 IN log_messages.attribute4%TYPE DEFAULT NULL)

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO log_messages(seq

,object_name

,line

,error_stack

,error_backtrace

,call_stack

,attribute1

,attribute2

,attribute3

,attribute4

,log_dt

,log_user)

VALUES(seq.nextval

,p_object_name

,p_line

,dbms_utility.format_error_stack

,dbms_utility.format_error_backtrace

,dbms_utility.format_call_stack

,p_attribute1

,p_attribute2

,p_attribute3

,p_attribute4

,SYSDATE

,USER);

COMMIT;

END log_error;

Going over the different parameters:

Seq:

Adding a sequence will allow us to order the message in the order they occurred

Object_name/Line:

By using the PL/SQL-directives $$PLSQL_UNIT and $$PLSQL_LINE, we know where the log was created.

Error_stack/Error_backtrace/Call_stack:

In the dbms_utilty package, we find three functions that give us valuable information about the error that was raised. The error stack gives us an overview of all the errors that were raised, giving more information than “SQLCODE” and “SQLERRM”. The error stack gives us the exact line number where the error occurred. The call stack will give us information about which code called the procedure or function raising the error. Later on, I will explain this in more detail.

Attribute1-4:

To reproduce the error, it can be helpful to know which parameters where used when the error occurred.

Log_dt/Log_user:

Further helpful information are the “who” and “when”. Depending on the technology used, you might want to use your own logic to retrieve the application user instead of the Oracle user.

Dbms_utilty example

Let’s take a look at what these functions produce by executing following block of code:

DECLARE

PROCEDURE proc1

BEGIN

RAISE NO_DATA_FOUND;

END;

PROCEDURE proc2

BEGIN

proc1;

END;

PROCEDURE proc3

BEGIN

proc2;

EXCEPTION

WHEN OTHERS THEN

log_error($$PLSQL_UNIT,$$PLSQL_LINE);

RAISE_APPLICATION_ERROR(—20001,‘Unhandled exception occured.’,TRUE);

END;

PROCEDURE proc4

BEGIN

proc3;

END;

BEGIN

proc4;

EXCEPTION

WHEN OTHERS THEN

log_error($$PLSQL_UNIT,$$PLSQL_LINE);

RAISE;

END;

As you can see, I’ve created four procedures, calling each other. Proc1 raises an error, which is caught in proc3, logged, and followed by the raise of a different error. There is a second log written in the anonymous block, so we end up with two records.

The two call stacks are

“ORA-01403: no data found”

And

“ORA-20001: Unhandled exception occured.
ORA-06512: at line 21
ORA-01403: no data found”

As you can see in the code of proc3, I have added a third parameter to the “RAISE_APPLICATION_ERROR”-procedure, telling it to keep the error stack. Our first message tells us a “no data found”-error occurred, our second message tells us we had two errors, first the ORA-01403, followed by the user-defined ORA-20001. The ORA-06512 is merely telling us the line number.

The two backtraces are:

“ORA-06512: at line 5
ORA-06512: at line 11
ORA-06512: at line 17″

And

“ORA-06512: at line 21
ORA-06512: at line 27
ORA-06512: at line 30″

The first back trace tells us that the error occurred at line 5, after proc1 was called at line 11, after proc2 was called on line 17. It points us to the error. If we look at the second backtrace, it points us to line 21, were we find the call to “RAISE_APPLICATION_ERROR”. Notice how it loses the information of the original error on line 5, so it is vital to store the back trace whenever we catch an exception.

Finally, looking at the first call stack, we find following information:

“—– PL/SQL Call Stack —–
object      line object
handle    number name
1CA24A88         1 anonymous block
1C9CDCC0        10 procedure SCOTT.LOG_ERROR
1CA121EC        20 anonymous block
1CA121EC        27 anonymous block
1CA121EC        30 anonymous block”

It points us to the original call. The actual log is written in the procedure “log_error”, which was called in proc3 at line 20. Proc3 was called by proc4 at line 27, and proc4 was called at line 30. Where the back trace tells us everything that happened between the error and the logging, the call stack tells us everything that happened before the error.

Conclusion

Troubleshooting errors can be difficult, especially if you don’t know what was going on. But by preparing yourself for these situations, and making sure you have all the information you need, you will be able to pinpoint the problem much faster.

I tried to highlight the most important aspect of error handling in my opinion. If you feel something is missing, please share your knowledge by leaving a comment.

Источник

Этот пост навеян статьями Часть 1. Логирование событий в Oracle PL/SQL и Часть 2. Идентификация событий происходящих в Oracle PL/SQL. В первую очередь, как специалисту по performance tuning и troubleshooting, хотелось бы прокомментировать некоторые нюансы.

1. Уровни детализации логгирования

В показанной системе не хватает гибкости настройки логгирования: как уровня детализации, так и места, куда их выводить. Можно было позаимствовать функциональность из широко известных систем логгирования а-ля java.util.logging (SLF4j, log4j и его вариации для других языков/систем, и тд), гибкую настройку для какого кода с какого уровня сообщений и куда их сохранять. Например, в том же log4plsql можно настроить вывод и в alert.log, и в trace file (с помощью `dbms_system.ksdwrt()`)

2. Пользовательские ошибки и сообщения

Из самой внутренней системы ошибок Оракл можно было позаимствовать использование UTL_LMS.FORMAT_MESSAGE. Кстати, сами ошибки(и events) можно посмотреть с помощью sys.standard.sqlerrm(N):

SQL> select sys.standard.sqlerrm(-1476) errmsg from dual;

ERRMSG
-------------------------------------
ORA-01476: divisor is equal to zero

Примеры: err_by_code.sql, trace_events.sql

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

3. Что же делать в случае незалоггированных ошибок

Естественно, может случиться так, что существующая система логгирования не регистрирует какие-то неординарные ошибки, или даже ее совсем нет в базе. Тут могут быть полезны триггеры `after servererror on database/schema`. Простой минимальный пример.

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

Например, недавно Nenad Noveljic расследовал проблему c «TNS-12599: TNS:cryptographic checksum mismatch» для чего ему нужно было получить callstack:

К счастью, помимо использованного у него в статье «ERRORSTACK», есть еще большой список «ACTIONS», включающий в себя и «CALLSTACK»:

В этой команде 12599 — это номер события(event), callstack — инструктирует сделать дамп call стека, level 2 — указывает вывести еще и аргументы функций, lifetime 1 — только один раз.

Более подробно об этом у Tanel Poder с примерами:

http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc
https://tanelpoder.com/2010/06/23/the-full-power-of-oracles-diagnostic-events-part-2-oradebug-doc-and-11g-improvements/

Мало того, как сам Танел и посоветовал, можно еще воспользоваться и «trace()» для форматированного вывода shortstack():

Так что этим же мы можем воспользоваться этим для вывода callstack:

alter system set events '12599 trace("stack is: %n", shortstack())';

Или в более новом формате:

alter system set events 'kg_event[12599]{occurence: start_after 1, end_after 1} trace("stack is: %n", shortstack())';

Как вы видите, здесь я еще добавил фильтр на количество срабатываний: после первого выполнения и только 1 раз.

Покажу на примере «ORA-01476: divisor is equal to zero»:

alter system set events 'kg_event[1476]{occurence: start_after 1, end_after 1} trace("stack is: %n", shortstack())';

Здесь kg_event — это Kernel Generic event, 1476 — ORA-1476. После этого запускаем в своей сессии:

SQL> alter session set events 'kg_event[1476]{occurence: start_after 1, end_after 1} trace("stack is: %n", shortstack())';

Session altered.

SQL> select 1/0 x from dual;
select 1/0 x from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select 1/0 x from dual;
select 1/0 x from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero


SQL> select 1/0 x from dual;
select 1/0 x from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

И в трейсфайле получаем:

# cat ORA19_ora_12981.trc
Trace file /opt/oracle/diag/rdbms/ora19/ORA19/trace/ORA19_ora_12981.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
Build label:    RDBMS_19.9.0.0.0DBRU_LINUX.X64_200930
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      b7c493c7f9b0
Release:        3.10.0-1062.12.1.el7.x86_64
Version:        #1 SMP Tue Feb 4 23:02:59 UTC 2020
Machine:        x86_64
Instance name: ORA19
Redo thread mounted by this instance: 1
Oracle process number: 66
Unix process pid: 12981, image: oracle@b7c493c7f9b0


*** 2021-05-08T14:12:27.000816+00:00 (PDB1(3))
*** SESSION ID:(251.9249) 2021-05-08T14:12:27.000846+00:00
*** CLIENT ID:() 2021-05-08T14:12:27.000851+00:00
*** SERVICE NAME:(pdb1) 2021-05-08T14:12:27.000855+00:00
*** MODULE NAME:(sqlplus.exe) 2021-05-08T14:12:27.000859+00:00
*** ACTION NAME:() 2021-05-08T14:12:27.000862+00:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-05-08T14:12:27.000865+00:00
*** CONTAINER ID:(3) 2021-05-08T14:12:27.000868+00:00

stack is: dbgePostErrorKGE<-dbkePostKGE_kgsf<-kgeade<-kgeselv<-kgesecl0<-evadiv<-kpofcr<-qerfiFetch<-opifch2<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main

Или, например, недавно я посоветовал использовать alter system set events 'trace[sql_mon.*] [SQL: ...] disk=high,memory=high,get_time=highres'; для выяснения причин, почему конкретный запрос не мониторится/сохраняется real-time SQL монитором (RTSM — Real Time SQL Monitor).

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

Источник

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

Источник

Obsessed with Oracle PL/SQL

Search This Blog

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

12.1 Compile-Time Warnings

12.1.1 DBMS_WARNING Package

12.2 Overview of Exception Handling

12.2.1 Exception Categories

12.2.2 Advantages of Exception Handlers

12.2.3 Guidelines for Avoiding and Handling Exceptions

12.3 Internally Defined Exceptions

12.4 Predefined Exceptions

DBMS_UTILITY.FORMAT_CALL_STACK

UTL_CALL_STACK (Oracle Database 12c)

DBMS_APPLICATION_INFO

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

Пакет DBMS_TRACE

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

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

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

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

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

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

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

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

Exceptions

Error Propagation

Handling errors

Dbms_utilty example

Conclusion

1. Уровни детализации логгирования

2. Пользовательские ошибки и сообщения

3. Что же делать в случае незалоггированных ошибок

source code

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