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
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:
PRAGMA EXCEPTION_INIT (exception_name, error_code_literal);
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)
/* Declare the exception. */
/* Associate the exception name with an error number. */
PRAGMA EXCEPTION_INIT (still_have_employees, -2292);
/* Try to delete the company. */
DELETE FROM company
WHERE company_id = company_id_in;
/* If child records were found, this exception is raised! */
(' Please delete employees for company first.');
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.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.