SQL> --in oracle all system exceptýons have a unýque number and a descrýptýon SQL> --description is a message SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where region_id=1; 7 dbms_output.put_line(v_name); 8 end; 9 / PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> / Europe PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where region_id=&anId; 7 dbms_output.put_line(v_name); 8* end; 9 / Enter value for anid: 2 old 6: where region_id=&anId; new 6: where region_id=2; Americas PL/SQL procedure successfully completed. SQL> / Enter value for anid: 6 old 6: where region_id=&anId; new 6: where region_id=6; declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 SQL> --we got an en error (exceptýon) SQL> --an exception is RAISED SQL> --this exception has the exceptýon number ORA-01403 SQL> --the description or message of this exception is : ORA-01403: no data found SQL> SQL> -- a small set of oracle exceptions also have names associated with them SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where region_id=&anId; 7 dbms_output.put_line(v_name); 8 exception 9 when no_data_found then 10 dbms_output.put_line('no such region'); 11* end; SQL> / Enter value for anid: 1 old 6: where region_id=&anId; new 6: where region_id=1; Europe PL/SQL procedure successfully completed. SQL> / Enter value for anid: 6 old 6: where region_id=&anId; new 6: where region_id=6; no such region PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where substr(region_name,1,1)='&1stletter0name'; 7 dbms_output.put_line(v_name); 8 exception 9 when no_data_found then 10 dbms_output.put_line('no such region'); 11* end; SQL> / Enter value for 1stletter0name: E old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='E'; Europe PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: M old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='M'; Middle East and Africa PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: K old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='K'; no such region PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: A old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='A'; declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where substr(region_name,1,1)='&1stletter0name'; 7 dbms_output.put_line(v_name); 8 exception 9 when no_data_found then 10 dbms_output.put_line('no such region'); 11 when too_many_rows then 12 dbms_output.put_line('more than one such region'); 13* end; SQL> / Enter value for 1stletter0name: E old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='E'; Europe PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: K old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='K'; no such region PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: A old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='A'; more than one such region PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where substr(region_name,1,1)='&1stletter0name'; 7 dbms_output.put_line(v_name); 8 exception 9 when others then 10 dbms_output.put_line('what is wrong;?????'); 11* end; SQL> / Enter value for 1stletter0name: E old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='E'; Europe PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: M old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='M'; Middle East and Africa PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: A old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='A'; what is wrong;????? PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where substr(region_name,1,1)='&1stletter0name'; 7 dbms_output.put_line(v_name); 8 exception 9 when others then 10 dbms_output.put_line('what is wrong;?????'); 11* end; SQL> / Enter value for 1stletter0name: K old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='K'; what is wrong;????? PL/SQL procedure successfully completed. SQL> --ýn exceptýon handling section if you need to you can "see" the oracle error number and oracle error message using SQLCODE and SQLERRM respectively SQL> ed Wrote file afiedt.buf 1 declare 2 v_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where substr(region_name,1,1)='&1stletter0name'; 7 dbms_output.put_line(v_name); 8 exception 9 when others then 10 dbms_output.put_line('what is wrong;?????'); 11 dbms_output.put_line('error code is '|| sqlcode); 12 dbms_output.put_line('error code is '|| sqlerrm); 13* end; 14 / Enter value for 1stletter0name: E old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='E'; Europe PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: A old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='A'; what is wrong;????? error code is -1422 error code is 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_name regions.region_name%type; 3 begin 4 select region_name into v_name 5 from regions 6 where substr(region_name,1,1)='&1stletter0name'; 7 dbms_output.put_line(v_name); 8 exception 9 when too_many_rows then 10 dbms_output.put_line('there are more than one such regions'); 11 dbms_output.put_line('Try again'); 12 when others then 13 dbms_output.put_line('what is wrong;?????'); 14 dbms_output.put_line('error code is '|| sqlcode); 15 dbms_output.put_line('error code is '|| sqlerrm); 16* end; 17 / Enter value for 1stletter0name: E old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='E'; Europe PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: A old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='A'; there are more than one such regions Try again PL/SQL procedure successfully completed. SQL> / Enter value for 1stletter0name: P old 6: where substr(region_name,1,1)='&1stletter0name'; new 6: where substr(region_name,1,1)='P'; what is wrong;????? error code is 100 error code is ORA-01403: no data found PL/SQL procedure successfully completed. SQL> --now we will learn how to handle unnamed exceptions SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> create table students( 2 id number(6) primary key, 3 name varchar2(25) check(upper(name) not like 'N%'), 4 region_id NUMBER references regiýns(region_id)); region_id NUMBER references regiýns(region_id)) * ERROR at line 4: ORA-00942: table or view does not exist SQL> ed Wrote file afiedt.buf 1 create table students( 2 id number(6) primary key, 3 name varchar2(25) check(upper(name) not like 'N%'), 4* region_id NUMBER references regions(region_id)) SQL> / region_id NUMBER references regions(region_id)) * ERROR at line 4: ORA-01031: insufficient privileges 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> --create table as statement creates and populates a new table baSED ON THE SQL> --output of the select statement attached to ýt SQL> --create table statement creates only not null constraints SQL> --so the myregions table does not have primary key SQL> alter table myregions 2 add primary key(region_id); Table altered. SQL> ed Wrote file afiedt.buf 1 create table students( 2 id number(6) primary key, 3 name varchar2(25) check(upper(name) not like 'N%'), 4* region_id NUMBER references myregions(region_id)) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number(6); 3 v_name students.name%type; 4 v_region_id students.region_id%type; 5 begin 6 v_id := &an_id; 7 v_name := &a_name; 8 v_region_id := &a_region_id; 9 insert into students(id,name,region_id) 10 values(v_id,v_name,v_region_id); 11* end; SQL> / Enter value for an_id: 1 old 6: v_id := &an_id; new 6: v_id := 1; Enter value for a_name: 'ali' old 7: v_name := &a_name; new 7: v_name := 'ali'; Enter value for a_region_id: 1 old 8: v_region_id := &a_region_id; new 8: v_region_id := 1; PL/SQL procedure successfully completed. SQL> select * from students; ID NAME REGION_ID ---------- ------------------------- ---------- 1 ali 1 SQL> / ID NAME REGION_ID ---------- ------------------------- ---------- 1 ali 1 SQL> declare 2 v_id number(6); 3 v_name students.name%type; 4 v_region_id students.region_id%type; 5 begin 6 v_id := &an_id; 7 v_name := &a_name; 8 v_region_id := &a_region_id; 9 insert into students(id,name,region_id) 10 values(v_id,v_name,v_region_id); 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: 'veli' old 7: v_name := &a_name; new 7: v_name := 'veli'; Enter value for a_region_id: 2 old 8: v_region_id := &a_region_id; new 8: v_region_id := 2; declare * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0017677) violated ORA-06512: at line 9 SQL> --we violated the unique constraint SQL> --we must handle this exception SQL> --this is a named exception : DUP_VAL_ON_INDEX SQL> / Enter value for an_id: 2 old 6: v_id := &an_id; new 6: v_id := 2; Enter value for a_name: 'Nazife' old 7: v_name := &a_name; new 7: v_name := 'Nazife'; Enter value for a_region_id: 1 old 8: v_region_id := &a_region_id; new 8: v_region_id := 1; declare * ERROR at line 1: ORA-02290: check constraint (USER15.SYS_C0017676) violated ORA-06512: at line 9 SQL> ---this is not named exception SQL> --we must declar a name for it and use pragma exception init to assign the number to the name SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number(6); 3 v_name students.name%type; 4 v_region_id students.region_id%type; 5 e_not_n exception; 6 pragma exception_init(e_not_n, -2290); 7 begin 8 v_id := &an_id; 9 v_name := &a_name; 10 v_region_id := &a_region_id; 11 insert into students(id,name,region_id) 12 values(v_id,v_name,v_region_id); 13 exception 14 when e_not_n then 15 dbms_output.put_line('student name cannot start with n'); 16 when dup_val_on_index then 17 dbms_output.put_line('uniqueness violated'); 18* end; 19 / Enter value for an_id: 3 old 8: v_id := &an_id; new 8: v_id := 3; Enter value for a_name: 'Naz' old 9: v_name := &a_name; new 9: v_name := 'Naz'; Enter value for a_region_id: 3 old 10: v_region_id := &a_region_id; new 10: v_region_id := 3; student name cannot start with n PL/SQL procedure successfully completed. SQL> / Enter value for an_id: 3 old 8: v_id := &an_id; new 8: v_id := 3; Enter value for a_name: 'veli' old 9: v_name := &a_name; new 9: v_name := 'veli'; Enter value for a_region_id: 1 old 10: v_region_id := &a_region_id; new 10: v_region_id := 1; PL/SQL procedure successfully completed. SQL> / Enter value for an_id: 3 old 8: v_id := &an_id; new 8: v_id := 3; Enter value for a_name: 'ayse' old 9: v_name := &a_name; new 9: v_name := 'ayse'; Enter value for a_region_id: 1 old 10: v_region_id := &a_region_id; new 10: v_region_id := 1; uniqueness violated PL/SQL procedure successfully completed. SQL> select * from students; ID NAME REGION_ID ---------- ------------------------- ---------- 1 ali 1 3 veli 1 SQL> spool off