How to create history table in oralce

Summary
This article describes a classic method of capturing table changes (insert, updates, deletes). Table changes are stored in another table along with user information who did the change.

Create table <Base Table Name>  and the table for history changes <History Table Name EX:- Table_Name_H>

CREATE TABLE <Base Table Name>
AS SELECT * FROM (Another Table Name);

CREATE TABLE <History Table Name EX:- Table_Name_H> 
AS SELECT * FROM <Base Table Name>
WHERE 1=2;

ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (USERNAME   VARCHAR2(30));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (OSUSER   VARCHAR2(30));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (MACHINE   VARCHAR2(64));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (PROGRAM   VARCHAR2(48));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (LOGON_TIME   DATE);
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (CHANGE_TIME   DATE);
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (CHANGE_TYPE   VARCHAR2(10));

TRIGGER CREATION PROCESS:-
====================================

/* Formatted on 9/2/2015 9:12:30 AM (QP5 v5.240.12305.39446) */
CREATE OR REPLACE TRIGGER [Trigger name]
   BEFORE INSERT OR DELETE OR UPDATE
   ON <Base Table Name>
   FOR EACH ROW
DECLARE
   username1     VARCHAR2 (30);
   osuser1       VARCHAR2 (30);
   machine1      VARCHAR2 (64);
   program1      VARCHAR2 (48);
   logon_time1   DATE;
BEGIN
   SELECT USERNAME,
          OSUSER,
          MACHINE,
          PROGRAM,
          LOGON_TIME
     INTO USERNAME1,
          OSUSER1,
          MACHINE1,
          PROGRAM1,
          LOGON_TIME1
     FROM v$session
    WHERE audsid = (SELECT USERENV ('SESSIONID') FROM DUAL);

   IF INSERTING
   THEN
      INSERT INTO <History Table Name EX:- Table_Name_H>
                                     (OWNER,
                                     OBJECT_NAME,
                                     SUBOBJECT_NAME,
                                     OBJECT_ID,
                                     DATA_OBJECT_ID,
                                     OBJECT_TYPE,
                                     CREATED,
                                     LAST_DDL_TIME,
                                     TIMESTAMP,
                                     STATUS,
                                     TEMPORARY,
                                     GENERATED,
                                     SECONDARY,
                                     USERNAME,
                                     OSUSER,
                                     MACHINE,
                                     PROGRAM,
                                     LOGON_TIME,
                                     CHANGE_TIME,
                                     CHANGE_TYPE)
           VALUES (:NEW.OWNER,
                   :NEW.OBJECT_NAME,
                   :NEW.SUBOBJECT_NAME,
                   :NEW.OBJECT_ID,
                   :NEW.DATA_OBJECT_ID,
                   :NEW.OBJECT_TYPE,
                   :NEW.CREATED,
                   :NEW.LAST_DDL_TIME,
                   :NEW.TIMESTAMP,
                   :NEW.STATUS,
                   :NEW.TEMPORARY,
                   :NEW.GENERATED,
                   :NEW.SECONDARY,
                   USERNAME1,
                   OSUSER1,
                   MACHINE1,
                   PROGRAM1,
                   LOGON_TIME1,
                   SYSDATE,
                   'INS');
   ELSIF DELETING
   THEN
      INSERT INTO<History Table Name EX:- Table_Name_H>
                                    (OWNER,
                                     OBJECT_NAME,
                                     SUBOBJECT_NAME,
                                     OBJECT_ID,
                                     DATA_OBJECT_ID,
                                     OBJECT_TYPE,
                                     CREATED,
                                     LAST_DDL_TIME,
                                     TIMESTAMP,
                                     STATUS,
                                     TEMPORARY,
                                     GENERATED,
                                     SECONDARY,
                                     USERNAME,
                                     OSUSER,
                                     MACHINE,
                                     PROGRAM,
                                     LOGON_TIME,
                                     CHANGE_TIME,
                                     CHANGE_TYPE)
           VALUES (:OLD.OWNER,
                   :OLD.OBJECT_NAME,
                   :OLD.SUBOBJECT_NAME,
                   :OLD.OBJECT_ID,
                   :OLD.DATA_OBJECT_ID,
                   :OLD.OBJECT_TYPE,
                   :OLD.CREATED,
                   :OLD.LAST_DDL_TIME,
                   :OLD.TIMESTAMP,
                   :OLD.STATUS,
                   :OLD.TEMPORARY,
                   :OLD.GENERATED,
                   :OLD.SECONDARY,
                   USERNAME1,
                   OSUSER1,
                   MACHINE1,
                   PROGRAM1,
                   LOGON_TIME1,
                   SYSDATE,
                   'DEL');
   ELSIF UPDATING
   THEN
      INSERT INTO <History Table Name EX:- Table_Name_H>
                                     (OWNER,
                                     OBJECT_NAME,
                                     SUBOBJECT_NAME,
                                     OBJECT_ID,
                                     DATA_OBJECT_ID,
                                     OBJECT_TYPE,
                                     CREATED,
                                     LAST_DDL_TIME,
                                     TIMESTAMP,
                                     STATUS,
                                     TEMPORARY,
                                     GENERATED,
                                     SECONDARY,
                                     USERNAME,
                                     OSUSER,
                                     MACHINE,
                                     PROGRAM,
                                     LOGON_TIME,
                                     CHANGE_TIME,
                                     CHANGE_TYPE)
           VALUES (:OLD.OWNER,
                   :OLD.OBJECT_NAME,
                   :OLD.SUBOBJECT_NAME,
                   :OLD.OBJECT_ID,
                   :OLD.DATA_OBJECT_ID,
                   :OLD.OBJECT_TYPE,
                   :OLD.CREATED,
                   :OLD.LAST_DDL_TIME,
                   :OLD.TIMESTAMP,
                   :OLD.STATUS,
                   :OLD.TEMPORARY,
                   :OLD.GENERATED,
                   :OLD.SECONDARY,
                   USERNAME1,
                   OSUSER1,
                   MACHINE1,
                   PROGRAM1,
                   LOGON_TIME1,
                   SYSDATE,
                   'UPD');
   END IF;
