EXCEPTIONS - 4

[debasisdas]

NOTE:-
=========
1.Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package.

2.Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

3.Scope Rules for PL/SQL Exceptions
-----------------------------------------------------------
Can't declare an exception twice in the same block. However can declare the same exception in two different blocks.

Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.

If you redeclare a global exception in a sub-block, the local declaration prevails. So, the sub-block cannot reference the global exception unless it was declared in a labeled block.

4.The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To call RAISE_APPLICATION_ERROR, use the syntax

raise_application_error(error_number, message[, {TRUE | FALSE}]);

Where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long.
If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors.

RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.

An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.

5.The exception OTHERS can handle all sort of exception ,so it must be the last one in the list of exception.

PRAGMA EXCEPTION_INIT
=====================

CREATE OR REPLACE PROCEDURE raiseexp IS
empty EXCEPTION;    --user defined exception declared
PRAGMA EXCEPTION_INIT(empty, -01400);  
--initialized to oracle defined number (this exceptionis raised when a null is inserted into afield with not null constraint)
BEGIN
INSERT INTO emp(empno)  VALUES  (null);
COMMIT;
EXCEPTION
WHEN empty THEN
dbms_output.put_line('ERROR: Trapped Fields Left Null');
--when the exception is raised customized message is displayed instead of oracle defined message.
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END ;
 
 

Note ;- in the above procedure ,the user tries to enter null values to a not-null field(EMPNO field of EMP table) ,which is restricted.