SQL> set serveroutput on SQL> drop myregions 2 ; drop myregions * ERROR at line 1: ORA-00950: invalid DROP option SQL> drop table myregions; Table dropped. SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> create table myregions as 2 select * from regions; Table created. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> desc myregions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> alter table myregions 2 add primary key(REGION_ID ); Table altered. SQL> insert into myregions 2 values(1,'AAAA'); insert into myregions * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0030469) violated SQL> --error number ýs ora-00001 SQL> error message ORA-00001: unique constraint (USER15.SYS_C0030469) violated SP2-0734: unknown command beginning "error mess..." - rest of line ignored. SQL> --this is a system exception SQL> --we checked oracle documentatýon and saw that tghis is a named exception SQL> -- DUP_VAL_ON_INDEX SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new region 2 --handle one relevant named exception 3 declare 4 begin 5 insert into myregions 6 values(1,'AAAA'); 7 exception 8 when dup_val_on_index then 9 dbms_output.put_line('you violated the pk! try again later!'); 10* end; 11 / you violated the pk! try again later! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new region 2 --handle all exceptions by printing the oracle error message 3 declare 4 begin 5 insert into myregions 6 values(1,'AAAA'); 7 exception 8 when others then 9 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 10 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 11* end; 12 / ###the oracle error message is =ORA-00001: unique constraint (USER15.SYS_C0030469) violated### ***the oracle error number is =-1*** PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new region 2 --handle one relevant named (predefined) system (oracle) exception 3 --handle all exceptions by printing the oracle error message 4 declare 5 begin 6 insert into myregions 7 values(1,'AAAA'); 8 exception 9 when dup_val_on_index then 10 dbms_output.put_line('pk is violated'); 11 when others then 12 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 13 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 14* end; 15 / pk is violated PL/SQL procedure successfully completed. SQL> desc myregions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> alter table myregions 2 add check(region_name not like '%NORTH%'); Table altered. SQL> insert into myregions 2 values(6,'AAAaaa') 3 . SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> insert into myregions 2 values(6,'AAAaaa'); 1 row created. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa SQL> insert into myregions 2 values(5,'AANORTHdjfhg'); insert into myregions * ERROR at line 1: ORA-02290: check constraint (USER15.SYS_C0030471) violated SQL> --oracle error number for check výolatýon ýs : -02290 SQL> --oracle error message for check violation is ORA-02290: check constraint (USER15.SYS_C0030471) violated SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new region 2 --handle one relevant named (predefined) system (oracle) exception 3 --handle all exceptions by printing the oracle error message 4 declare 5 begin 6 insert into myregions 7 values(1,'AAAA'); 8 exception 9 when dup_val_on_index then 10 dbms_output.put_line('pk is violated'); 11 when others then 12 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 13 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 14* end; SQL> . SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new region 2 -- read all values from keyboard 3 --handle one relevant named (predefined) system (oracle) exception 4 --handle all exceptions by printing the oracle error message 5 declare 6 begin 7 insert into myregions 8 values(®ion_id,'®ion_name'); 9 exception 10 when dup_val_on_index then 11 dbms_output.put_line('pk is violated'); 12 when others then 13 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 14 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 15* end; SQL> / Enter value for region_id: 7 Enter value for region_name: ygyg old 8: values(®ion_id,'®ion_name'); new 8: values(7,'ygyg'); PL/SQL procedure successfully completed. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 6 rows selected. SQL> --write an anonymous block to add a new region SQL> -- read all values from keyboard SQL> --handle one relevant named (predefined) system (oracle) exception SQL> --handle all exceptions by printing the oracle error message SQL> declare 2 begin 3 insert into myregions 4 values(®ion_id,'®ion_name'); 5 exception 6 when dup_val_on_index then 7 dbms_output.put_line('pk is violated'); 8 when others then 9 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 10 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 11 end; 12 / Enter value for region_id: 8 Enter value for region_name: NORTH DAKOTA old 4: values(®ion_id,'®ion_name'); new 4: values(8,'NORTH DAKOTA'); ###the oracle error message is =ORA-02290: check constraint (USER15.SYS_C0030471) violated### ***the oracle error number is =-2290*** PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 begin 3 insert into myregions 4 values(®ion_id,'®ion_name'); 5 exception 6 when dup_val_on_index then 7 dbms_output.put_line('pk is violated'); 8 when others then 9 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 10 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 11* end; SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new region 2 -- read all values from keyboard 3 --handle one relevant named (predefined) system (oracle) exception 4 --handle check constraint violation by setting the name to 'NO NAME' 5 --handle the unnamed exception with error number ORA-02290 6 --handle the unnamed exception with message ORA-02290: Check contraint .... 7 --handle all exceptions by printing the oracle error message 8 declare 9 yusuf exception; 10 pragma exception_init(yusuf,-2290); 11 begin 12 insert into myregions 13 values(®ion_id,'®ion_name'); 14 exception 15 when dup_val_on_index then 16 dbms_output.put_line('pk is violated'); 17 when yusuf then 18 dbms_output.put_line('region name cannot contain north. chaned it to NO NAME'); 19 when others then 20 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 21 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 22* end; SQL> / Enter value for region_id: 8 Enter value for region_name: ooooooo old 13: values(®ion_id,'®ion_name'); new 13: values(8,'ooooooo'); PL/SQL procedure successfully completed. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 8 ooooooo 7 rows selected. SQL> --write an anonymous block to add a new region SQL> -- read all values from keyboard SQL> --handle one relevant named (predefined) system (oracle) exception SQL> --handle check constraint violation by setting the name to 'NO NAME' SQL> --handle the unnamed exception with error number ORA-02290 SQL> --handle the unnamed exception with message ORA-02290: Check contraint .... SQL> --handle all exceptions by printing the oracle error message SQL> declare 2 yusuf exception; 3 pragma exception_init(yusuf,-2290); 4 begin 5 insert into myregions 6 values(®ion_id,'®ion_name'); 7 exception 8 when dup_val_on_index then 9 dbms_output.put_line('pk is violated'); 10 when yusuf then 11 dbms_output.put_line('region name cannot contain north. chaned it to NO NAME'); 12 when others then 13 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 14 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 15 end; 16 / Enter value for region_id: 9 Enter value for region_name: NORTH CYP old 6: values(®ion_id,'®ion_name'); new 6: values(9,'NORTH CYP'); region name cannot contain north. chaned it to NO NAME PL/SQL procedure successfully completed. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 8 ooooooo 7 rows selected. SQL> ed Wrote file afiedt.buf 1 -- read all values from keyboard 2 --handle one relevant named (predefined) system (oracle) exception 3 --handle check constraint violation by setting the name to 'NO NAME' 4 --handle the unnamed exception with error number ORA-02290 5 --handle the unnamed exception with message ORA-02290: Check contraint .... 6 --handle all exceptions by printing the oracle error message 7 declare 8 e_check exception; 9 pragma exception_init(e_check,-2290); 10 v_region_id myregions.region_id%type; 11 v_region_name myregions.region_name%type; 12 begin 13 v_region_id := &rid; 14 v_region_name := &rname; 15 insert into myregions 16 values(v_region_id,v_region_name); 17 exception 18 when dup_val_on_index then 19 dbms_output.put_line('pk is violated'); 20 when e_check then 21 dbms_output.put_line('Changed it to NO NAME'); 22 insert into myregions 23 values(v_region_id,'NO NAME'); 24 when others then 25 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 26 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 27* end; SQL> / Enter value for rid: 9 old 13: v_region_id := &rid; new 13: v_region_id := 9; Enter value for rname: 'AUSTRALIA' old 14: v_region_name := &rname; new 14: v_region_name := 'AUSTRALIA'; PL/SQL procedure successfully completed. SQL> desc myregions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> / Enter value for rid: 10 old 13: v_region_id := &rid; new 13: v_region_id := 10; Enter value for rname: 'ggsgsNORTH sfj' old 14: v_region_name := &rname; new 14: v_region_name := 'ggsgsNORTH sfj'; Changed it to NO NAME PL/SQL procedure successfully completed. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 8 ooooooo 9 AUSTRALIA 10 NO NAME 9 rows selected. SQL> ed Wrote file afiedt.buf 1 -- read all values from keyboard 2 --1-handle one relevant named (predefined) system (oracle) exception 3 --2-handle the unnamed exception with error number ORA-02290 check constraint violation by setting the name to 'NO NAME' 4 --3-handle the case where the region name is not entered in all 5 --upper case letters as a user defined exception by converting name to all upper case 6 --4-handle all exceptions by printing the oracle error message 7 declare 8 e_check exception; 9 pragma exception_init(e_check,-2290); 10 v_region_id myregions.region_id%type; 11 v_region_name myregions.region_name%type; 12 e_not_upper exception; 13 begin 14 v_region_id := &rid; 15 v_region_name := &rname; 16 if v_region_name != upper(v_region_name) then 17 raise e_not_upper; 18 end if; 19 insert into myregions 20 values(v_region_id,v_region_name); 21 exception 22 when dup_val_on_index then 23 dbms_output.put_line('pk is violated'); 24 when e_check then 25 dbms_output.put_line('Changed it to NO NAME'); 26 insert into myregions 27 values(v_region_id,'NO NAME'); 28 when e_not_upper then 29 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 30 insert into myregions 31 values(v_region_id,upper(v_region_name)); 32 when others then 33 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 34 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 35 end; 36* / SQL> del 36 SQL> / Enter value for rid: 11 old 14: v_region_id := &rid; new 14: v_region_id := 11; Enter value for rname: 'ay name' old 15: v_region_name := &rname; new 15: v_region_name := 'ay name'; USE CAPS LOCK YOU DUMMY! PL/SQL procedure successfully completed. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 8 ooooooo 9 AUSTRALIA 10 NO NAME 11 AY NAME 10 rows selected. SQL> -- read all values from keyboard SQL> --1-handle one relevant named (predefined) system (oracle) exception SQL> --2-handle the unnamed exception with error number ORA-02290 check constraint violation by se SQL> --3-handle the case where the region name is not entered in all SQL> --upper case letters as a user defined exception by converting name to all upper case SQL> --4-handle all exceptions by printing the oracle error message SQL> declare 2 e_check exception; 3 pragma exception_init(e_check,-2290); 4 v_region_id myregions.region_id%type; 5 v_region_name myregions.region_name%type; 6 e_not_upper exception; 7 egin 8 v_region_id := &rid; 9 v_region_name := &rname; 10 if v_region_name != upper(v_region_name) then 11 raise e_not_upper; 12 end if; 13 insert into myregions 14 values(v_region_id,v_region_name); 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk is violated'); 18 when e_check then 19 dbms_output.put_line('Changed it to NO NAME'); 20 insert into myregions 21 values(v_region_id,'NO NAME'); 22 when e_not_upper then 23 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 24 insert into myregions 25 values(v_region_id,upper(v_region_name)); 26 when others then 27 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 28 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 29 end; 30 / Enter value for rid: 15 old 8: v_region_id := &rid; new 8: v_region_id := 15; Enter value for rname: north cyprus old 9: v_region_name := &rname; new 9: v_region_name := north cyprus; v_region_name := north cyprus; * ERROR at line 9: ORA-06550: line 9, column 18: PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table long double ref char time timestamp interval date binary national character nchar SQL> ed Wrote file afiedt.buf 1 declare 2 e_check exception; 3 pragma exception_init(e_check,-2290); 4 v_region_id myregions.region_id%type; 5 v_region_name myregions.region_name%type; 6 e_not_upper exception; 7 begin 8 v_region_id := &rid; 9 v_region_name := &rname; 10 if v_region_name != upper(v_region_name) then 11 raise e_not_upper; 12 end if; 13 insert into myregions 14 values(v_region_id,v_region_name); 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk is violated'); 18 when e_check then 19 dbms_output.put_line('Changed it to NO NAME'); 20 insert into myregions 21 values(v_region_id,'NO NAME'); 22 when e_not_upper then 23 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 24 insert into myregions 25 values(v_region_id,upper(v_region_name)); 26 when others then 27 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 28 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 29* end; SQL> / Enter value for rid: 15 old 8: v_region_id := &rid; new 8: v_region_id := 15; Enter value for rname: north cyp old 9: v_region_name := &rname; new 9: v_region_name := north cyp; v_region_name := north cyp; * ERROR at line 9: ORA-06550: line 9, column 27: PLS-00103: Encountered the symbol "CYP" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol "." was substituted for "CYP" to continue. SQL> / Enter value for rid: 15 old 8: v_region_id := &rid; new 8: v_region_id := 15; Enter value for rname: 'north cyprus' old 9: v_region_name := &rname; new 9: v_region_name := 'north cyprus'; USE CAPS LOCK YOU DUMMY! declare * ERROR at line 1: ORA-02290: check constraint (USER15.SYS_C0030471) violated ORA-06512: at line 24 ORA-06510: PL/SQL: unhandled user-defined exception SQL> ed Wrote file afiedt.buf 1 declare 2 e_check exception; 3 pragma exception_init(e_check,-2290); 4 v_region_id myregions.region_id%type; 5 v_region_name myregions.region_name%type; 6 e_not_upper exception; 7 begin 8 v_region_id := &rid; 9 v_region_name := &rname; 10 if v_region_name != upper(v_region_name) then 11 raise e_not_upper; 12 end if; 13 insert into myregions 14 values(v_region_id,v_region_name); 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk is violated'); 18 when e_check then 19 dbms_output.put_line('Changed it to NO NAME'); 20 insert into myregions 21 values(v_region_id,'NO NAME'); 22 when e_not_upper then 23 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 24 insert into myregions 25 values(v_region_id,upper(v_region_name)); 26 when others then 27 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 28 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 29* end; SQL> ed Wrote file afiedt.buf 1 declare 2 e_check exception; 3 pragma exception_init(e_check,-2290); 4 v_region_id myregions.region_id%type; 5 v_region_name myregions.region_name%type; 6 e_not_upper exception; 7 begin 8 v_region_id := &rid; 9 v_region_name := &rname; 10 raise CASE_NOT_FOUND; 11 if v_region_name != upper(v_region_name) then 12 raise e_not_upper; 13 end if; 14 insert into myregions 15 values(v_region_id,v_region_name); 16 exception 17 when dup_val_on_index then 18 dbms_output.put_line('pk is violated'); 19 when e_check then 20 dbms_output.put_line('Changed it to NO NAME'); 21 insert into myregions 22 values(v_region_id,'NO NAME'); 23 when e_not_upper then 24 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 25 insert into myregions 26 values(v_region_id,upper(v_region_name)); 27 when others then 28 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 29 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 30* end; 31 / Enter value for rid: 16 old 8: v_region_id := &rid; new 8: v_region_id := 16; Enter value for rname: 'SSSS' old 9: v_region_name := &rname; new 9: v_region_name := 'SSSS'; ###the oracle error message is =ORA-06592: CASE not found while executing CASE statement### ***the oracle error number is =-6592*** PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 e_check exception; 3 pragma exception_init(e_check,-2290); 4 v_region_id myregions.region_id%type; 5 v_region_name myregions.region_name%type; 6 e_not_upper exception; 7 begin 8 v_region_id := &rid; 9 v_region_name := &rname; 10 if v_region_name != upper(v_region_name) then 11 raise e_not_upper; 12 end if; 13 insert into myregions 14 values(v_region_id,v_region_name); 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk is violated'); 18 when e_check then 19 dbms_output.put_line('Changed it to NO NAME'); 20 insert into myregions 21 values(v_region_id,'NO NAME'); 22 RAISE E_CHECK; 23 when e_not_upper then 24 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 25 insert into myregions 26 values(v_region_id,upper(v_region_name)); 27 when others then 28 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 29 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 30* end; 31 / Enter value for rid: 18 old 8: v_region_id := &rid; new 8: v_region_id := 18; Enter value for rname: 'NORTH CYPRUS' old 9: v_region_name := &rname; new 9: v_region_name := 'NORTH CYPRUS'; Changed it to NO NAME declare * ERROR at line 1: ORA-02290: check constraint (.) violated ORA-06512: at line 22 ORA-02290: check constraint (USER15.SYS_C0030471) violated SQL> SELECT * FROM MYREGIONS; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 8 ooooooo 9 AUSTRALIA 10 NO NAME 11 AY NAME 10 rows selected. SQL> ED Wrote file afiedt.buf 1 declare 2 e_check exception; 3 pragma exception_init(e_check,-2290); 4 v_region_id myregions.region_id%type; 5 v_region_name myregions.region_name%type; 6 e_not_upper exception; 7 begin 8 v_region_id := &rid; 9 v_region_name := &rname; 10 if v_region_name != upper(v_region_name) then 11 raise e_not_upper; 12 end if; 13 insert into myregions 14 values(v_region_id,v_region_name); 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk is violated'); 18 when e_check then 19 dbms_output.put_line('Changed it to NO NAME'); 20 insert into myregions 21 values(v_region_id,'NO NAME'); 22 COMMIT; 23 RAISE E_CHECK; 24 when e_not_upper then 25 dbms_output.put_line('USE CAPS LOCK YOU DUMMY!'); 26 insert into myregions 27 values(v_region_id,upper(v_region_name)); 28 when others then 29 dbms_output.put_line('###the oracle error message is ='||SQLERRM||'###'); 30 dbms_output.put_line('***the oracle error number is ='||SQLCODE||'***'); 31* end; SQL> / Enter value for rid: 89 old 8: v_region_id := &rid; new 8: v_region_id := 89; Enter value for rname: 'NORTH ASD' old 9: v_region_name := &rname; new 9: v_region_name := 'NORTH ASD'; Changed it to NO NAME declare * ERROR at line 1: ORA-02290: check constraint (.) violated ORA-06512: at line 23 ORA-02290: check constraint (USER15.SYS_C0030471) violated SQL> SELECT * FROM MYREGIONS; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 6 AAAaaa 7 ygyg 8 ooooooo 9 AUSTRALIA 10 NO NAME 11 AY NAME 89 NO NAME 11 rows selected. SQL> SPOOL OFF