What are the different types of pragma and where can we use them?

========================================================================

What are the different types of pragma and where can we use them?
Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.

The syntax for pragmas are as follows
PRAMA
The instruction is a statement that provides some instructions to the compiler.
Pragmas are defined in the declarative section in PL/SQL.
The following pragmas are available:
AUTONOMOUS_TRANSACTION:
Prior to Oracle 8.1, each Oracle session in PL/SQL could have at most one active transaction at a given time. In other words, changes were all or nothing. Oracle8i PL/SQL addresses that short comings with the AUTONOMOUS_TRANSACTION pragma. This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction. For instance, if rollback or commit needs to take place within the block without effective the transaction outside the block, this type of pragma can be used.
EXCEPTION_INIT:
The most commonly used pragma, this is used to bind a user defined exception to a particular error number.
For example:

Declare

 I_GIVE_UP   EXCEPTION;

 PRAGMA EXCEPTION_INIT(I_give_up, -20000);

BEGIN

  ..

         EXCEPTION WHEN I_GIVE_UP

do something..

END;

RESTRICT_REFERENCES:
Defines the purity level of a packaged program. This is not required starting with Oracle8i.
Prior to Oracle8i if you were to invoke a function within a package specification from a SQL statement, you would have to provide a RESTRICT_REFERENCE directive to the PL/SQL engine for that function.

Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in Example 10-4.

Example 10-4 Using PRAGMA EXCEPTION_INIT

DECLARE

   deadlock_detected EXCEPTION;

   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);

BEGIN

   NULL; -- Some operation that causes an ORA-00060 error

EXCEPTION

   WHEN deadlock_detected THEN

      NULL; -- handle the error

END;

/

Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR

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.
In Example 10-5, you call raise_application_error if an error condition of your choosing happens (in this case, if the current schema owns less than 1000 tables):

Example 10-5 Raising an Application Error With raise_application_error

DECLARE

   num_tables NUMBER;

BEGIN

   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;

   IF num_tables < 1000 THEN

      /* Issue your own error code (ORA-20101) with your own error message. 

         Note that you do not need to qualify raise_application_error with 

         DBMS_STANDARD */

      raise_application_error(-20101, 'Expecting at least 1000 tables');

   ELSE

      NULL; -- Do the rest of the processing (for the non-error case).

   END IF;

END;

/

The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. Also, it can use the pragma EXCEPTION_INIT to map specific error numbers returned by raise_application_error to exceptions of its own, as the following Pro*C example shows:

EXEC SQL EXECUTE
  /* Execute embedded PL/SQL block using host
    variables v_emp_id and v_amount, which were
    assigned values in the host environment. */
  DECLARE
    null_salary EXCEPTION;
    /* Map error number returned by raise_application_error
      to user-defined exception. */
    PRAGMA EXCEPTION_INIT(null_salary, -20101);
  BEGIN
    raise_salary(:v_emp_id, :v_amount);
  EXCEPTION
    WHEN null_salary THEN
      INSERT INTO emp_audit VALUES (:v_emp_id, ...);
  END;
END-EXEC;

This technique allows the calling application to handle error conditions in specific exception handlers.

Redeclaring Predefined Exceptions

Remember, PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself. Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. For example, if you declare an exception named invalid_number and then PL/SQL raises the predefined exception INVALID_NUMBER internally, a handler written for INVALID_NUMBER will not catch the internal exception. In such cases, you must use dot notation to specify the predefined exception, as follows:

EXCEPTION
  WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
    -- handle the error
END;

========================================================================