Go Back

PLSQL 4

PL/SQL -> PLSQLBasics

INTRODUCTION TO PL/SQL 3:

PLSQL Exceptions:

EXCEPTION handling: :
  
DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
END;

SQL> /
Enter value for department_no: 10/

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 8 

Handling exception with EXCEPTION handler:

  
DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, 
           v_ename, 
           v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN no_data_found THEN 
      dbms_output.Put_line('DEPRTMENT NO:' 
                           ||v_deptno 
                           || 'IS NOT PRESENT IN THE EMPTABLE'); 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('DEPRTMENT NO:' 
                           ||v_deptno 
                           || 'IS RETRIVING MORE THAN ONE ROW'); 
END; 
/

Enter value for department_no: 10

DEPRTMENT NO:10IS RETRIVING MORE THAN ONE ROW                                                                                     

PL/SQL procedure successfully completed.

DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('DEPRTMENT NO:' 
                           ||v_deptno 
                           || 'IS RETRIVING MORE THAN ONE ROW'); 
END; 
/

Enter value for department_no: 1
DECLARE
*
ERROR at line 1:
ORA-01403: no data found 
ORA-06512: at line 8 

DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN no_data_found THEN 
      dbms_output.Put_line('DEPRTMENT NO:' 
                           ||v_deptno 
                           || 'IS NOT PRESENT IN THE EMPTABLE'); 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('DEPRTMENT NO:' 
                           ||v_deptno 
                           || 'IS RETRIVING MORE THAN ONE ROW'); 
END; 
/ 

Enter value for department_no: 1
DEPRTMENT NO:1 IS NOT PRESENT IN THE EMPTABLE                                                                                      
PL/SQL procedure successfully completed.

CREATE TABLE DEPARTMENT
AS
SELECT * FROM DEPT;

Table created.

SQL> SELECT * FROM DEPARTMENT;

    DEPTNO 	DNAME          		LOC                                                                                                     
---------- -------------- -------------                                                                                           
        10 	ACCOUNTING     	NEW YORK                                                                                                
        20 	RESEARCH       	DALLAS                                                                                                  
        30 	SALES          		CHICAGO                                                                                                 
        40 	OPERATIONS     	BOSTON                                                                                                  

SQL> ALTER TABLE DEPARTMENT
     ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);

Table altered.

DECLARE 
    v_deptno NUMBER(10); 
    v_dname  VARCHAR2(10); 
    v_loc    VARCHAR2(10); 
BEGIN 
    v_deptno := &department_no; 

    v_dname := '&DEPARTMENT_NAME'; 

    v_loc := '&LOC'; 

    INSERT INTO department 
    VALUES      (v_deptno, 
                 v_dname, 
                 v_loc); 
END; 
/ 
Enter value for department_no: 10
Enter value for department_name: COMPUTERS
Enter value for loc: MIAMI
   DECLARE
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPT_PK) violated 
ORA-06512: at line 9 

DECLARE 
    v_deptno NUMBER(10); 
    v_dname  VARCHAR2(10); 
    v_loc    VARCHAR2(10); 
BEGIN 
    v_deptno := &department_no; 

    v_dname := '&DEPARTMENT_NAME'; 

    v_loc := '&LOC'; 

    INSERT INTO department 
    VALUES      (v_deptno, 
                 v_dname, 
                 v_loc); 
EXCEPTION 
    WHEN dup_val_on_index THEN 
      dbms_output.Put_line('DEPARTMENT NO:' 
                           ||'  ' 
                           ||v_deptno 
                           || 'IS ALREADY  ENTERED IN THE DEPARTMENT TABLE'); 
END; 
/

SQL> /
Enter value for department_no: 10

Enter value for department_name: COMPUTERS

Enter value for loc: MIAMI

DEPARTMENT NO:  10 IS ALREADY ENTERED IN THE DEPARTMENT TABLE                                                                      

PL/SQL procedure successfully completed.

DECLARE 
    v_deptno NUMBER(10); 
    v_dname  VARCHAR2(10); 
    v_loc    VARCHAR2(10); 
BEGIN 
    v_deptno := &department_no; 
    v_dname := '&DEPARTMENT_NAME'; 
    v_loc := '&LOC'; 
    INSERT INTO department 
    VALUES      (v_deptno, 
                 v_dname, 
                 v_loc); 
    COMMIT;
