Pre defined Exception.

Pre defined (System defined) Exception:

• Predefined exception are raised automatically by the system during run time.
• Predefined exception are already available in the program it is not necessary to declare them in the declarative section like user_defined exception.

Predefined Exception List:

Exception Name	Error No	Description
ORA-0001	DUP_VAL_ON_INDEX	Unique constraint violated.
ORA-1001	INVALID_CURSOR	Illegal cursor operation.
ORA-1403	NO_DATA_FOUND	No data found.
ORA-1422	TOO_MANY_ROWS	A SELECT INTO statement Matches more than one row.
ORA-1722	INVALID_NUMBER	Conversion to a number Failed for example, ‘netlojava street 1’  not valid.
ORA-6502	VALUE_ERROR	Truncation, arithmetic, or Conversion error.
ORA-01476	ZERO_DIVIDE	Divisor is equal to zero.
ORA-06511	CURSOR_ALREADY_OPEN	This exception raised when we try to open a cursor   which is already opened.
ORA-01017	LOGIN_DENIED	This exception is raised When we try to enter oracle using invalid username/password.

 
Examples:
SQL>DECLARE
       v_empno emp.empno%TYPE:=&empno;
       v_ename emp.ename%TYPE;
       v_job emp.job%TYPE;
    BEGIN
       SELECT empno=v_empno;
       DBMS_OUTPUT.PUT_LINE(‘The empno detail are ‘||v_ename||’ ‘||v_job);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(‘The empno is not found.’);
    END;
SQL>DECLARE
       v_accno kcb_acc_tab.accno%TYPE:=&accno;
       v_name kcb_acc_tab.name%TYPE:=&name;
       v_bal kcb_acc_tab.bal%TYPE:=&bal;
    BEGIN
       INSERT INTO kcb_acc_tab(accno,name,bal)
       VALUES(v_accno,v_name,v_bal);
       DBMS_OUTPUT.PUT_LINE(‘Account detailes are inserted successfully’);
    EXCEPTION
       WHEN DUP_VAL_ON_INDEX THEN
       DBMS_OUTPUT.PUT_LINE(‘accno already exists’);
    END;
SQL>DECLARE
       v_empno emp.empno%TYPE;
       v_ename emp.ename%TYPE;
       v_deptno emp.deptno%TYPE;
    BEGIN
       SELECT empno,ename,deptno INTO v_empno,v_ename,v_deptno
       FROM emp
       WHERE empno=7788 AND ename=’SCOTT’;
       DBMS_OUTPUT.PUT_LINE(‘The scott works in department number:’||v_deptno);
       Select empno,ename,deptno into v_empno,v_ename,v_deptno
       FROM emp
       Where deptno=10;
       DBMS_OUTPUT.PUT_LINE(‘The Employee number:’||v_empno);
       DBMS_OUTPUT.PUT_LINE(‘The Employee name:’||v_ename);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE(‘Error:There is no such empno or ename or deptno’);
       WHEN TOO_MANY_ROWS THEN
       DBMS_OUTPUT.PUT_LINE(‘Error:More than one Employee works in department number 10’);
      WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(‘Error occurred while processing the program’);    
    END;