Oracle Predefined Exceptions

Oracle Predefined exceptions – Learn about how exception occurs and how to handle errors with Examples.

Exception

It is an error that occur at the time of program execution (Runtime).
This type of Exceptions arise due to improper logic(Coding mistakes) in PL-SQL block, design faults, hardware failures during the program execution.

Exceptions- This are internally defined (by the run-time system) or user defined.
Internally defined exceptions include division by zero and out of memory.
Exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR.

Exception divided into 2 types.

1. System defined Exceptions – Predefined exceptions defined in Oracle.
2. User defined Exceptions. – User-defined exceptions are exceptions specific to your application.

Oracle Predefined exceptions-Named System Exceptions

When referenced an uninitialized row in a table.

Predefined Exceptions	Explanation
DUP_VAL_ON_INDEX	when trying to execute insert or update statements on unique columns in tables.
NO_DATA_FOUND	When executing select into statement and no rows were returned. When referenced an uninitialized row in a table.
TOO_MANY_ROWS	When executing select into statement and more than one row was returned
ZERO_DIVIDE	When tried to divide a number by zero.
INVALID_NUMBER	when tried to execute a SQL statement that tried to convert a string to a number, but it was unsuccessful.
VALUE_ERROR	when tried to perform an operation and there was a error on a conversion, truncation, or invalid conversion of numeric or character data.
CURSOR_ALREADY_OPEN	When tried to open a cursor that was already open and not closed.
LOGIN_DENIED	when tried to log into Oracle with an invalid username/password combination.
INVALID_CURSOR	When 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 OPEN the cursor.
NOT_LOGGED_ON	When tried to execute a call to Oracle before logging in.
WHEN OTHERS	clause is used to trap all remaining exceptions that have not been handled by your Named System exceptions and Named Programmer-Defined Exceptions.

Syntax for Procedures

CREATE [OR REPLACE] PROCEDURE procedure_name
   [ (parameter [,parameter]) ]
IS
   [declaration_section]

BEGIN executable_section EXCEPTION WHEN exception_name1 THEN
[statements]

WHEN exception_name2 THEN
[statements]

WHEN exception_name_n THEN
[statements]

WHEN OTHERS THEN
[statements]

END [procedure_name];

Example Program for ZERO_DIVIDE Error

DECLARE
  A NUMBER :=20;
  B NUMBER :=0;
  C NUMBER;
BEGIN
  C:=A/B;
EXCEPTION
WHEN ZERO_DIVIDE THEN
  DBMS_OUTPUT.PUT_LINE('Number Cannot  Divide by Zero');
END;

Output

Number Cannot Divide by Zero

Example Program for NO_DATA_FOUND Error

DECLARE
  E_NAME NUMBER;
BEGIN
  SELECT NAME INTO E_NAME FROM employee_tbl1 WHERE ID=11;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('No Data Found for the ID - 11');
END;

Output

No Data Found for the ID – 11

Example Program for TOO_MANY_ROWS Error

DECLARE
  E_NAME NUMBER;
BEGIN
  SELECT NAME 
    INTO E_NAME 
FROM employee_tbl1 WHERE DepartmentID=1;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('More than one record 
         exist with the DepartmentID -1');
END;

Output
More than one record exist with the DepartmentID -1

Example Program for DUP_VAL_ON_INDEX Error

DECLARE
  E_NAME NUMBER;
BEGIN
  INSERT INTO Department_TBL1 VALUES
    (2, 'Payroll', 'Delhi', 'Ron'
    );
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
  DBMS_OUTPUT.PUT_LINE('Trying to insert into 
  Duplicate Values into Department_TBL1');
END;

Output
Trying to insert into Duplicate Values into Department_TBL1

Example Program for VALUE_ERROR Error

DECLARE
  A NUMBER ;
  B VARCHAR2(10) :='ABC';
BEGIN
  A :=B;
EXCEPTION
WHEN VALUE_ERROR THEN
  DBMS_OUTPUT.PUT_LINE('String value to 
   integer value conversion error');
END;

Output:
String value to integer value conversion error

Example Program for INVALID_NUMBER Error

DECLARE
BEGIN
  INSERT
  INTO Department_TBL1 VALUES
    (
      'ABC',
      'COMMUNICATION',
      'Sydney',
      'Cindrella'
    );
EXCEPTION
WHEN INVALID_NUMBER THEN
  DBMS_OUTPUT.PUT_LINE('INVALID NUMBER INSERTION 
  INTO Department_TBL1');
END;

Syntax for Functions

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
IS | AS
   [declaration_section]