EXCEPTION 
    WHEN dup_val_on_index THEN 
      dbms_output.Put_line('DEPARTMENT NO:' 
                           ||'  ' 
                           ||v_deptno 
                           || 'IS ALREADY ENTERED IN THE DEPARTMENT TABLE'); 
END; 
/

Enter value for department_no: 10
Enter value for department_name: COMPUTERS & TECHNOLOGY
Enter value for loc: MIAMI
   DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at line 7 

DECLARE 
    v_deptno NUMBER(10); 
    v_dname  VARCHAR2(10); 
    v_loc    VARCHAR2(10); 
BEGIN 
    v_deptno := &department_no; 
    v_dname := '&DEPARTMENT_NAME'; 
    v_loc := '&LOC'; 
    INSERT INTO department 
    VALUES      (v_deptno, 
                 v_dname, 
                 v_loc); 
EXCEPTION 
    WHEN dup_val_on_index THEN 
      dbms_output.Put_line('DEPARTMENT NO:' 
                           ||'  ' 
                           ||v_deptno 
                           || 'IS ALREADY  ENTERED IN THE DEPARTMENT TABLE'); 
    WHEN value_error THEN 
      dbms_output.Put_line('DATA TYPE OR DATA SIZE INVALID'); 
END; 
/ 

Enter value for department_no: 10

Enter value for department_name: COMPUTERS & TECHNOLOGY

Enter value for loc: MIAMI

DATA TYPE OR DATA SIZE INVALID                                                                                                    

PL/SQL procedure successfully completed.

RAISE statement:

  
SQL>    CREATE TABLE BANK_TRANS
  2     (ACC_NO   NUMBER(10),
  3     CURRBAL   NUMBER(10))
  4  /

Table created.

SQL>  INSERT INTO BANK_TRANS
  2    VALUES
  3   (1,1000)
  4  /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1   INSERT INTO BANK_TRANS
  2    VALUES
  3*  (2,2000)
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1   INSERT INTO BANK_TRANS
  2    VALUES
  3*  (3,3000)
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

DECLARE 
    v_accno       NUMBER(10); 
    v_currbal     NUMBER(10); 
    v_withdrawamt NUMBER(10); 
    more_than_bal EXCEPTION; 
BEGIN 
    v_accno := &account_no; 

    SELECT acc_no, 
           currbal 
    INTO   v_accno, v_currbal 
    FROM   bank_trans 
    WHERE  acc_no = v_accno; 

    v_withdrawamt := &withdrawamt; 

    IF v_withdrawamt > v_currbal THEN 
      RAISE more_than_bal; 
    ELSE 
      dbms_output.Put_line('YOU SUCCESSFULLY WITHDRAWN AMOUNT'); 
    END IF; 
EXCEPTION 
    WHEN more_than_bal THEN 
      dbms_output.Put_line('ATTEMPTED TO WITH  DRAW MORE THAN THE CURRENT  BALANCE' 
                           || ' ' 
                           || v_currbal 
                           ||' ' 
                           || ' FROM THE ACCOUNT NUMBER' 
                           || '    ' 
                           || v_accno); 
END; 
/
Enter value for account_no: 1

Enter value for withdrawamt: 500

YOU SUCCESSFULLY WITHDRAWN AMOUNT                                                                                                 

PL/SQL procedure successfully completed.

SQL> /

Enter value for account_no: 2

Enter value for withdrawamt: 3000

ATTEMPTED TO WITH DRAW MORE THAN THE CURRENT

BALANCE 2000  FROM THE ACCOUNT NUMBER   2                                          

PL/SQL procedure successfully completed.

PRAGMA EXCEPTION_INIT:

  
DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
END; 
/ 

Enter value for department_no: 10

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 8 

DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
    e_toooomanyrows EXCEPTION; 
    PRAGMA EXCEPTION_INIT(e_toooomanyrows, -1422); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN e_toooomanyrows THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||'  ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
END; 
/

Enter value for department_no: 10

THE DEPTNO  10 FETCHING MORE THAN ONE ROW                                                                                  

PL/SQL procedure successfully completed.

DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
    e_toooomanyrows EXCEPTION; 
    PRAGMA EXCEPTION_INIT(e_toooomanyrows, -1421); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, 
           v_ename, 
           v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN e_toooomanyrows THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||'  ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
END; 
/
Enter value for department_no: 10

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 10 

DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('THE DEPTNO   ' 
                           ||v_deptno 
                           ||' ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
END; 
/
Enter value for department_no: 10

THE DEPTNO  10 FETCHING MORE THAN ONE ROW                                                                                         

PL/SQL procedure successfully completed.

WHEN OTHERS:

  
DECLARE 
    v_empno  NUMBER(10); 
    v_ename  VARCHAR2(10); 
    v_sal    NUMBER(10); 
    v_deptno NUMBER(10); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, 
           v_ename, 
           v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||'  ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
    WHEN OTHERS THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||' ' 
                           || 'IS NOT PRESENT IN THE TABLE'); 
END; 
/

Enter value for department_no: 10

THE DEPTNO  10 FETCHING MORE THAN ONE ROW                                                                                         

PL/SQL procedure successfully completed.

SQL> /

Enter value for department_no: 1

THE DEPTNO  1 IS NOT PRESENT IN THE TABLE                                                                                         

PL/SQL procedure successfully completed.

SQL CODE & SQLERRM:

  
DECLARE 
    v_empno     NUMBER(10); 
    v_ename     VARCHAR2(10); 
    v_sal       NUMBER(10); 
    v_deptno    NUMBER(10); 
    v_errorcode NUMBER(10); 
    v_errortext VARCHAR2(255); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, 
           v_ename, 
           v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN too_many_rows 
    THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||' ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
    WHEN OTHERS 
    THEN 
      v_errorcode := SQLCODE; 
      v_errortext := SQLERRM; 
      dbms_output.Put_line(v_errorcode 
                           || v_errortext); 
END; 
/ 

Enter value for department_no: 10

THE DEPTNO  10 FETCHING MORE THAN ONE ROW                                                                                         

PL/SQL procedure successfully completed.

DECLARE 
    v_empno     NUMBER(10); 
    v_ename     VARCHAR2(10); 
    v_sal       NUMBER(10); 
    v_deptno    NUMBER(10); 
    v_errorcode NUMBER(10); 
    v_errortext VARCHAR2(255); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||' ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
    WHEN OTHERS THEN 
      v_errorcode := SQLCODE; 

      v_errortext := SQLERRM; 

      dbms_output.Put_line(v_errorcode 
                           || v_errortext); 
END; 
/
Enter value for department_no: 1

100ORA-01403: no data found                                                                                                       

PL/SQL procedure successfully completed.

DECLARE 
    v_empno     NUMBER(10); 
    v_ename     VARCHAR2(10); 
    v_sal       NUMBER(10); 
    v_deptno    NUMBER(10); 
    v_errorcode NUMBER(10); 
    v_errortext VARCHAR2(255); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, 
           v_ename, 
           v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN too_many_rows THEN 
      dbms_output.Put_line('THE DEPTNO  ' 
                           ||v_deptno 
                           ||'  ' 
                           || 'FETCHING MORE THAN ONE ROW'); 
    WHEN OTHERS THEN 
      v_errorcode := SQLCODE; 
      v_errortext := SQLERRM; 
      dbms_output.Put_line(v_errorcode); 
      dbms_output.Put_line(v_errortext); 
END; 
/
Enter value for department_no: 1
100                                                                                                                               
ORA-01403: no data found                                                                                                          

PL/SQL procedure successfully completed.

DECLARE 
    v_empno     NUMBER(10); 
    v_ename     VARCHAR2(10); 
    v_sal       NUMBER(10); 
    v_deptno    NUMBER(10); 
    v_errorcode NUMBER(10); 
    v_errortext VARCHAR2(255); 
BEGIN 
    v_deptno := &department_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, v_ename, v_sal 
    FROM   emp 
    WHERE  deptno = v_deptno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN OTHERS THEN 
      v_errorcode := SQLCODE; 

      v_errortext := SQLERRM; 

      dbms_output.Put_line(v_errorcode); 

      dbms_output.Put_line(v_errortext); 
END; 
/
Enter value for department_no: 10

-1422                                                                                                                             

ORA-01422: exact fetch returns more than requested number of rows                                                                 

