SQL> --EXCEPTION HANDLING SQL> DECLARE 2 V_NAME VARCHAR2(10); 3 BEGIN 4 . SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(10); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8* END; SQL> / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; Jennifer PL/SQL procedure successfully completed. SQL> / Enter value for anyid: 30 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 30; DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 SQL> --SELECT INTO CRASHED BECAUSE THERE ARE MNORE THAN 1 EMPLOYEES IN DEPT 30 SQL> **WHNEVER AN EEROR OCCURS IN THE DB, ORACLE DETECTS IT SP2-0734: unknown command beginning "**WHNEVER ..." - rest of line ignored. SQL> --ANY ERROR/EXCEPTION THAT ORACLE DETECTS HAS A UNIQUE ID (SQLCODE) SQL> --ANY ERROR/EXCEPTION THAT ORACLE DETECTS HAS AN ERROR MESSAGE SQL> SQL> **IN THE ABOVE EXAMPLE ERROR CODE IS ORA-01422 SP2-0734: unknown command beginning "**IN THE A..." - rest of line ignored. SQL> --IN THE ABOVE EXAMPLE ERROR MESSAGE IS ORA-01422: exact fetch returns more than requested number of rows SQL> SQL> --SOME OF THE EXCEPTIONS THAT ORACLE CAN DETECT HAVE NAMES!!!! SQL> --WE CALL THESE NAMED ORACLE EXCEPTIONS SQL> -- NAMED SYSTEM EXCEPTIONS SQL> -- PREDEFIONED ORACLE EXCEPTIONS SQL> -- PREDEFIONED SYSTEM EXCEPTIONS SQL> SQL> --ALL OTHER EXCEPTIONS THAT ARE DETECTED BY ORACLE AND HAS NO NAME ARE CALLED UNNAMED/NONPREDEFINED ORACLE/SYSTEM EXCEPTIONS SQL> SQL> SQL> --IN THE EXAMPLE GIVEN ABOVE THE EXCEPTION RAISED HAS ERROR NUMBER ORA-01422 WHICH IS A NAMED EXCEPTION SQL> --NAME OF THIS EXCEPTION IS TOO_MANY_ROWS SQL> / Enter value for anyid: 123 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 123; DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 SQL> --THE ERROR CODE IS ORA-01403 SQL> --ERROR MESSAGE IS ORA-01403: no data found SQL> --EXCEPTION NAME IS NO_DATA_FOUND SQL> SQL> / Enter value for anyid: old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= ; WHERE DEPARTMENT_ID= ; * ERROR at line 6: ORA-06550: line 6, column 24: PL/SQL: ORA-00936: missing expression ORA-06550: line 4, column 3: PL/SQL: SQL Statement ignored SQL> SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(10); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8* END; SQL> / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; Jennifer PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(2); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8* END; SQL> / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4 SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(2); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT'); 11 WHEN NO_DATA_FOUND THEN 12 DBMS_OUTPUT.PUT_LINE('NO ONE IN THE DEPARTMENT'); 13* END; 14 / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4 SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(25); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT'); 11 WHEN NO_DATA_FOUND THEN 12 DBMS_OUTPUT.PUT_LINE('NO ONE IN THE DEPARTMENT'); 13* END; SQL> / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; Jennifer PL/SQL procedure successfully completed. SQL> / Enter value for anyid: 30 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 30; MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT PL/SQL procedure successfully completed. SQL> / Enter value for anyid: 123 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 123; NO ONE IN THE DEPARTMENT PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(10); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8 V_NAME := 'A123456789123456789'; 9 EXCEPTION 10 WHEN TOO_MANY_ROWS THEN 11 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT'); 12 WHEN NO_DATA_FOUND THEN 13 DBMS_OUTPUT.PUT_LINE('NO ONE IN THE DEPARTMENT'); 14* END; 15 / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; Jennifer DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 8 SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(10); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8 V_NAME := 'A123456789123456789'; 9 EXCEPTION 10 WHEN TOO_MANY_ROWS THEN 11 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT'); 12 WHEN NO_DATA_FOUND THEN 13 DBMS_OUTPUT.PUT_LINE('NO ONE IN THE DEPARTMENT'); 14 WHEN OTHERS THEN 15 DBMS_OUTPUT.PUT_LINE('SOMETHING IS WRONG'); 16 DBMS_OUTPUT.PUT_LINE('EXCEPTION CODE IS '|| SQLCODE); 17 DBMS_OUTPUT.PUT_LINE('EXCEPTION ERROR MESSAGE IS '||SQLERRM); 18* END; SQL> / Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; Jennifer SOMETHING IS WRONG EXCEPTION CODE IS -6502 EXCEPTION ERROR MESSAGE IS ORA-06502: PL/SQL: numeric or value error: character string buffer too small PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_NAME VARCHAR2(10); 3 BEGIN 4 SELECT FIRST_NAME INTO V_NAME 5 FROM EMPLOYEES 6 WHERE DEPARTMENT_ID= &ANYID; 7 DBMS_OUTPUT.PUT_LINE(V_NAME); 8 V_NAME := 'A123456789123456789'; 9 EXCEPTION 10 WHEN TOO_MANY_ROWS THEN 11 DBMS_OUTPUT.PUT_LINE('MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT'); 12 WHEN NO_DATA_FOUND THEN 13 DBMS_OUTPUT.PUT_LINE('NO ONE IN THE DEPARTMENT'); 14 WHEN OTHERS THEN 15 DBMS_OUTPUT.PUT_LINE('SOMETHING IS WRONG'); 16 DBMS_OUTPUT.PUT_LINE('EXCEPTION CODE IS '|| SQLCODE); 17 DBMS_OUTPUT.PUT_LINE('EXCEPTION ERROR MESSAGE IS '||SQLERRM); 18* END; SQL> // Enter value for anyid: 10 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 10; Jennifer SOMETHING IS WRONG EXCEPTION CODE IS -6502 EXCEPTION ERROR MESSAGE IS ORA-06502: PL/SQL: numeric or value error: character string buffer too small PL/SQL procedure successfully completed. SQL> / Enter value for anyid: 30 old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 30; MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT PL/SQL procedure successfully completed. SQL> --IN THE AEXAMPLE ABOVE, WHEN SELECT IINTO IS USED WITH DEPARTMENT_ID=30, TOO_MANY_ROWS SQL> --EXCEPTION IS RAISED AT THE SELECT STATEMENT BEFORE LINE NUMBER 8 SQL> --THEREFORE WHEN EXCEPTION IS RAIUSED AND CONTROL ID PASSED TO THE EXCEPTION HANDLING SECTION SQL> --THE FIRST MATCH TOO_MANY_ROWS IS EXECUTED SQL> / Enter value for anyid: ASD old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= ASD; WHERE DEPARTMENT_ID= ASD; * ERROR at line 6: ORA-06550: line 6, column 24: PL/SQL: ORA-00904: "ASD": invalid identifier ORA-06550: line 4, column 3: PL/SQL: SQL Statement ignored SQL> --THIS IS A COMPILER ERROR SQL> / Enter value for anyid: 'SSS' old 6: WHERE DEPARTMENT_ID= &ANYID; new 6: WHERE DEPARTMENT_ID= 'SSS'; SOMETHING IS WRONG EXCEPTION CODE IS -1722 EXCEPTION ERROR MESSAGE IS ORA-01722: invalid number PL/SQL procedure successfully completed. SQL> --LETS CREATE A NEW TABLE FRIENDS SQL> --THE TABLE WILL HAVE ID :PK/NAME:NOT NULL/GENDER:CHECK SQL> CREATE TABLE FRIENDS( 2 ID NUMBER(2) CONSTRAINT FRIENDS_ID_PK PRIMARY KEY, 3 NAME VARCHAR2(10) NOT NULL, 4 GENDER CHAR(1) CONSTRAINT FRIENDS_GENDER_CK CHECK(GENDER IN ('M','F'))); Table created. SQL> --WRITE AN ANONYMOUS BLOCK TO ADD NEW FRIENDS TO THE TABLE. SQL> --READ ALL INFO FROM KEYBOARD SQL> --HANDLE ALL EXCEPTIONS SQL> SQL> --WHICH TABLE: FRIENDS SQL> SQL> --*WHICH SEL COMMand(if any) : insert SQL> ed Wrote file afiedt.buf 1 declare 2 v_id friends.id%type; 3 v_name friends.name%type; 4 v_gender friends.gender%type; 5 begin 6 v_id := &an_id; 7 v_name := '&a_name'; 8 v_gender :='&1'; 9 insert into friends(id,name,gender) 10 values(v_id,v_name,v_gender); 11* end; 12 / Enter value for an_id: 1 old 6: v_id := &an_id; new 6: v_id := 1; Enter value for a_name: alư old 7: v_name := '&a_name'; new 7: v_name := 'alư'; Enter value for 1: F old 8: v_gender :='&1'; new 8: v_gender :='F'; PL/SQL procedure successfully completed. SQL> / Enter value for an_id: 1 old 6: v_id := &an_id; new 6: v_id := 1; Enter value for a_name: VELI old 7: v_name := '&a_name'; new 7: v_name := 'VELI'; Enter value for 1: M old 8: v_gender :='&1'; new 8: v_gender :='M'; declare * ERROR at line 1: ORA-00001: unique constraint (USER15.FRIENDS_ID_PK) violated ORA-06512: at line 9 SQL> / Enter value for an_id: 2 old 6: v_id := &an_id; new 6: v_id := 2; Enter value for a_name: ALIYE old 7: v_name := '&a_name'; new 7: v_name := 'ALIYE'; Enter value for 1: R old 8: v_gender :='&1'; new 8: v_gender :='R'; declare * ERROR at line 1: ORA-02290: check constraint (USER15.FRIENDS_GENDER_CK) violated ORA-06512: at line 9 SQL> ED Wrote file afiedt.buf 1 declare 2 ASSETOU EXCEPTION; 3 PRAGMA EXCEPTION_INIT(ASSETOU,-2290); 4 v_id friends.id%type; 5 v_name friends.name%type; 6 v_gender friends.gender%type; 7 V_MAX_ID friends.id%type; 8 begin 9 v_id := &an_id; 10 v_name := '&a_name'; 11 v_gender :='&1'; 12 insert into friends(id,name,gender) 13 values(v_id,v_name,v_gender); 14 EXCEPTION 15 WHEN DUP_VAL_ON_INDEX THEN 16 DBMS_OUTPUT.PUT_LINE('USING MAX ID TO GENERATE A NEW ID'); 17 SELECT MAX(ID) INTO V_MAX_ID FROM FRIENDS; 18 V_MAX_ID:=V_MAX_ID+1; 19 insert into friends(id,name,gender) 20 values(v_MAX_id,v_name,v_gender); 21 WHEN ASSETOU THEN 22 DBMS_OUTPUT.PUT_LINE('USING NULL FOR GENDER'); 23 insert into friends(id,name,gender) 24 values(v_id,v_name,NULL); 25 WHEN OTHERS THEN 26 DBMS_OUTPUT.PUT_LINE('****'||SQLERRM); 27* end; 28 / Enter value for an_id: 1 old 9: v_id := &an_id; new 9: v_id := 1; Enter value for a_name: MOLHIM old 10: v_name := '&a_name'; new 10: v_name := 'MOLHIM'; Enter value for 1: M old 11: v_gender :='&1'; new 11: v_gender :='M'; USING MAX ID TO GENERATE A NEW ID PL/SQL procedure successfully completed. SQL> SELECT * FROM FRIENDS; ID NAME G ---------- ---------- - 1 alư F 2 MOLHIM M SQL> declare 2 ASSETOU EXCEPTION; 3 PRAGMA EXCEPTION_INIT(ASSETOU,-2290); 4 v_id friends.id%type; 5 v_name friends.name%type; 6 v_gender friends.gender%type; 7 V_MAX_ID friends.id%type; 8 begin 9 v_id := &an_id; 10 v_name := '&a_name'; 11 v_gender :='&1'; 12 insert into friends(id,name,gender) 13 values(v_id,v_name,v_gender); 14 EXCEPTION 15 WHEN DUP_VAL_ON_INDEX THEN 16 DBMS_OUTPUT.PUT_LINE('USING MAX ID TO GENERATE A NEW ID'); 17 SELECT MAX(ID) INTO V_MAX_ID FROM FRIENDS; 18 V_MAX_ID:=V_MAX_ID+1; 19 insert into friends(id,name,gender) 20 values(v_MAX_id,v_name,v_gender); 21 WHEN ASSETOU THEN 22 DBMS_OUTPUT.PUT_LINE('USING NULL FOR GENDER'); 23 insert into friends(id,name,gender) 24 values(v_id,v_name,NULL); 25 WHEN OTHERS THEN 26 DBMS_OUTPUT.PUT_LINE('****'||SQLERRM); 27 end; 28 / Enter value for an_id: 3 old 9: v_id := &an_id; new 9: v_id := 3; Enter value for a_name: AYSE old 10: v_name := '&a_name'; new 10: v_name := 'AYSE'; Enter value for 1: K old 11: v_gender :='&1'; new 11: v_gender :='K'; USING NULL FOR GENDER PL/SQL procedure successfully completed. SQL> SELECT * FROM FRIENDS; ID NAME G ---------- ---------- - 1 alư F 2 MOLHIM M 3 AYSE SQL> declare 2 ASSETOU EXCEPTION; 3 PRAGMA EXCEPTION_INIT(ASSETOU,-2290); 4 v_id friends.id%type; 5 v_name friends.name%type; 6 v_gender friends.gender%type; 7 V_MAX_ID friends.id%type; 8 begin 9 v_id := &an_id; 10 v_name := '&a_name'; 11 v_gender :='&1'; 12 insert into friends(id,name,gender) 13 values(v_id,v_name,v_gender); 14 EXCEPTION 15 WHEN DUP_VAL_ON_INDEX THEN 16 DBMS_OUTPUT.PUT_LINE('USING MAX ID TO GENERATE A NEW ID'); 17 SELECT MAX(ID) INTO V_MAX_ID FROM FRIENDS; 18 V_MAX_ID:=V_MAX_ID+1; 19 insert into friends(id,name,gender) 20 values(v_MAX_id,v_name,v_gender); 21 WHEN ASSETOU THEN 22 DBMS_OUTPUT.PUT_LINE('USING NULL FOR GENDER'); 23 insert into friends(id,name,gender) 24 values(v_id,v_name,NULL); 25 WHEN OTHERS THEN 26 DBMS_OUTPUT.PUT_LINE('****'||SQLERRM); 27 end; 28 / Enter value for an_id: 3 old 9: v_id := &an_id; new 9: v_id := 3; Enter value for a_name: TTT old 10: v_name := '&a_name'; new 10: v_name := 'TTT'; Enter value for 1: T old 11: v_gender :='&1'; new 11: v_gender :='T'; USING NULL FOR GENDER declare * ERROR at line 1: ORA-00001: unique constraint (USER15.FRIENDS_ID_PK) violated ORA-06512: at line 23 ORA-02290: check constraint (USER15.FRIENDS_GENDER_CK) violated SQL> SPOOL OFF