END;
/

Script to Give Grant (Read Only) for all Objects to a particular Schema

/* Formatted on 6/23/2014 10:33:45 AM (QP5 v5.115.810.9015) */
DECLARE
   CURSOR cur_grants (p_user varchar2
   )
   IS
      SELECT 'GRANT '
             || DECODE (db.object_type,
                   'TABLE', 'SELECT',
                   'VIEW', 'SELECT',
                   'EXECUTE')
             || ' ON '
             || DECODE (db.owner, 'PUBLIC', '', db.owner || '.')
             || '"'
             || db.object_name
             || '"'
             || ' TO '
             || p_user
                sql_stmt
      FROM all_objects db
      WHERE db.object_type IN
                  ('TABLE',
                   'PACKAGE',
                   'PACKAGE BODY',
                   'PROCEDURE',
                   'VIEW',
                   'FUNCTION')
      UNION
      SELECT 'GRANT '
             || DECODE (ao2.object_type,
                   'TABLE', 'SELECT',
                   'VIEW', 'SELECT',
                   'EXECUTE')
             || ' ON '
             || DECODE (ao.owner, 'PUBLIC', '', ao.owner || '.')
             || '"'
             || ao.object_name
             || '"'
             || ' TO '
             || p_user
                sql_stmt
      FROM all_objects ao, all_objects ao2, dba_synonyms ds
      WHERE     ao.object_type = 'SYNONYM'
            AND ao.object_name = ds.synonym_name
            AND ao2.object_name = ds.table_name
            AND ao2.object_type IN
                     ('TABLE',
                      'PACKAGE',
                      'PACKAGE BODY',
                      'PROCEDURE',
                      'VIEW',
                      'FUNCTION');

   v_user   VARCHAR2 (20) := 'USER_NAME';
