PRAGMA EXCEPTION_INIT help

[kotasateesh]

hi all,
can any one help me how to use PRAGMA EXCEPTION_INIT.
i am not able to get the exact usage and diff between raise_application_error as both are said to be used as same.
can anybody please help me to know exactly what it is

Sateesh

[rotem_fo]

From Oracle PL/SQL Programming, 2nd Edition book by Steven Feuerstein & Bill Pribyl:

The EXCEPTION_INIT pragma

You can use a compiler construct called a pragma to associate a name with an internal error code. A pragma is a special instruction to the compiler that is processed at compile time instead of at runtime. A pragma called EXCEPTION_INIT instructs the compiler to associate or initialize a programmer-defined exception with a specific Oracle error number. With a name for that error, you can then raise this exception and write a handler which will trap that error. While in most cases you will leave it to Oracle to raise these system exceptions, you could also raise them yourself.

The pragma EXCEPTION_INIT must appear in the declaration section of a block, after the declaration of the exception name used in the pragma, as shown below:

DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_code_literal);
BEGIN

where exception_name is the name of an exception and error_code_literal is the number of the Oracle error (including the minus sign, if the error code is negative, as is almost always the case).

In the following program code, I declare and associate an exception for this error:

ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) -
child record found.
This error occurs if I try to delete a parent record while there are child records still in that table. A child record is a record with a foreign key reference to the parent table:

PROCEDURE delete_company (company_id_in IN NUMBER)
IS
/* Declare the exception. */
still_have_employees EXCEPTION;

/* Associate the exception name with an error number. */
PRAGMA EXCEPTION_INIT (still_have_employees, -2292);
BEGIN
/* Try to delete the company. */
DELETE FROM company
WHERE company_id = company_id_in;
EXCEPTION
/* If child records were found, this exception is raised! */
WHEN still_have_employees
THEN
DBMS_OUTPUT.PUT_LINE
(' Please delete employees for company first.');
END;
When you use EXCEPTION_INIT, you must supply a literal number for the second argument of the pragma call. By explicitly naming this system exception, the purpose of the exception handler is self-evident.

The EXCEPTION_INIT pragma improves the readability of your programs by assigning names to otherwise obscure error numbers. You can employ the EXCEPTION_INIT pragma more than once in your program. You can even assign more than one exception name to the same error number.