PRAGMA EXCEPTION_INIT help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PRAGMA EXCEPTION_INIT help

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    PRAGMA EXCEPTION_INIT help

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    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.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width