BEGIN
   FOR i IN cur_grants (v_user)
   LOOP
      EXECUTE IMMEDIATE i.sql_stmt;

      DBMS_OUTPUT.put_line ('Command : ' || i.sql_stmt);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;
/
-- Please give the User Name in v_user variable

Script to create Synonym for all Objects for a particular Schema

/* Formatted on 6/23/2014 10:31:21 AM (QP5 v5.115.810.9015) */
DECLARE
   CURSOR cur_synonym (p_user varchar2
   )
   IS
      SELECT    'CREATE SYNONYM '
             || p_user
             || '.'
             || db.object_name
             || ' FOR APPS.'
             || db.object_name
                sql_stmt
      FROM dba_objects db
      WHERE db.owner = 'APPS';

   v_user   VARCHAR2 (20) := 'USER_NAME';
BEGIN
   FOR i IN cur_synonym (v_user)
   LOOP
      EXECUTE IMMEDIATE i.sql_stmt;

      DBMS_OUTPUT.put_line ('Command : ' || i.sql_stmt);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;

-- Please give the User Name in v_user variable

How to submit the concurrent request from PLSQL

We first need to initialise oracle applications session (FND_GLOBAL.APPS_INITIALIZE) then only we can call this "FND_REQUEST.SUBMIT_REQUEST".

Syntax

DECLARE
vn_resp_appl_id NUMBER;
vn_resp_id NUMBER;
vn_user_id NUMBER;
vn_request_id NUMBER;
BEGIN

/* Getting Profile option values */
vn_resp_appl_id := apps.fnd_profile.VALUE ('RESP_APPL_ID');
vn_resp_id := apps.fnd_profile.VALUE ('RESP_ID');
vn_user_id := apps.fnd_profile.VALUE ('USER_ID');

/* Application Initialisation */
apps.fnd_global.apps_initialize (n_user_id, n_resp_id, n_resp_appl_id);

/* Concurrent request submission */
vn_request_id := apps.fnd_request.submit_request (
application IN VARCHAR2 DEFAULT NULL,
program IN VARCHAR2 DEFAULT NULL,
description IN VARCHAR2 DEFAULT NULL,
start_time IN VARCHAR2 DEFAULT NULL,
sub_request IN BOOLEAN DEFAULT FALSE,
argument1 IN VARCHAR2 DEFAULT CHR (0),
argument2 IN VARCHAR2 DEFAULT CHR (0),
argument3 IN VARCHAR2 DEFAULT CHR (0),
----- argument4 to argument99 -------------
argument100 IN VARCHAR2 DEFAULT CHR (0)
);

/* Commit */
COMMIT;
END;

FND_REQUEST.SUBMIT_REQUEST {Description}

application : Short name of application under which the program is registered.
program : concurrent program name for which the request has to be submitted.
description : [Optional] Will be displayed along with user concurrent program name.
start_time : [Optional] Time at which the request has to start running.
sub_request : [Optional] Set to TRUE if the request is submitted from another running request and has to be treated as a sub request. Default is FALSE
argument1..100 : [Optional] Arguments {parameters} for the concurrent req

Sequence in Oracle

What is a Sequence?

• A sequence is a user created database object that can be shared by multiple users to generate unique integers.
• A typical usage for sequences is to create a primary key value, which must be unique for each row.
• The sequence is generated and incremented (or decremented) by an internal Oracle routine.
• This can be a time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
• Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:

CREATE SEQUENCE sequence_name [START WITH start_num] [INCREMENT BY increment_num] [ { MAXVALUE maximum_num | NOMAXVALUE } ] [ { MINVALUE minimum_num | NOMINVALUE } ] [ { CYCLE | NOCYCLE } ] [ { CACHE cache_num | NOCACHE } ] [ { ORDER | NOORDER } ]

CREATE SEQUENCE sequence_name [START WITH start_num] [INCREMENT BY increment_num] [ { MAXVALUE maximum_num | NOMAXVALUE } ] [ { MINVALUE minimum_num | NOMINVALUE } ] [ { CYCLE | NOCYCLE } ] [ { CACHE cache_num | NOCACHE } ] [ { ORDER | NOORDER } ]

