Raising Exceptions:
Raising Exceptions means the exception declared through the user is raised explicitly unlike Predefined exceptions. A keyword RAISE is used to perform this operation. Syntax for using raising exceptions is:
RAISE ex1;
RAISE_APPLICATION_ERROR
Raise_Application_Error stops the program and terminates the block abruptly. It is primarily used in triggers to rollback the transaction and provides suitable error messages. Syntax for this error is as follows:
RAISE_APPLICATION_ERROR ( errornumber , MESSAGE);
where the error messages span among -20000 and -20999. Message is a character data which can hold upto 2000 bytes. The following example describes this:
In the following instance, if an employee's salary is missing raise_application_error is called:
DECLARE
Emp_id number := &no;
current_salary NUMBER;
increase number:=&x;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
/* Issue user-defined error message. */
raise_application_error(-20101, 'Salary is missing');
ELSE
UPDATE emp SET sal = current_salary + increase
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'NO MATCHING RECORD');
END raise_salary;
This program accepts the employee number and the salary increments. If the subsequent employee is found it should update the value else it raises an error. Suppose 765 is a number that is not available in the table. The error message in this case would be:
ERROR at line 1:
ORA-20000: NO RECORD MATCHING
ORA-06512: at line 17
Using EXCEPTION_INIT - Use of PRAGMA
To handle unnamed internal exceptions, you must use the pragma EXCEPTION_INIT or OTHERS handler. The pragma is a compiler directive that can be thought of as a parenthetical remark to the compiler. Pragmas (also known as pseudo instructions) are processed at compile time not at the run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to relate an exception name with an Oracle error number. That permits any internal exception to be referenced through name and to write a specific handler for it.
PRAGMA EXCEPTION_INIT is provided in the declarative part of a PL/SQL block, package or subprogram using the syntax that is:
PRAGMA EXCEPTION_INIT (exception_name, Oracle_error_number);
where exception_name is the name of a previously declared exception. The pragma should be appearing somewhere after the exception declaration in the similar declarative part, as described in the following example:
DECLARE
X EXCEPTION;
PRAGMA EXCEPTION_INIT(X,-1400);
BEGIN
INSERT INTO EMP(EMPNO) VALUES(NULL);
EXCEPTION
WHEN X THEN
DBMS_OUTPUT.PUT_LINE('VALUE CANNOT HOLD NULL VALUES');
END;
In the given example, if a null value is being inserted onto a column that cannot contain null values, an exception is raised.