PL/SQL procedure successfully completed.

DECLARE 
    v_empno     NUMBER(10); 
    v_ename     NUMBER(10); 
    v_sal       NUMBER(10); 
    v_deptno    NUMBER(10); 
    v_errorcode NUMBER(10); 
    v_errortext VARCHAR2(255); 
BEGIN 
    v_empno := &employee_no; 

    SELECT empno, 
           ename, 
           sal 
    INTO   v_empno, 
           v_ename, 
           v_sal 
    FROM   emp 
    WHERE  empno = v_empno; 

    dbms_output.Put_line(v_empno 
                         ||v_ename 
                         ||v_sal); 
EXCEPTION 
    WHEN OTHERS THEN 
      v_errorcode := SQLCODE; 
      v_errortext := SQLERRM; 
      dbms_output.Put_line(v_errorcode); 
      dbms_output.Put_line(v_errortext); 
END; 
/

Enter value for employee_no: 7369

-6502                                                                                                                             

ORA-06502: PL/SQL: numeric or value error: character to number conversion error                                                   

PL/SQL procedure successfully completed.

RAISE:

  
DECLARE 
    v_accno       NUMBER(10); 
    v_currbal     NUMBER(10); 
    v_withdrawamt NUMBER(10); 
    v_errorcode   NUMBER(10); 
    v_errortext   VARCHAR2(255); 
    more_than_bal EXCEPTION; 
BEGIN 
    v_accno := &account_no; 

    SELECT acc_no, 
           currbal 
    INTO   v_accno, v_currbal 
    FROM   bank_trans 
    WHERE  acc_no = v_accno; 

    v_withdrawamt := &withdrawamt; 

    IF v_withdrawamt > v_currbal THEN 
      RAISE more_than_bal; 
    ELSE 
      dbms_output.Put_line('YOU SUCCESSFULLY WITHDRAWN AMOUNT'); 
    END IF; 
EXCEPTION 
    WHEN OTHERS THEN 
      v_errorcode := SQLCODE; 
      v_errortext := SQLERRM; 
      dbms_output.Put_line(v_errorcode); 
      dbms_output.Put_line(v_errortext); 
END; 
/
Enter value for account_no: 1

Enter value for withdrawamt: 2000
1                                                                                                                                 
User-Defined Exception                                                                                                            

PL/SQL procedure successfully completed.

RAISE_APPLICATION_ERROR:

By using the built- in function RAISE_APPLICATION_ERROR we can create our own error messages, which are more descriptive than named exceptions.

RAISE_APPLICATION_ERROR(ERROR_NUMBER, ERROR_MESSAGE).

Where error_number is a value BETWEEN 20,000 AND 20,999. ERROR MESSAGE is the text associated with this error.

  
DECLARE 
    v_sal   NUMBER(10); 
    v_empno NUMBER(10); 
BEGIN 
    v_empno := &employee_no; 

    SELECT sal 
    INTO   v_sal 
    FROM   emp 
    WHERE  empno = v_empno; 

    IF Length(v_empno) > 4 THEN 
      Raise_application_error(-20000, 'YOU CANNOT ENTER EMPNO  ITH MORE THAN 4 NUMBERS'); 
    END IF; 

    dbms_output.Put_line(v_sal); 
EXCEPTION 
    WHEN no_data_found THEN 
      Raise_application_error(-20001, 'THERE IS NO DATA FOR THE  EMPNO YOU ENTERED'); 
END; 
/
Enter value for employee_no: 7369

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON

SQL> /

Enter value for employee_no: 7369
800                                                                                                                               

PL/SQL procedure successfully completed.

Enter value for employee_no: 99999

DECLARE
*
ERROR at line 1:
ORA-20001: THERE IS NO DATA FOR THE EMPNO YOU ENTERED 
ORA-06512: at line 14 

DECLARE 
    v_sal   NUMBER(10); 
    v_empno NUMBER(10); 
BEGIN 
    v_empno := &employee_no; 

    IF Length(v_empno) > 4 THEN 
      Raise_application_error(-20000, 'YOU CANNOT ENTER EMPNO WITH MORE THAN 4  NUMBERS'); 
    END IF; 

    SELECT sal 
    INTO   v_sal 
    FROM   emp 
    WHERE  empno = v_empno; 

    dbms_output.Put_line(v_sal); 
