HowToOracle:- Everything about exceptions

Every thing about Oracle Exception Handling
================================================
Facts:-
1. There are 2 kinds of exceptions:-
System defined / pre-defined exceptions
Programmer-Defined Exceptions

2. System defined exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows:

Oracle Exception Name
Oracle Error
Explanation

DUP_VAL_ON_INDEX
ORA-00001
You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

TIMEOUT_ON_RESOURCE
ORA-00051
You were waiting for a resource and you timed out.

TRANSACTION_BACKED_OUT
ORA-00061
The remote portion of a transaction has rolled back.

INVALID_CURSOR
ORA-01001
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.

NOT_LOGGED_ON
ORA-01012
You tried to execute a call to Oracle before logging in.

LOGIN_DENIED
ORA-01017
You tried to log into Oracle with an invalid username/password combination.

NO_DATA_FOUND
ORA-01403
You tried one of the following:
You executed a SELECT INTO statement and no rows were returned.
You referenced an uninitialized row in a table.
You read past the end of file with the UTL_FILE package.

TOO_MANY_ROWS
ORA-01422
You tried to execute a SELECT INTO statement and more than one row was returned.

ZERO_DIVIDE
ORA-01476
You tried to divide a number by zero.

INVALID_NUMBER
ORA-01722
You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.

STORAGE_ERROR
ORA-06500
You ran out of memory or memory was corrupted.

PROGRAM_ERROR
ORA-06501
This is a generic "Contact Oracle support" message because an internal problem was encountered.

VALUE_ERROR
ORA-06502
You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.

CURSOR_ALREADY_OPEN
ORA-06511
You tried to open a cursor that is already open.


Sample Procedure describing handling NO_DATA_FOUND error:-
---------------------------------------------------------------
CREATE OR REPLACE PROCEDURE proc_named_exception
AS
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename FROM emp WHERE ename = 'SMITH';
Dbms_Output.put_line('Employe name is = '||v_ename); -- Valid record willbe shown in output
/* the following will throw NO_DATA_FOUND exception */
SELECT ename INTO v_ename FROM emp WHERE ename = 'TITIKAKA';
Dbms_Output.put_line('Employe name is = '||v_ename); -- NO_DATA_FOUND, so control will go to exception block
-- Nothing will be shown in output
SELECT ename INTO v_ename FROM emp WHERE ename = 'KING';
Dbms_Output.put_line('Employe name is = '||v_ename);
EXCEPTION
WHEN No_Data_Found THEN
Dbms_Output.put_line('no data found');
END;

Facts 3:- By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, you might put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN-END block with an exception handler inside a loop, you can continue executing the loop even if
some loop iterations raise exceptions.See sample 2 below.

Sample 2 Procedure describing handling NO_DATA_FOUND and DUP_VAL_ON_INDEX error:-
-------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE proc_named_exception1
AS
v_ename emp.ename%TYPE;
BEGIN
BEGIN -- This block is to handle NDF
SELECT ename INTO v_ename FROM emp WHERE ename = 'SMITH';
Dbms_Output.put_line('Employe name is = '||v_ename); -- Valid record willbe shown in output
/* the following will throw NO_DATA_FOUND exception */
SELECT ename INTO v_ename FROM emp WHERE ename = 'TITIKAKA';
Dbms_Output.put_line('Employe name is = '||v_ename); -- NO_DATA_FOUND, so control will go to exception block
-- Nothing will be shown in output
SELECT ename INTO v_ename FROM emp WHERE ename = 'KING';
Dbms_Output.put_line('Employe name is = '||v_ename);
EXCEPTION
WHEN No_Data_Found THEN
Dbms_Output.put_line('NO_DATA_FOUND');
END;

BEGIN -- This block is to handle DVOI
INSERT INTO emp VALUES(7369,'Hasim','Admin',9898,SYSDATE,7000,NULL,10);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
Dbms_Output.put_line('DUP_VAL_ON_INDEX');
END;
END;


Fact 4:- You can handle more than one exception in a single handler as follows:-

EXCEPTION
When NO_DATA_FOUND or TOO_MANY_ROWS then
Statements;
END;

Fact 5:- A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as

exception.They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to

raise internal exceptions.

Declaring Exception:
--------------------

DECLARE
myexception EXCEPTION;
BEGIN

Raising Exception:
------------------
BEGIN
RAISE myexception;

Handling Exception:
----------------------
BEGIN
EXCEPTION
WHEN myexception THEN
Statements;
END;

Sample 3 Procedure addemp in the package is having an user defined exception
-------------------------------------------------------------------------------

PROMPT CREATE OR REPLACE PACKAGE hasim_pack2
CREATE OR REPLACE PACKAGE hasim_pack2 AS
PROCEDURE addemp;
PROCEDURE delemp;
END hasim_pack2;
/

PROMPT CREATE OR REPLACE PACKAGE BODY hasim_pack2
CREATE OR REPLACE PACKAGE BODY hasim_pack2 AS
PROCEDURE addemp IS
is_exists EXCEPTION;
cnt NUMBER :=0;
BEGIN
SELECT Count(1) INTO cnt FROM emp WHERE empno IN (9898,9897);
IF cnt > 0 THEN
RAISE is_exists;
else
INSERT ALL
INTO emp VALUES(9898,'Timothy','CEO',7839,SYSDATE,7000,NULL,10)
INTO emp VALUES(9897,'Hasim','Admin',9898,SYSDATE,7000,NULL,10)
SELECT * FROM dual;
COMMIT;
END IF;
EXCEPTION
WHEN is_exists THEN
Raise_Application_Error(-20001,'You must delete Timothy/hasim first');
WHEN OTHERS then
Raise_Application_Error(-20002,'Some error happened while inserting records');
END addemp;


