SQL> --exception handling SQL> declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&a_department_id; 7 dbms_output.put_line(name) 8 end; 9 / Enter value for a_department_id: 10 old 6: where department_id=&a_department_id; new 6: where department_id=10; end; * ERROR at line 8: ORA-06550: line 8, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "END" to continue. SQL> ed Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&a_department_id; 7 dbms_output.put_line(name); 8* end; 9 / Enter value for a_department_id: 10 old 6: where department_id=&a_department_id; new 6: where department_id=10; PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Enter value for a_department_id: 10 old 6: where department_id=&a_department_id; new 6: where department_id=10; Jennifer PL/SQL procedure successfully completed. SQL> / Enter value for a_department_id: 30 old 6: where department_id=&a_department_id; 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> --we got an error message because thgere are more than 1 emploees ıon department 30 SQL> --whenever an eeeror condıtıon occurs, oracle RAISES AN EXCEPTION SQL> --EVERY EXCEPTION THAT ORACLE CAN DETECT HAS A UNIQUE ID OR error number SQL> --EVERY EXCEPTION THAT ORACLE CAN DETECT HAS A MESSAGE SQL> --IN THE ABOVE EXAMPLE THE EXCEPTION NUMBER OR ERROR NUMBER IS ORA-01422 SQL> --THE ERROR MESSAGE IS ORA-01422: exact fetch returns more than requested number of rows SQL> SQL> --SOME EXCEPTIONS ALSO HAVE A NAME SQL> --IN THE ABOVE EXAMPLE THE NAME OF THE EXCEPTIOIN IS TOO_MANY_ROWS SQL> SQL> --IN PL/SQL IN ORDER TO HANDLE EXCEPTIONS, WE MUST USE EXCEPTION NAME SQL> SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&a_department_id; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); 11* end; SQL> / Enter value for a_department_id: 10 old 6: where department_id=&a_department_id; new 6: where department_id=10; dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); * ERROR at line 10: ORA-06550: line 10, column 24: PLS-00114: identifier 'MORE THAN ONE EMPLOYEE IN THIS' too long SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&a_department_id; 7 dbms_output.put_line(name); 8 --EXCEPTION 9 --WHEN TOO_MANY_ROWS THEN 10 --dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); 11* end; SQL> / Enter value for a_department_id: 10 old 6: where department_id=&a_department_id; new 6: where department_id=10; Jennifer PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&a_department_id; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); 11* end; SQL> / Enter value for a_department_id: 10 old 6: where department_id=&a_department_id; new 6: where department_id=10; dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); * ERROR at line 10: ORA-06550: line 10, column 23: PLS-00114: identifier 'MORE THAN ONE EMPLOYEE IN THIS' too long SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=10; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); 11* end; SQL> / dbms_output.put_line("MORE THAN ONE EMPLOYEE IN THIS DEPARTMENT"); * ERROR at line 10: ORA-06550: line 10, column 23: PLS-00114: identifier 'MORE THAN ONE EMPLOYEE IN THIS' too long SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=10; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line(SQLERRM); 11* end; SQL> / Jennifer PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=10; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line("SQLERRM"); 11* end; SQL> / Jennifer PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=10; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11* end; SQL> / Jennifer PL/SQL procedure successfully completed. SQL> / Jennifer PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11* end; SQL> / Enter value for anyno: 10 old 6: where department_id=&ANYNO; new 6: where department_id=10; Jennifer PL/SQL procedure successfully completed. SQL> / Enter value for anyno: 30 old 6: where department_id=&ANYNO; new 6: where department_id=30; MORE THAN ONE EMPLOYEES IN DEPT PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(50); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11* end; 12 / Enter value for anyno: 123 old 6: where department_id=&ANYNO; new 6: where department_id=123; declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 SQL> --WE GOT ORA-01403 EXCEPTİON SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(10); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11 WHEN NO_DATA_FOUND THEN 12 dbms_output.put_line('NO ONE'); 13* end; SQL> / Enter value for anyno: 123 old 6: where department_id=&ANYNO; new 6: where department_id=123; NO ONE PL/SQL procedure successfully completed. SQL> / Enter value for anyno: 30 old 6: where department_id=&ANYNO; new 6: where department_id=30; MORE THAN ONE EMPLOYEES IN DEPT PL/SQL procedure successfully completed. SQL> / Enter value for anyno: 10 old 6: where department_id=&ANYNO; new 6: where department_id=10; Jennifer PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(5); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11 WHEN NO_DATA_FOUND THEN 12 dbms_output.put_line('NO ONE'); 13* end; SQL> / Enter value for anyno: 10 old 6: where department_id=&ANYNO; 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 name varchar2(5); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11 WHEN NO_DATA_FOUND THEN 12 dbms_output.put_line('NO ONE'); 13 WHEN OTHERS THEN 14 dbms_output.put_line('SOMETHING IS WRONG'); 15* end; SQL> / Enter value for anyno: 10 old 6: where department_id=&ANYNO; new 6: where department_id=10; SOMETHING IS WRONG PL/SQL procedure successfully completed. SQL> / Enter value for anyno: 20 old 6: where department_id=&ANYNO; new 6: where department_id=20; MORE THAN ONE EMPLOYEES IN DEPT PL/SQL procedure successfully completed. SQL> / Enter value for anyno: 123 old 6: where department_id=&ANYNO; new 6: where department_id=123; NO ONE PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(5); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN OTHERS THEN 10 dbms_output.put_line('SOMETHING IS WRONG'); 11 WHEN TOO_MANY_ROWS THEN 12 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 13 WHEN NO_DATA_FOUND THEN 14 dbms_output.put_line('NO ONE'); 15* end; 16 / Enter value for anyno: 10 old 6: where department_id=&ANYNO; new 6: where department_id=10; WHEN OTHERS THEN * ERROR at line 9: ORA-06550: line 9, column 1: PLS-00370: OTHERS handler must be last among the exception handlers of a block ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated SQL> ED Wrote file afiedt.buf 1 declare 2 name varchar2(5); 3 begin 4 select first_name into name 5 from employees 6 where department_id=&ANYNO; 7 dbms_output.put_line(name); 8 EXCEPTION 9 WHEN TOO_MANY_ROWS THEN 10 dbms_output.put_line('MORE THAN ONE EMPLOYEES IN DEPT'); 11 WHEN NO_DATA_FOUND THEN 12 dbms_output.put_line('NO ONE'); 13 dbms_output.put_line('IN THE GIVEN DEPT'); 14 WHEN OTHERS THEN 15 dbms_output.put_line('SOMETHING IS WRONG'); 16 dbms_output.put_line('SQL ERROR MESSAGE'||SQLERRM); 17 dbms_output.put_line('SQL ERROR NUMBER'||SQLCODE); 18* end; 19 / Enter value for anyno: 10 old 6: where department_id=&ANYNO; new 6: where department_id=10; SOMETHING IS WRONG SQL ERROR MESSAGEORA-06502: PL/SQL: numeric or value error: character string buffer too small SQL ERROR NUMBER-6502 PL/SQL procedure successfully completed. SQL> --CREATE A TABLE MYFRIENDS(ID (PK) NAME (NN) AND GENDER (CHECK)) SQL> CREATE TABLE MYFRIENDS( 2 ID NUMBER(2) CONSTRAINT MYFRIENDS_ID_PK PRIMARY KEY, 3 NAME VARCHAR2(10) NOT NULL, 4 GENDER CHAR(1) CONSTRAINT MYFRIENDS_GENDER_CK CHECK(GENDER IN ('F','M'))); Table created. SQL> DESC MYFRIENDS; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(2) NAME NOT NULL VARCHAR2(10) GENDER CHAR(1) SQL> --WRITE AN ANONYMOUS BLOCK THAT ACCEPTS VALUES FROM KEYBOARD DESCRIBING A FRIEND AND ENTERS THEM SQL> --ADDS A NEW FRIEND TO THE MYFRIENDS TABLE USING THESE VALUES SQL> --HANDLE ALL POSSIBLE EXCEPTIONAS SQL> SQL> --WHICH TABLE? MYFRIENDS SQL> SQL> --ANY SQL COMMAND? YES SQL> --WHICH ONE? INSERT SQL> SQL> INSERT INTO MYFRIENDS(ID,NAME,GENDER) 2 VALUES(&1,'&2','&3') 3 SQL> / Enter value for 1: 1 Enter value for 2: ALI Enter value for 3: M old 2: VALUES(&1,'&2','&3') new 2: VALUES(1,'ALI','M') 1 row created. SQL> SELECT * FROM MYFRIENDS; ID NAME G ---------- ---------- - 1 ALI M SQL> ED Wrote file afiedt.buf 1* SELECT * FROM MYFRIENDS SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_ID MYFRIENDS.ID%TYPE := &ID; 3 V_NAME MYFRIENDS.NAME%TYPE; 4 v_GENDER MYFRIENDS.GENDER%TYPE; 5 BEGIN 6 V_NAME := '&NAME'; 7 V_GENDER := '&GEBNDER'; 8 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 9 VALUES(V_ID,V_NAME,V_GENDER); 10* END; 11 / Enter value for id: 2 old 2: V_ID MYFRIENDS.ID%TYPE := &ID; new 2: V_ID MYFRIENDS.ID%TYPE := 2; Enter value for name: AYSE old 6: V_NAME := '&NAME'; new 6: V_NAME := 'AYSE'; Enter value for gebnder: F old 7: V_GENDER := '&GEBNDER'; new 7: V_GENDER := 'F'; PL/SQL procedure successfully completed. SQL> SELECT * FROM MYFRIENDS; ID NAME G ---------- ---------- - 1 ALI M 2 AYSE F SQL> DECLARE 2 V_ID MYFRIENDS.ID%TYPE := &ID; 3 V_NAME MYFRIENDS.NAME%TYPE; 4 v_GENDER MYFRIENDS.GENDER%TYPE; 5 BEGIN 6 V_NAME := '&NAME'; 7 V_GENDER := '&GEBNDER'; 8 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 9 VALUES(V_ID,V_NAME,V_GENDER); 10 END; 11 / Enter value for id: 1 old 2: V_ID MYFRIENDS.ID%TYPE := &ID; new 2: V_ID MYFRIENDS.ID%TYPE := 1; Enter value for name: VELI old 6: V_NAME := '&NAME'; new 6: V_NAME := 'VELI'; Enter value for gebnder: M old 7: V_GENDER := '&GEBNDER'; new 7: V_GENDER := 'M'; DECLARE * ERROR at line 1: ORA-00001: unique constraint (USER15.MYFRIENDS_ID_PK) violated ORA-06512: at line 8 SQL> / Enter value for id: 3 old 2: V_ID MYFRIENDS.ID%TYPE := &ID; new 2: V_ID MYFRIENDS.ID%TYPE := 3; Enter value for name: AYSA old 6: V_NAME := '&NAME'; new 6: V_NAME := 'AYSA'; Enter value for gebnder: K old 7: V_GENDER := '&GEBNDER'; new 7: V_GENDER := 'K'; DECLARE * ERROR at line 1: ORA-02290: check constraint (USER15.MYFRIENDS_GENDER_CK) violated ORA-06512: at line 8 SQL> --UNIQUE VIOLATION ORA-1 HAS A NAME DUP_VAL_ON_INDEX SQL> **CHECK VIOLATION IS UNNAMED SP2-0734: unknown command beginning "**CHECK VI..." - rest of line ignored. SQL> **WE MUST DECLARE A NAME FOR IT BEFORE WE CAN HANDLE THE EXCEPTION SP2-0734: unknown command beginning "**WE MUST ..." - rest of line ignored. SQL> ED Wrote file afiedt.buf 1 DECLARE 2 V_ID MYFRIENDS.ID%TYPE := &ID; 3 V_NAME MYFRIENDS.NAME%TYPE; 4 v_GENDER MYFRIENDS.GENDER%TYPE; 5 V_MAX_ID MYFRIENDS.ID%TYPE; 6 BEGIN 7 V_NAME := '&NAME'; 8 V_GENDER := '&GEBNDER'; 9 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 10 VALUES(V_ID,V_NAME,V_GENDER); 11 EXCEPTION 12 WHEN DUP_VAL_ON_INDEX THEN 13 DBMS_OUTPUT.PUT_LINE('USING THE MAXIMUMN VALUE TO GENERATE A NEW ID'); 14 SELECT MAX(ID) INTO V_MAX_ID FROM MYFRIENDS; 15 V_MAX_ID :=V_MAX_ID+1; 16 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 17 VALUES(V_MAX_ID,V_NAME,V_GENDER); 18* END; 19 / Enter value for id: 4 old 2: V_ID MYFRIENDS.ID%TYPE := &ID; new 2: V_ID MYFRIENDS.ID%TYPE := 4; Enter value for name: PERP old 7: V_NAME := '&NAME'; new 7: V_NAME := 'PERP'; Enter value for gebnder: F old 8: V_GENDER := '&GEBNDER'; new 8: V_GENDER := 'F'; PL/SQL procedure successfully completed. SQL> SELECT * FROM MYFRIENDS; ID NAME G ---------- ---------- - 1 ALI M 2 AYSE F 4 PERP F SQL> DECLARE 2 V_ID MYFRIENDS.ID%TYPE := &ID; 3 V_NAME MYFRIENDS.NAME%TYPE; 4 v_GENDER MYFRIENDS.GENDER%TYPE; 5 V_MAX_ID MYFRIENDS.ID%TYPE; 6 BEGIN 7 V_NAME := '&NAME'; 8 V_GENDER := '&GEBNDER'; 9 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 10 VALUES(V_ID,V_NAME,V_GENDER); 11 EXCEPTION 12 WHEN DUP_VAL_ON_INDEX THEN 13 DBMS_OUTPUT.PUT_LINE('USING THE MAXIMUMN VALUE TO GENERATE A NEW ID'); 14 SELECT MAX(ID) INTO V_MAX_ID FROM MYFRIENDS; 15 V_MAX_ID :=V_MAX_ID+1; 16 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 17 VALUES(V_MAX_ID,V_NAME,V_GENDER); 18 END; 19 / Enter value for id: 1 old 2: V_ID MYFRIENDS.ID%TYPE := &ID; new 2: V_ID MYFRIENDS.ID%TYPE := 1; Enter value for name: FATMA old 7: V_NAME := '&NAME'; new 7: V_NAME := 'FATMA'; Enter value for gebnder: F old 8: V_GENDER := '&GEBNDER'; new 8: V_GENDER := 'F'; USING THE MAXIMUMN VALUE TO GENERATE A NEW ID PL/SQL procedure successfully completed. SQL> SELECT * FROM MYFRIENDS; ID NAME G ---------- ---------- - 1 ALI M 2 AYSE F 4 PERP F 5 FATMA F SQL> ED Wrote file afiedt.buf 1* SELECT * FROM MYFRIENDS SQL> ED Wrote file afiedt.buf 1 DECLARE 2 XX EXCEPTION; 3 PRAGMA EXCEPTION_INIT(XX,-2290); 4 V_ID MYFRIENDS.ID%TYPE := &ID; 5 V_NAME MYFRIENDS.NAME%TYPE; 6 v_GENDER MYFRIENDS.GENDER%TYPE; 7 V_MAX_ID MYFRIENDS.ID%TYPE; 8 BEGIN 9 V_NAME := '&NAME'; 10 V_GENDER := '&GEBNDER'; 11 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 12 VALUES(V_ID,V_NAME,V_GENDER); 13 EXCEPTION 14 WHEN DUP_VAL_ON_INDEX THEN 15 DBMS_OUTPUT.PUT_LINE('USING THE MAXIMUMN VALUE TO GENERATE A NEW ID'); 16 SELECT MAX(ID) INTO V_MAX_ID FROM MYFRIENDS; 17 V_MAX_ID :=V_MAX_ID+1; 18 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 19 VALUES(V_MAX_ID,V_NAME,V_GENDER); 20 WHEN XX THEN 21 DBMS_OUTPUT.PUT_LINE('USING THE NULL VALUE FOR GENDER'); 22 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 23 VALUES(V_MAX_ID,V_NAME,NULL); 24* END; 25 / Enter value for id: 3 old 4: V_ID MYFRIENDS.ID%TYPE := &ID; new 4: V_ID MYFRIENDS.ID%TYPE := 3; Enter value for name: AAA old 9: V_NAME := '&NAME'; new 9: V_NAME := 'AAA'; Enter value for gebnder: M old 10: V_GENDER := '&GEBNDER'; new 10: V_GENDER := 'M'; PL/SQL procedure successfully completed. SQL> / Enter value for id: 1 old 4: V_ID MYFRIENDS.ID%TYPE := &ID; new 4: V_ID MYFRIENDS.ID%TYPE := 1; Enter value for name: ASAAA old 9: V_NAME := '&NAME'; new 9: V_NAME := 'ASAAA'; Enter value for gebnder: M old 10: V_GENDER := '&GEBNDER'; new 10: V_GENDER := 'M'; USING THE MAXIMUMN VALUE TO GENERATE A NEW ID PL/SQL procedure successfully completed. SQL> SELECT * FROM MYFRIENDS; ID NAME G ---------- ---------- - 1 ALI M 2 AYSE F 4 PERP F 5 FATMA F 3 AAA M 6 ASAAA M 6 rows selected. SQL> DECLARE 2 XX EXCEPTION; 3 PRAGMA EXCEPTION_INIT(XX,-2290); 4 V_ID MYFRIENDS.ID%TYPE := &ID; 5 V_NAME MYFRIENDS.NAME%TYPE; 6 v_GENDER MYFRIENDS.GENDER%TYPE; 7 V_MAX_ID MYFRIENDS.ID%TYPE; 8 BEGIN 9 V_NAME := '&NAME'; 10 V_GENDER := '&GEBNDER'; 11 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 12 VALUES(V_ID,V_NAME,V_GENDER); 13 EXCEPTION 14 WHEN DUP_VAL_ON_INDEX THEN 15 DBMS_OUTPUT.PUT_LINE('USING THE MAXIMUMN VALUE TO GENERATE A NEW ID'); 16 SELECT MAX(ID) INTO V_MAX_ID FROM MYFRIENDS; 17 V_MAX_ID :=V_MAX_ID+1; 18 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 19 VALUES(V_MAX_ID,V_NAME,V_GENDER); 20 WHEN XX THEN 21 DBMS_OUTPUT.PUT_LINE('USING THE NULL VALUE FOR GENDER'); 22 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 23 VALUES(V_MAX_ID,V_NAME,NULL); 24 END; 25 / Enter value for id: 7 old 4: V_ID MYFRIENDS.ID%TYPE := &ID; new 4: V_ID MYFRIENDS.ID%TYPE := 7; Enter value for name: JJJ old 9: V_NAME := '&NAME'; new 9: V_NAME := 'JJJ'; Enter value for gebnder: A old 10: V_GENDER := '&GEBNDER'; new 10: V_GENDER := 'A'; USING THE NULL VALUE FOR GENDER DECLARE * ERROR at line 1: ORA-01400: cannot insert NULL into ("USER15"."MYFRIENDS"."ID") ORA-06512: at line 22 ORA-02290: check constraint (USER15.MYFRIENDS_GENDER_CK) violated SQL> ED Wrote file afiedt.buf 1 DECLARE 2 XX EXCEPTION; 3 PRAGMA EXCEPTION_INIT(XX,-2290); 4 V_ID MYFRIENDS.ID%TYPE := &ID; 5 V_NAME MYFRIENDS.NAME%TYPE; 6 v_GENDER MYFRIENDS.GENDER%TYPE; 7 V_MAX_ID MYFRIENDS.ID%TYPE; 8 BEGIN 9 V_NAME := '&NAME'; 10 V_GENDER := '&GEBNDER'; 11 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 12 VALUES(V_ID,V_NAME,V_GENDER); 13 EXCEPTION 14 WHEN DUP_VAL_ON_INDEX THEN 15 DBMS_OUTPUT.PUT_LINE('USING THE MAXIMUMN VALUE TO GENERATE A NEW ID'); 16 SELECT MAX(ID) INTO V_MAX_ID FROM MYFRIENDS; 17 V_MAX_ID :=V_MAX_ID+1; 18 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 19 VALUES(V_MAX_ID,V_NAME,V_GENDER); 20 WHEN XX THEN 21 DBMS_OUTPUT.PUT_LINE('USING THE NULL VALUE FOR GENDER'); 22 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 23 VALUES(V_ID,V_NAME,NULL); 24* END; SQL> / Enter value for id: 7 old 4: V_ID MYFRIENDS.ID%TYPE := &ID; new 4: V_ID MYFRIENDS.ID%TYPE := 7; Enter value for name: AAA old 9: V_NAME := '&NAME'; new 9: V_NAME := 'AAA'; Enter value for gebnder: Q old 10: V_GENDER := '&GEBNDER'; new 10: V_GENDER := 'Q'; USING THE NULL VALUE FOR GENDER PL/SQL procedure successfully completed. SQL> SELECT * FROM MYFRIENDS; ID NAME G ---------- ---------- - 1 ALI M 2 AYSE F 4 PERP F 5 FATMA F 3 AAA M 6 ASAAA M 7 AAA 7 rows selected. SQL> DESC MYFRIENDS Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(2) NAME NOT NULL VARCHAR2(10) GENDER CHAR(1) SQL> ED Wrote file afiedt.buf 1* SELECT * FROM MYFRIENDS SQL> ED Wrote file afiedt.buf 1 DECLARE 2 XX EXCEPTION; 3 PRAGMA EXCEPTION_INIT(XX,-2290); 4 V_ID MYFRIENDS.ID%TYPE := &ID; 5 V_NAME MYFRIENDS.NAME%TYPE; 6 v_GENDER MYFRIENDS.GENDER%TYPE; 7 V_MAX_ID MYFRIENDS.ID%TYPE; 8 BEGIN 9 V_NAME := '&NAME'; 10 V_GENDER := '&GEBNDER'; 11 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 12 VALUES(V_ID,V_NAME,V_GENDER); 13 EXCEPTION 14 WHEN DUP_VAL_ON_INDEX THEN 15 DBMS_OUTPUT.PUT_LINE('USING THE MAXIMUMN VALUE TO GENERATE A NEW ID'); 16 SELECT MAX(ID) INTO V_MAX_ID FROM MYFRIENDS; 17 V_MAX_ID :=V_MAX_ID+1; 18 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 19 VALUES(V_MAX_ID,V_NAME,V_GENDER); 20 WHEN XX THEN 21 DBMS_OUTPUT.PUT_LINE('USING THE NULL VALUE FOR GENDER'); 22 INSERT INTO MYFRIENDS(ID,NAME,GENDER) 23 VALUES(V_ID,V_NAME,NULL); 24 WHEN OTHERS THEN 25 DBMS_OUTPUT.PUT_LINE(SQLERRM); 26* END; SQL> / Enter value for id: 8 old 4: V_ID MYFRIENDS.ID%TYPE := &ID; new 4: V_ID MYFRIENDS.ID%TYPE := 8; Enter value for name: A1234567891234567 old 9: V_NAME := '&NAME'; new 9: V_NAME := 'A1234567891234567'; Enter value for gebnder: M old 10: V_GENDER := '&GEBNDER'; new 10: V_GENDER := 'M'; ORA-06502: PL/SQL: numeric or value error: character string buffer too small PL/SQL procedure successfully completed. SQL> SPOOL OFF