where

1. The default start_num is 1.
2. The default increment number is 1.
3. The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
4. minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
5. NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
6. NOMINVALUE is the default.
7. maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
8. NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
9. NOMAXVALUE is the default.
10. CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
11. When an ascending sequence reaches its maximum value, the next value generated is the minimum.
12. When a descending sequence reaches its minimum value, the next value generated is the maximum.
13. NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
14. NOCYCLE is the default.
15. CACHE cache_num specifies the number of integers to keep in memory.
16. The default number of integers to cache is 20.
17. The minimum number of integers that may be cached is 2.
18. The maximum integers that may be cached is determined by the formula CEIL(maximum_num – minimum_num)/ABS(increment_num).
19. NOCACHE specifies no integers are to be stored.
20. ORDER guarantees the integers are generated in the order of the request.
21. You typically use ORDER when using Real Application Clusters.
22. NOORDER doesn’t guarantee the integers are generated in the order of the request.
23. NOORDER is the default

Creating a sequence and then get the next value

CREATE SEQUENCE test_seq; Sequence created. SELECT test_seq.nextval FROM DUAL; NEXTVAL ---------- 1 INSERT INTO test VALUES (test_seq.nextval,'Record A'); ---Using a sequence to populate a table's column

CREATE SEQUENCE test_seq;   Sequence created.   SELECT test_seq.nextval FROM DUAL;   NEXTVAL ----------     1   INSERT INTO test VALUES (test_seq.nextval,'Record A');                   ---Using a sequence to populate a table's column

Once initialized, you can get the current value from the sequence using currval.

SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 1 SELECT test_seq.currval FROM dual; CURRVAL ---------- 1

SELECT test_seq.nextval FROM dual;   NEXTVAL ----------      1 SELECT test_seq.currval FROM dual;   CURRVAL ----------      1

You can’t use CURRVAL just after a sequence creation. It will throw an error.

create sequence deptno_seq start with 50 increment by 10; Sequence created. select deptno_seq.currval from dual; select deptno_seq.currval * ERROR at line 1: ORA-08002: sequence DEPTNO_SEQ.CURRVAL is not yet defined in this session select deptno_seq.currval, deptno_seq.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 50 50 ----Will Work

create sequence deptno_seq start with 50 increment by 10;   Sequence created.   select deptno_seq.currval from dual;   select deptno_seq.currval        * ERROR at line 1: ORA-08002: sequence DEPTNO_SEQ.CURRVAL is not yet defined in this session   select deptno_seq.currval, deptno_seq.nextval from dual;   CURRVAL    NEXTVAL ---------- ----------      50         50               ----Will Work

When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value.

SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 1 SELECT test_seq.nextval, test_seq.currval FROM dual; NEXTVAL CURRVAL --------- --------- 2 2

SELECT test_seq.nextval FROM dual;   NEXTVAL ----------      1 SELECT test_seq.nextval, test_seq.currval FROM dual;   NEXTVAL    CURRVAL ---------   ---------      2          2

Getting Information on Sequences
You get information on your sequences from user_sequences.

desc user_sequences; Name Null? Type ------------------------------------------------------------------- SEQUENCE_NAME NOT NULL VARCHAR2(30) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER select * from user_sequences;

desc user_sequences;   Name                    Null?             Type ------------------------------------------------------------------- SEQUENCE_NAME     NOT NULL               VARCHAR2(30) MIN_VALUE                                NUMBER MAX_VALUE                                NUMBER INCREMENT_BY        NOT NULL            NUMBER CYCLE_FLAG                               VARCHAR2(1) ORDER_FLAG                               VARCHAR2(1) CACHE_SIZE           NOT NULL              NUMBER LAST_NUMBER         NOT NULL             NUMBER   select * from user_sequences;

Modifying a Sequence

• You modify a sequence using the ALTER SEQUENCE statement.
• You cannot change the start value of a sequence.
• The minimum value cannot be more than the current value of the sequence ( currval ).
• The maximum value cannot be less than the current value of the sequence ( currval ).