PROCEDURE delemp IS
begin
DELETE FROM emp WHERE empno IN (9898,9897);
COMMIT;
END delemp;

END hasim_pack2;
/

Fact 6:-
An Exception cannot be declared twice in the same block.
Exceptions declared in a block are considered as local to that block and global to its sub-blocks.An enclosing block cannot access Exceptions declared in its sub-block. Where as it possible for a sub-block to refer its enclosing Exceptions.

Sample 4 Exceptions raised in sub-block is handled by main block
-----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE proc_named_exception3
AS
v_ename emp.ename%TYPE;
res NUMBER;
BEGIN
BEGIN -- This block is to handle TMR
SELECT ename INTO v_ename FROM emp WHERE job = 'PRESIDENT';
Dbms_Output.put_line('President Employe name is = '||v_ename); -- Valid record willbe shown in output
SELECT (10/2) INTO res FROM dual;
Dbms_Output.put_line('10/2 = '||res); -- Valid record willbe shown in output
SELECT (10/0) INTO res FROM dual;
Dbms_Output.put_line('10/0 = '||res); -- Zero_Divide,no record willbe shown in output,exception catcher is in main block
EXCEPTION
WHEN TOO_MANY_ROWS THEN
Dbms_Output.put_line('TOO_MANY_ROWS');
END;

BEGIN -- This block is to handle DVOI
INSERT INTO emp VALUES(7369,'Hasim','Admin',9898,SYSDATE,7000,NULL,10);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
Dbms_Output.put_line('DUP_VAL_ON_INDEX');
END;
EXCEPTION
WHEN Zero_Divide THEN
Dbms_Output.put_line('Zero_Divide');
END;


Fact 7:- RAISE_APPLICATION_ERROR
To display your own error messages one can use the built-in RAISE_APPLICATION_ERROR. They display the error message in the same way as Oracle errors. You should use a negative number between –20000 to –20999 for the error_number and the error message should not exceed 512 characters. Check sample 3.

Fact 8:- The WHEN OTHERS clause is used to trap all remaining exceptions that have not been handled by the System defined Exceptions and Programmer-Defined Exceptions.

Sample 5 Usage of when others in the previous addemp procedure
----------------------------------------------------------------
create or replace PROCEDURE addemp1 IS
is_exists EXCEPTION;
cnt NUMBER :=0;
BEGIN
SELECT Count(1) INTO cnt FROM emp WHERE empno IN (9898,9897);
IF cnt > 0 THEN
RAISE is_exists;
else
INSERT ALL
INTO emp VALUES(9898,'Timothy','CEO',7839,SYSDATE,7000,NULL,10)
INTO emp VALUES(9897,'Hasim','Admin',9898,SYSDATE,7000,NULL,10)
SELECT * FROM dual;
COMMIT;
END IF;
SELECT (10/0) INTO cnt FROM dual;
EXCEPTION
WHEN is_exists THEN
Raise_Application_Error(-20001,'You must delete Timothy/hasim first');
WHEN OTHERS then
Raise_Application_Error(-20002,'Some error happened while inserting records');
END addemp1;



SQL> exec addemp1;
BEGIN addemp1; END;

*
ERROR at line 1:
ORA-20001: You must delete Timothy/hasim first
ORA-06512: at "SCOTT.ADDEMP1", line 17
ORA-06512: at line 1

SQL> exec hasim_pack2.delemp;

PL/SQL procedure successfully completed.

SQL> exec addemp1;
BEGIN addemp1; END;

*
ERROR at line 1:
ORA-20002: Some error happened while inserting records
ORA-06512: at "SCOTT.ADDEMP1", line 20
ORA-06512: at line 1


SQL>


Fact 9:- The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code.

Sample 5 Usage of when others in the previous addemp procedure
----------------------------------------------------------------
create or replace PROCEDURE addemp2 IS
is_exists EXCEPTION;
cnt NUMBER :=0;
BEGIN
SELECT Count(1) INTO cnt FROM emp WHERE empno IN (9898,9897);
IF cnt > 0 THEN
RAISE is_exists;
else
INSERT ALL
INTO emp VALUES(9898,'Timothy','CEO',7839,SYSDATE,7000,NULL,10)
INTO emp VALUES(9897,'Hasim','Admin',9898,SYSDATE,7000,NULL,10)
SELECT * FROM dual;
COMMIT;
END IF;
SELECT (10/0) INTO cnt FROM dual;
EXCEPTION
WHEN is_exists THEN
Raise_Application_Error(-20001,'You must delete Timothy/hasim first');
WHEN OTHERS then
Raise_Application_Error(-20002,'Some error happened while inserting records'||SQLCODE||' -ERROR- '||SQLERRM);
END addemp2;

Samplerun:-
SQL> exec hasim_pack2.delemp;

PL/SQL procedure successfully completed.

SQL> exec addemp2;
BEGIN addemp2; END;

*
ERROR at line 1:
ORA-20002: Some error happened while inserting records-1476 -ERROR- ORA-01476:
divisor is equal to zero
ORA-06512: at "SCOTT.ADDEMP2", line 20
ORA-06512: at line 1