ERROR HANDLING

ERROR HANDLING

PL/SQL implements error handling with exceptions and exception handlers. Exceptions can be associated with oracle errors or with your own user-defined errors. By using exceptions and exception handlers, you can make your PL/SQL programs robust and able to deal with both unexpected and expected errors during execution.

ERROR TYPES

  • Compile-time errors
  •  Runtime errors

Errors that occur during the compilation phase are detected by the PL/SQL engine and reported back to the user, we have to correct them.

Runtime errors are detected by the PL/SQL runtime engine which can programmatically raise and caught by exception handlers.
Exceptions are designed for run-time error handling, rather than compile-time error handling.

HANDLING EXCEPTIONS
When exception is raised, control passes to the exception section of the block. The exception section consists of handlers for some or all of the exceptions. An exception handler contains the code that is executed when the error associated with the exception occurs, and the exception is raised.
Syntax:
EXCEPTION
When exception_name then
Sequence_of_statements;
When exception_name then
Sequence_of_statements;
When others then
Sequence_of_statements;
END;

EXCEPTION TYPES

  • Predefined exceptions
  • User-defined exceptions

PREDEFINED EXCEPTIONS
Oracle has predefined several exceptions that correspond to the most common oracle errors. Like the predefined types, the identifiers of these exceptions are defined in the STANDARD package. Because of this, they are already available to the program, it is not necessary to declare them in the declarative section.

 USER-DEFINED EXCEPTIONS

A user-defined exception is an error that is defined by the programmer. User-defined exceptions are declared in the declarative secion of a PL/SQL block. Just like variables, exeptions have a type EXCEPTION and scope.

RAISING EXCEPTIONS
User-defined exceptions are raised explicitly via the RAISE statement.

 BULIT-IN ERROR FUNCTIONS

SQLCODE AND SQLERRM
1 SQLCODE returns the current error code, and SQLERRM returns the current error message text;
2 For user-defined exception SQLCODE returns 1 and SQLERRM returns “user-deifned exception”.
3 SQLERRM wiil take only negative value except 100. If any positive value other than 100 returns non-oracle exception.

 DBMS_UTILITY.FORMAT_ERROR_STACK

1 The built-in function, like SQLERRM, returns the message associated with the current error.
2 It differs from SQLERRM in two ways:
3 Its length is not restricted; it will return the full error message string.
4 You can not pass an error code number to this function; it cannot be used to return the message for a random error code.

 DBMS_UTILITY.FORMAT_CALL_STACK

This function returns a formatted string showing the execution call stack inside your PL/SQL application. Its usefulness is not restricted to error management; you will also find its handy for tracing the exectution of your code. You may not use this function in exception block.

 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

It displays the execution stack at the point where an exception was raised. Thus , you can call this function with an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.

 EXCEPTION_INIT PRAGMA

Using this you can associate a named exception with a particular oracle error. This gives you the ability to trap this error specifically, rather than via an OTHERS handler.
Syntax:
PRAGMA EXCEPTION_INIT(exception_name, oracle_error_number);

 RAISE_APPLICATION_ERROR

You can use this built-in function to create your own error messages, which can be more descriptive than named exceptions.
Syntax:
RAISE_APPLICATION_ERROR(error_number, error_message,, [keep_errors_flag]);
The Boolean parameter keep_errors_flag is optional. If it is TRUE, the new error is added to the list of errors already raised. If it is FALSE, which is default, the new error will replace the current list of errors.

 EXCEPTION PROPAGATION

Exceptions can occur in the declarative, the executable, or the exception section of a PL/SQL block.

EXCEPTION RAISED IN THE EXECUATABLE SECTION
Exceptions raised in execuatable section can be handled in current block or outer block.

EXCEPTION RAISED IN THE DECLARATIVE SECTION

Exceptions raised in the declarative secion must be handled in the outer block.

 EXCEPTION RAISED IN THE EXCEPTION SECTION

Exceptions raised in the declarative secion must be handled in the outer block.

 RESTRICTIONS

  • You can not pass exception as an argument to a subprogram.

Related posts:

Leave a Reply