, , ,

Error Reporting Functions

In an exception handler, you can use the built-in functions SQLCODE and SQLERRM to find out which error occurred and to get the associated error message.
1. For internal exceptions, SQLCODE returns the number of the Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. SQLERRM returns the corresponding error message. The message begins with the Oracle error code.
2. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message User-Defined Exception unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message.
The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.
If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message ORA-0000: normal, successful completion.

Passing  an error number
You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. Make sure you pass negative error numbers to SQLERRM. In the following example, you pass positive numbers and so get unwanted results:
DECLARE
…
err_msg VARCHAR2(100);
BEGIN
/* Get all Oracle error messages. */
FOR err_num IN 1..9999 LOOP
err_msg := SQLERRM(err_num); — wrong; should be -err_num
INSERT INTO errors VALUES (err_msg);
END LOOP;
END;
Passing a positive number to SQLERRM always returns the message user-defined exception unless you pass +100, in which case SQLERRM returns the message no data found. Passing a zero to SQLERRM always returns the message normal, successful completion.
SQLCODE or SQLERRM in SQL statements
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
…
EXCEPTION
…
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;
The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. The functions SQLCODE and SQLERRM are especially useful in the OTHERS exception handler
because they tell you which internal exception was raised.

/by
, , ,

NonPredefined Error

To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called pseudoinstructions)
are processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
where exception_name is the name of a previously declared exception. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:

/by
, , ,

Predefined Exceptions

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.
PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and SQLCODE return values.

/by
, , ,

User-Defined Exceptions

PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements.

/by
, , ,

Exception Handling in PL SQL

In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.

 
Exception Types
There are three types of exception
 
Exception Handlers 
 
Trapping an Exception:
If the exception is raised in the executable section of the block, processing branches to the corresponding exception handler in the exception section of the block. If PL/SQL successfully handles the exception, then the exception does not propagate to the enclosing block or environment. The PL/SQL block terminates successfully.

Propagating an Exception:
If the exception is raised in the executable section of the block and there is no corresponding exception handler, the PL/SQL block terminates with failure and the exception is propagated to the calling environment.

/by