EXCEPTION 
    WHEN no_data_found THEN 
      Raise_application_error(-20001, 'THERE IS NO DATA FOR THE EMPNO YOU      ENTERED'); 
END; 
/

Enter value for employee_no: 7369

800                                                                                                                               

PL/SQL procedure successfully completed.

Enter value for employee_no: 9999

DECLARE
*
ERROR at line 1:
ORA-20001: THERE IS NO DATA FOR THE EMPNO YOU ENTERED 
ORA-06512: at line 14 

Enter value for employee_no: 99999

DECLARE
*
ERROR at line 1:
ORA-20000: YOU CANNOT ENTER EMPNO WITH MORE THAN 4 NUMBERS 
ORA-06512: at line 7 

Nested Exceptions:

 
DECLARE
  vTemp     NUMBER := 0;
  vConvRate NUMBER := 0;
  
BEGIN
  DBMS_OUTPUT.Put_line('Corporate1');
  SELECT conversion_rate
    INTO vConvRate
    FROM gl_daily_rates
   WHERE conversion_date = '01-APR-12'
     AND from_currency   = 'USD'
     AND to_currency     = 'INR'
     AND conversion_type = 'Corporate1';
   DBMS_OUTPUT.Put_line('Conversion Rate: '|| vConvRate);
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
    BEGIN
      DBMS_OUTPUT.Put_line('PeriodEnd');
      SELECT conversion_rate
        INTO vConvRate
        FROM gl_daily_rates
       WHERE conversion_date = '01-APR-12'
         AND from_currency   = 'USD'
         AND to_currency     = 'INR'
         AND conversion_type = 'PeriodEnd';
      DBMS_OUTPUT.Put_line('Conversion Rate1: '|| vConvRate);
    EXCEPTION
       WHEN NO_DATA_FOUND
        THEN
          BEGIN
            DBMS_OUTPUT.Put_line('Corporate2');
            SELECT conversion_rate
              INTO vConvRate
              FROM gl_daily_rates
            WHERE conversion_date = '01-APR-12'
              AND from_currency   = 'USD'
              AND to_currency     = 'INR'
              AND conversion_type = 'Corporate2';
            DBMS_OUTPUT.Put_line('Conversion Rate2: '|| vConvRate);
          EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
              BEGIN
                DBMS_OUTPUT.Put_line('NULL');
                SELECT conversion_rate
                  INTO vConvRate
                  FROM gl_daily_rates
                WHERE conversion_date = '01-APR-12'
                  AND from_currency   = 'USD'
                  AND to_currency     = 'INR';
                DBMS_OUTPUT.Put_line('Conversion Rate3: '|| vConvRate);
              EXCEPTION
                WHEN OTHERS
                THEN
                  DBMS_OUTPUT.Put_line('Error 3: '||SQLERRM);
              END;
            WHEN OTHERS
            THEN
              DBMS_OUTPUT.Put_line('Error 2: '||SQLERRM);
          END;
          
      WHEN OTHERS
      THEN
        DBMS_OUTPUT.Put_line('Error 1: '||SQLERRM);
    END;  
    
  WHEN OTHERS 
  THEN
     DBMS_OUTPUT.Put_line('Error: '||SQLERRM);
END;

Another Example for Named Exception:

DECLARE
vDeptNo  NUMBER := 0;
vCounter NUMBER := 0;
vException EXCEPTION;
CURSOR cEmp 
    IS SELECT *
         FROM emp;
BEGIN
   FOR rcEmp IN cEmp
   LOOP 
      vCounter := vCounter + 1;
      BEGIN
         SELECT deptno
           INTO vDeptNo
           FROM scott.dept
          WHERE deptno = rcEmp.deptno;
          RAISE vException;
      EXCEPTION
         WHEN vException
         THEN
            DBMS_OUTPUT.Put_line('Error Custom Exception: '||vCounter); 
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.Put_line('Error so roll Backing at EmpNo: '||rcEmp.empno||' DeptNo: '||rcEmp.deptno);
            ROLLBACK;
      END;
      
      INSERT INTO xxxx_dummy VALUES (vCounter||' '||rcEmp.empno);      
      
   END LOOP;
   COMMIT;
END;
/ 

Go Back

Go Back