Removing a Sequence
• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
• Once removed, the sequence can no longer be referenced.

DROP SEQUENCE dept_deptid_seq; Sequence dropped.

DROP SEQUENCE dept_deptid_seq;   Sequence dropped.

Exception Handling in Oracle

What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.
An exception is an identifier in PL/SQL that is raised during the execution of a block that terminates its main body of actions. A block always terminates when PL/SQL raises an exception, but can you specify an exception handler to perform final actions.

Types of Exception

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
Named System Exceptions (or Predefined Oracle Server Exceptions) and Unnamed System Exceptions (or Nonpredefined Oracle Server Exceptions) are implicitly raised.
User-defined Exceptions are explicitly raised.

Structure of Exception Handling

DECLARE Declaration section BEGIN Exception section EXCEPTION WHEN ex_name1 THEN -Error handling statements WHEN ex_name2 THEN -Error handling statements WHEN Others THEN -Error handling statements END;

DECLARE    Declaration section BEGIN    Exception section EXCEPTION   WHEN ex_name1 THEN      -Error handling statements   WHEN ex_name2 THEN      -Error handling statements   WHEN Others THEN      -Error handling statements END;

You can trap any error by including a corresponding routine within the exception handling section of the PL/SQL block. Each handler consists of a WHEN clause, which specifies an exception, followed by a sequence of statements to be executed when that exception is raised.
The exception-handling section traps only those exceptions that are specified; any other exceptions are not trapped unless you use the OTHERS exception handler.

Exceptions Trapping Rules:

• Begin the exception-handling section of the block with the EXCEPTION keyword.
• You can define several exception handlers, each with its own set of actions, for the block.
• When an exception occurs, PL/SQL processes only one handler before leaving the block.
• Place the OTHERS clause after all other exception-handling clauses.
• WHEN OTHERS is the last clause and you can have only one OTHERS clause.

a) Named System Exceptions
System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.
Note: PL/SQL declares predefined exceptions in the STANDARD package.
Few Predefined Exceptions:

• NO_DATA_FOUND (ORA-01403) — When a SELECT…INTO clause does not return any row from a table.
• TOO_MANY_ROWS (ORA-01422) — When you SELECT or fetch more than one row into a record or variable.
• ZERO_DIVIDE (ORA-01476) — When you attempt to divide a number by zero.
• CURSOR_ALREADY_OPEN (ORA-06511) — You tried to open a cursor that is already open.
• INVALID_CURSOR (ORA-01001) — Illegal cursor operation occurred. You tried to reference a cursor that does not yet exist. This may have happened because you’ve executed a FETCH cursor or CLOSE cursor before Opening the cursor.
• INVALID_NUMBER (ORA-01722) — You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.
• DUP_VAL_ON_INDEX (ORA-00001) — Attempted to insert a duplicate value.
• LOGIN_DENIED (ORA-01017) — You tried to log into Oracle with an invalid username/password combination.
• NOT_LOGGED_ON (ORA-01012) — You tried to execute a call to Oracle before logging in.
• VALUE_ERROR (ORA-06502) — You tried to perform an operation and there was an error on a conversion, truncation, or invalid constraining of numeric or character data.

Named system exceptions are:

• Not declared explicitly.
• Raised implicitly when a predefined Oracle error occurs.
• Caught by referencing the standard name within an exception-handling routine.

For Example:

BEGIN Execution section EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('A SELECT...INTO did not return any row.'); END;

BEGIN   Execution section EXCEPTION   WHEN NO_DATA_FOUND THEN   dbms_output.put_line ('A SELECT...INTO did not return any row.'); END;

b) Unnamed System Exceptions
Those system exception for which oracle does not provide a name is known as unnamed system exception. These exceptions do not occur frequently. These Exceptions have a code and an associated message.
There are two ways to handle unnamed system exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.
We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer defined exception name.
Steps to be followed to use unnamed system exceptions are

• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicitly.
• To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT as given above and handled referencing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, Err_code); BEGIN Execution section EXCEPTION WHEN exception_name THEN Handle the exception END;

