SQL> --user defined exceptions are exceptions that oracle dbms engine does not detect SQL> create table emp( 2 id number(2) primary key, 3 name varchar2(20); name varchar2(20) * ERROR at line 3: ORA-00907: missing right parenthesis SQL> ed Wrote file afiedt.buf 1 create table emp( 2 id number(2) primary key, 3 name varchar2(20), 4 gender char(1) check (gender in ('F','M')) 5* ) SQL> / Table created. SQL> insert into emp 2 values(&id,null,'&gender'); Enter value for id: 1 Enter value for gender: F old 2: values(&id,null,'&gender') new 2: values(1,null,'F') 1 row created. SQL> / Enter value for id: 1 Enter value for gender: F old 2: values(&id,null,'&gender') new 2: values(1,null,'F') insert into emp * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0015463) violated SQL> / Enter value for id: 2 Enter value for gender: k old 2: values(&id,null,'&gender') new 2: values(2,null,'k') insert into emp * ERROR at line 1: ORA-02290: check constraint (USER15.SYS_C0015462) violated SQL> --WRITE A PL/SQL BLOCK TO INSERT A NEW EMPLOYEE INTO THE EMP TABLE SQL> --HANDLE THE CASE WHERE ENAME STARTS WITH n AS A USER DEFINED EXCEPTION SQL> --HANDLE THE EXCEPTIONS RELATED TO THE SQL COMMAND YOU USED SQL> ED Wrote file afiedt.buf 1 --user defined exceptions 2 DECLARE 3 V_id emp.id%type; 4 v_naME EMP.NAME%TYPE; 5 V_GENDER EMP.GENDER%TYPE; 6 e_ck exception; --unnamed system exception 7 pragma exception_init(e_ck,-2290); 8 --user defined exception UDE 9 --UDE step 1 : declare a name 10 e_notN exception; 11 BEGIN 12 V_id := &id; 13 v_naME := '&name'; 14 V_GENDER := '&GENDER'; 15 --UDE step 2 : Use an if statement (usually) to check if the 16 -- the error condition occured 17 if v_name like 'N%' then 18 raise e_notN; --UDE step 2.1 : raise the exception 19 end if; 20 insert into emp 21 values(v_id,v_name,v_gender) 22 exception 23 when dup_val_on_index then --named system exception 24 dbms_output.put_line('same id exists in the db'); 25 when invalid_number or value_error then 26 dbms_output.put_line('datatype/size problem'); 27 when e_ck then 28 dbms_output.put_line('gender values can only be F or M'); 29 --UDE step 3 : handle the exception using its name 30 when e_notN then 31 dbms_output.put_line('names cannot start with N'); 32 when others then 33 dbms_output.put_line('*****'||sqlerrm||'*****'); 34* end; 35 / Enter value for id: 2 old 12: V_id := &id; new 12: V_id := 2; Enter value for name: ali old 13: v_naME := '&name'; new 13: v_naME := 'ali'; Enter value for gender: M old 14: V_GENDER := '&GENDER'; new 14: V_GENDER := 'M'; exception * ERROR at line 22: ORA-06550: line 21, column 32: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 20, column 3: PL/SQL: SQL Statement ignored ORA-06550: line 25, column 3: PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "case" was substituted for "WHEN" to continue. ORA-06550: line 34, column 4: PLS-00103: Encountered the symbol ";" when expecting one of the following: case SQL> ed Wrote file afiedt.buf 1 --user defined exceptions 2 DECLARE 3 V_id emp.id%type; 4 v_naME EMP.NAME%TYPE; 5 V_GENDER EMP.GENDER%TYPE; 6 e_ck exception; --unnamed system exception 7 pragma exception_init(e_ck,-2290); 8 --user defined exception UDE 9 --UDE step 1 : declare a name 10 e_notN exception; 11 BEGIN 12 V_id := &id; 13 v_naME := '&name'; 14 V_GENDER := '&GENDER'; 15 --UDE step 2 : Use an if statement (usually) to check if the 16 -- the error condition occured 17 if v_name like 'N%' then 18 raise e_notN; --UDE step 2.1 : raise the exception 19 end if; 20 insert into emp(id,name,gender) 21 values(v_id,v_name,v_gender); 22 exception 23 when dup_val_on_index then --named system exception 24 dbms_output.put_line('same id exists in the db'); 25 when invalid_number or value_error then 26 dbms_output.put_line('datatype/size problem'); 27 when e_ck then 28 dbms_output.put_line('gender values can only be F or M'); 29 --UDE step 3 : handle the exception using its name 30 when e_notN then 31 dbms_output.put_line('names cannot start with N'); 32 when others then 33 dbms_output.put_line('*****'||sqlerrm||'*****'); 34* end; SQL> / Enter value for id: 2 old 12: V_id := &id; new 12: V_id := 2; Enter value for name: ali old 13: v_naME := '&name'; new 13: v_naME := 'ali'; Enter value for gender: M old 14: V_GENDER := '&GENDER'; new 14: V_GENDER := 'M'; PL/SQL procedure successfully completed. SQL> select * from emp; ID NAME G ---------- -------------------- - 1 F 2 ali M SQL> --user defined exceptions SQL> DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 --user defined exception UDE 8 --UDE step 1 : declare a name 9 e_notN exception; 10 BEGIN 11 V_id := &id; 12 v_naME := '&name'; 13 V_GENDER := '&GENDER'; 14 --UDE step 2 : Use an if statement (usually) to check if the 15 -- the error condition occured 16 if v_name like 'N%' then 17 raise e_notN; --UDE step 2.1 : raise the exception 18 end if; 19 insert into emp(id,name,gender) 20 values(v_id,v_name,v_gender); 21 exception 22 when dup_val_on_index then --named system exception 23 dbms_output.put_line('same id exists in the db'); 24 when invalid_number or value_error then 25 dbms_output.put_line('datatype/size problem'); 26 when e_ck then 27 dbms_output.put_line('gender values can only be F or M'); 28 --UDE step 3 : handle the exception using its name 29 when e_notN then 30 dbms_output.put_line('names cannot start with N'); 31 when others then 32 dbms_output.put_line('*****'||sqlerrm||'*****'); 33 end; 34 / Enter value for id: 1 old 11: V_id := &id; new 11: V_id := 1; Enter value for name: veli old 12: v_naME := '&name'; new 12: v_naME := 'veli'; Enter value for gender: M old 13: V_GENDER := '&GENDER'; new 13: V_GENDER := 'M'; same id exists in the db PL/SQL procedure successfully completed. SQL> / Enter value for id: 3 old 11: V_id := &id; new 11: V_id := 3; Enter value for name: ayse old 12: v_naME := '&name'; new 12: v_naME := 'ayse'; Enter value for gender: K old 13: V_GENDER := '&GENDER'; new 13: V_GENDER := 'K'; gender values can only be F or M PL/SQL procedure successfully completed. SQL> / Enter value for id: 3 old 11: V_id := &id; new 11: V_id := 3; Enter value for name: Nazife old 12: v_naME := '&name'; new 12: v_naME := 'Nazife'; Enter value for gender: F old 13: V_GENDER := '&GENDER'; new 13: V_GENDER := 'F'; names cannot start with N PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise e_notN; 14 insert into emp(id,name,gender) 15 values(v_id,v_name,v_gender); 16 exception 17 when dup_val_on_index then --named system exception 18 dbms_output.put_line('same id exists in the db'); 19 when invalid_number or value_error then 20 dbms_output.put_line('datatype/size problem'); 21 when e_ck then 22 dbms_output.put_line('gender values can only be F or M'); 23 when e_notN then 24 dbms_output.put_line('names cannot start with N'); 25 when others then 26 dbms_output.put_line('*****'||sqlerrm||'*****'); 27* end; 28 / Enter value for id: 4 old 9: V_id := &id; new 9: V_id := 4; Enter value for name: aaa old 10: v_naME := '&name'; new 10: v_naME := 'aaa'; Enter value for gender: F old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'F'; exception * ERROR at line 16: ORA-06550: line 16, column 2: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare else elsif end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge ORA-06550: line 27, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise e_notN; 14 end if; 15 insert into emp(id,name,gender) 16 values(v_id,v_name,v_gender); 17 exception 18 when dup_val_on_index then --named system exception 19 dbms_output.put_line('same id exists in the db'); 20 when invalid_number or value_error then 21 dbms_output.put_line('datatype/size problem'); 22 when e_ck then 23 dbms_output.put_line('gender values can only be F or M'); 24 when e_notN then 25 dbms_output.put_line('names cannot start with N'); 26 when others then 27 dbms_output.put_line('*****'||sqlerrm||'*****'); 28* end; SQL> / Enter value for id: 4 old 9: V_id := &id; new 9: V_id := 4; Enter value for name: aa old 10: v_naME := '&name'; new 10: v_naME := 'aa'; Enter value for gender: F old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'F'; PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise invalid_number; 14 end if; 15 insert into emp(id,name,gender) 16 values(v_id,v_name,v_gender); 17 exception 18 when dup_val_on_index then --named system exception 19 dbms_output.put_line('same id exists in the db'); 20 when invalid_number or value_error then 21 dbms_output.put_line('datatype/size problem'); 22 when e_ck then 23 dbms_output.put_line('gender values can only be F or M'); 24 when e_notN then 25 dbms_output.put_line('names cannot start with N'); 26 when others then 27 dbms_output.put_line('*****'||sqlerrm||'*****'); 28* end; SQL> / Enter value for id: 6 old 9: V_id := &id; new 9: V_id := 6; Enter value for name: aaa old 10: v_naME := '&name'; new 10: v_naME := 'aaa'; Enter value for gender: F old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'F'; PL/SQL procedure successfully completed. SQL> / Enter value for id: 7 old 9: V_id := &id; new 9: V_id := 7; Enter value for name: Nazife old 10: v_naME := '&name'; new 10: v_naME := 'Nazife'; Enter value for gender: F old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'F'; datatype/size problem PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise too_many_rows; 14 end if; 15 insert into emp(id,name,gender) 16 values(v_id,v_name,v_gender); 17 exception 18 when dup_val_on_index then --named system exception 19 dbms_output.put_line('same id exists in the db'); 20 when invalid_number or value_error then 21 dbms_output.put_line('datatype/size problem'); 22 when e_ck then 23 dbms_output.put_line('gender values can only be F or M'); 24 when e_notN then 25 dbms_output.put_line('names cannot start with N'); 26 when others then 27 dbms_output.put_line('*****'||sqlerrm||'*****'); 28* end; SQL> / Enter value for id: 7 old 9: V_id := &id; new 9: V_id := 7; Enter value for name: Nazife old 10: v_naME := '&name'; new 10: v_naME := 'Nazife'; Enter value for gender: F old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'F'; *****ORA-01422: exact fetch returns more than requested number of rows***** PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise e_notN; 14 end if; 15 insert into emp(id,name,gender) 16 values(v_id,v_name,v_gender); 17 exception 18 when dup_val_on_index then --named system exception 19 dbms_output.put_line('same id exists in the db'); 20 when invalid_number or value_error then 21 dbms_output.put_line('datatype/size problem'); 22 when e_ck then 23 dbms_output.put_line('gender values can only be F or M'); 24 when e_notN then 25 dbms_output.put_line('names cannot start with N'); 26 raise too_many_rows; 27 when others then 28 dbms_output.put_line('*****'||sqlerrm||'*****'); 29* end; SQL> / Enter value for id: 7 old 9: V_id := &id; new 9: V_id := 7; Enter value for name: Nazife old 10: v_naME := '&name'; new 10: v_naME := 'Nazife'; Enter value for gender: F old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'F'; names cannot start with N DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 26 ORA-06510: PL/SQL: unhandled user-defined exception SQL> using raise in the exception handling part will cause the programn to crash wıth the excveptıon name!!! SP2-0734: unknown command beginning "using rais..." - rest of line ignored. SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise e_notN; 14 end if; 15 insert into emp(id,name,gender) 16 values(v_id,v_name,v_gender); 17 exception 18 when dup_val_on_index then --named system exception 19 dbms_output.put_line('same id exists in the db'); 20 raise e_notN; 21 when invalid_number or value_error then 22 dbms_output.put_line('datatype/size problem'); 23 when e_ck then 24 dbms_output.put_line('gender values can only be F or M'); 25 when e_notN then 26 dbms_output.put_line('names cannot start with N'); 27 raise too_many_rows; 28 when others then 29 dbms_output.put_line('*****'||sqlerrm||'*****'); 30* end; SQL> / Enter value for id: 1 old 9: V_id := &id; new 9: V_id := 1; Enter value for name: ali old 10: v_naME := '&name'; new 10: v_naME := 'ali'; Enter value for gender: M old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'M'; same id exists in the db DECLARE * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 20 ORA-00001: unique constraint (USER15.SYS_C0015463) violated SQL> ed Wrote file afiedt.buf 1 DECLARE 2 V_id emp.id%type; 3 v_naME EMP.NAME%TYPE; 4 V_GENDER EMP.GENDER%TYPE; 5 e_ck exception; --unnamed system exception 6 pragma exception_init(e_ck,-2290); 7 e_notN exception; 8 BEGIN 9 V_id := &id; 10 v_naME := '&name'; 11 V_GENDER := '&GENDER'; 12 if v_name like 'N%' then 13 raise e_notN; 14 end if; 15 insert into emp(id,name,gender) 16 values(v_id,v_name,v_gender); 17 exception 18 when dup_val_on_index then --named system exception 19 dbms_output.put_line('same id exists in the db'); 20 raise; 21 when invalid_number or value_error then 22 dbms_output.put_line('datatype/size problem'); 23 when e_ck then 24 dbms_output.put_line('gender values can only be F or M'); 25 when e_notN then 26 dbms_output.put_line('names cannot start with N'); 27 raise too_many_rows; 28 when others then 29 dbms_output.put_line('*****'||sqlerrm||'*****'); 30* end; SQL> / Enter value for id: 1 old 9: V_id := &id; new 9: V_id := 1; Enter value for name: aaa old 10: v_naME := '&name'; new 10: v_naME := 'aaa'; Enter value for gender: M old 11: V_GENDER := '&GENDER'; new 11: V_GENDER := 'M'; same id exists in the db DECLARE * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0015463) violated ORA-06512: at line 20 SQL> --when you use raise without an exception name, you are re-raising the most recent exception SQL> begin 2 dbms_output.put_line('hello'); 3 end; 4 / hello PL/SQL procedure successfully completed. SQL> begin 2 raise_application_error(-20001,'hello'); 3 end; 4 / begin * ERROR at line 1: ORA-20001: hello ORA-06512: at line 2 SQL> using raise_application error, you raise an exception and cause the program to crash and you8 print an oracle like error message SP2-0734: unknown command beginning "using rais..." - rest of line ignored. SQL> --you can use raise_application_error in executable or mexceptıon handlıng sectıons SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is 3 select * 4 from regions; 5 v_REGION_ID regions.REGION_ID%type; 6 v_REGION_NAME regions.REGION_NAME%type; 7 begin 8 open c; 9 loop 10 fetch c into v_REGION_id, V_REGION_NAME; 11 exit when c%notfound; 12 dbms_output.put_line( v_REGION_id||' ' ||v_REGION_NAME); 13 end loop; 14 close c; 15* end; 16 / 1 Europe 2 Americas 3 Asia 4 Middle East and Africa PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is 3 select * 4 from regions; 5 --v_REGION_ID regions.REGION_ID%type; 6 --v_REGION_NAME regions.REGION_NAME%type; 7 --declaring a record based on a row of a table 8 r regions%rowtype; 9 begin 10 open c; 11 loop 12 fetch c into r; 13 exit when c%notfound; 14 dbms_output.put_line( r.REGION_id||' ' ||r.REGION_NAME); 15 end loop; 16 close c; 17* end; 18 / 1 Europe 2 Americas 3 Asia 4 Middle East and Africa PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is 3 select * 4 from regions; 5 begin 6 --a cursor for loop 7 for suleiman in c 8 loop 9 dbms_output.put_line( suleiman.REGION_id||' ' ||suleiman.REGION_NAME); 10 end loop; 11* end; 12 / 1 Europe 2 Americas 3 Asia 4 Middle East and Africa PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c is 3 select * 4 from regions; 5 begin 6 --a cursor for loop 7 for suleiman in c 8 loop 9 dbms_output.put_line( suleiman.REGION_id||' ' ||suleiman.REGION_NAME); 10 exit when c%rowcount=3; 11 end loop; 12* end; SQL> / 1 Europe 2 Americas 3 Asia PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for suleiman in (select * from regions) 3 loop 4 dbms_output.put_line( suleiman.REGION_id||' ' ||suleiman.REGION_NAME); 5 exit when c%rowcount=3; 6 end loop; 7* end; 8 / exit when c%rowcount=3; * ERROR at line 5: ORA-06550: line 5, column 15: PLS-00201: identifier 'C' must be declared ORA-06550: line 5, column 5: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 begin 2 for suleiman in (select * from regions) 3 loop 4 dbms_output.put_line( suleiman.REGION_id||' ' ||suleiman.REGION_NAME); 5 end loop; 6* end; 7 / 1 Europe 2 Americas 3 Asia 4 Middle East and Africa PL/SQL procedure successfully completed. SQL> begin 2 raise invalid_cursor; 3 end; 4 / begin * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 2 SQL> begin 2 raise; 3 end; 4 / raise; * ERROR at line 2: ORA-06550: line 2, column 2: PLS-00367: a RAISE statement with no exception name must be inside an exception handler ORA-06550: line 2, column 2: PL/SQL: Statement ignored SQL> spool off