DECLARE    exception_name EXCEPTION;    PRAGMA    EXCEPTION_INIT (exception_name, Err_code); BEGIN    Execution section EXCEPTION    WHEN exception_name THEN      Handle the exception END;

For Example:
Let’s trap for Oracle server error number –2292, which is an integrity constraint violation.
1] Declare the name for the exception within the declarative section.
exception EXCEPTION;
Where: exception is the name of the exception.
2.] Associate the declared exception with the standard Oracle server error number using the
PRAGMA EXCEPTION_INIT statement.
PRAGMA EXCEPTION_INIT(exception, error_number);
Where: exception is the previously declared exception.
error_number is a standard Oracle Server error number.
3] Reference the declared exception within the corresponding exception-handling routine.

DEFINE p_deptno = 10 DECLARE e_emps_remaining EXCEPTION; PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292); BEGIN DELETE FROM departments WHERE department_id = &p_deptno; COMMIT; EXCEPTION WHEN e_emps_remaining THEN DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' || TO_CHAR(&p_deptno) || '. Employees exist. '); END;

DEFINE p_deptno = 10 DECLARE      e_emps_remaining EXCEPTION;      PRAGMA EXCEPTION_INIT(e_emps_remaining, -2292); BEGIN      DELETE FROM departments      WHERE department_id = &p_deptno;      COMMIT; EXCEPTION      WHEN e_emps_remaining THEN      DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||      TO_CHAR(&p_deptno) || '. Employees exist. '); END;

c) User-defined Exceptions
Apart from system exceptions we can explicitly define exceptions based on business rules. These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.
For Example:

DECLARE e_invalid_department EXCEPTION; BEGIN UPDATE departments SET department_name = '&p_department_desc' WHERE department_id = &p_department_number; IF SQL%NOTFOUND THEN RAISE e_invalid_department; END IF; COMMIT; EXCEPTION WHEN e_invalid_department THEN DBMS_OUTPUT.PUT_LINE('No such department id.'); END;

DECLARE      e_invalid_department EXCEPTION; BEGIN     UPDATE departments     SET department_name = '&p_department_desc'     WHERE department_id = &p_department_number;     IF SQL%NOTFOUND THEN       RAISE e_invalid_department;     END IF;     COMMIT; EXCEPTION     WHEN e_invalid_department THEN     DBMS_OUTPUT.PUT_LINE('No such department id.'); END;

Functions for Trapping Exceptions:
When an exception occurs, you can identify the associated error code or error message by using two functions. Based on the values of the code or message, you can decide which subsequent action to take based on the error.
• SQLCODE: Returns the numeric value for the error code. 
• SQLERRM: Returns the message associated with the error number.
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:

DECLARE err_num NUMBER; err_msg VARCHAR2(100); BEGIN ... EXCEPTION ... WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO errors VALUES (err_num, err_msg); END;

DECLARE     err_num NUMBER;     err_msg VARCHAR2(100); BEGIN     ... EXCEPTION     ...   WHEN OTHERS THEN     err_num := SQLCODE;     err_msg := SUBSTR(SQLERRM, 1, 100);     INSERT INTO errors VALUES (err_num, err_msg); END;

RAISE_APPLICATION_ERROR ( ) :
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message);
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The returned error is consistent with how the Oracle server produces a predefined, nonpredefined, or user-defined error. The error number and message is displayed to the user.
Executable section:

BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (-20202,'This is not a valid manager'); END IF; ...

BEGIN ... DELETE FROM employees WHERE manager_id = v_mgr; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (-20202,'This is not a valid manager'); END IF; ...

Exception section:

... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.'); END;

... EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.'); END;

What is Exception propagation in Oracle? 
Exceptions which are not handled in a sub block get propagated to the outer block. When an exception occurs, it terminates from the line where the exception occurs and the control goes to the calling program or the next outer block. If not handled in the outer block, it terminates that block and propagates to the next outer block and so on. And, if exception occurs in the outermost block, then the whole program gets terminated.