SQL> --user defined exception SQL> --write an anonymous block that will read a department number and print name of the department. SQL> --treat the case where department number has three or more digits a s a user defined SQL> --exception and print "secret departments" SQL> --treat the named exceptýons related to the sql command you used as well SQL> desc departments Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> set pagesize 100 SQL> set linesize 140 SQL> set serveroutput on SQL> select * from departments 2 ; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected. SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise e_secrets; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 when too_many_rows then 18 dbms_output.put_line('too many depts????'); 19 when value_error or invalid_number then 20 dbms_output.put_line('data type/size mismatch'); 21 when e_secrets then 22 dbms_output.put_line('secret depts????'); 23 when others then 24 dbms_output.put_line('***'||sqlerrm||'****'); 25* end; 26 / Enter value for a_dept_id: 10 old 6: v_id := &a_dept_id; new 6: v_id := 10; Administration PL/SQL procedure successfully completed. SQL> / Enter value for a_dept_id: 55 old 6: v_id := &a_dept_id; new 6: v_id := 55; no such dept PL/SQL procedure successfully completed. SQL> / Enter value for a_dept_id: 110 old 6: v_id := &a_dept_id; new 6: v_id := 110; secret depts???? PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise no_data_found; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 when too_many_rows then 18 dbms_output.put_line('too many depts????'); 19 when value_error or invalid_number then 20 dbms_output.put_line('data type/size mismatch'); 21 when e_secrets then 22 dbms_output.put_line('secret depts????'); 23 when others then 24 dbms_output.put_line('***'||sqlerrm||'****'); 25* end; SQL> / Enter value for a_dept_id: 10 old 6: v_id := &a_dept_id; new 6: v_id := 10; Administration PL/SQL procedure successfully completed. SQL> / Enter value for a_dept_id: 110 old 6: v_id := &a_dept_id; new 6: v_id := 110; no such dept PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise e_secrets; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 when too_many_rows then 18 dbms_output.put_line('too many depts????'); 19 when value_error or invalid_number then 20 dbms_output.put_line('data type/size mismatch'); 21 when e_secrets then 22 dbms_output.put_line('secret depts????'); 23 raise no_data_found; 24 when others then 25 dbms_output.put_line('***'||sqlerrm||'****'); 26* end; SQL> / Enter value for a_dept_id: 10 old 6: v_id := &a_dept_id; new 6: v_id := 10; Administration PL/SQL procedure successfully completed. SQL> / Enter value for a_dept_id: 110 old 6: v_id := &a_dept_id; new 6: v_id := 110; secret depts???? declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 23 ORA-06510: PL/SQL: unhandled user-defined exception SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise e_secrets; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 raise; 18 when too_many_rows then 19 dbms_output.put_line('too many depts????'); 20 when value_error or invalid_number then 21 dbms_output.put_line('data type/size mismatch'); 22 when e_secrets then 23 dbms_output.put_line('secret depts????'); 24 raise e_secrets; 25 when others then 26 dbms_output.put_line('***'||sqlerrm||'****'); 27* end; SQL> / Enter value for a_dept_id: 10 old 6: v_id := &a_dept_id; new 6: v_id := 10; Administration PL/SQL procedure successfully completed. SQL> / Enter value for a_dept_id: 55 old 6: v_id := &a_dept_id; new 6: v_id := 55; no such dept declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 17 SQL> --when you use raise by itself without an exception name you are re-raising the same exception SQL> / Enter value for a_dept_id: 110 old 6: v_id := &a_dept_id; new 6: v_id := 110; secret depts???? declare * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 24 ORA-06510: PL/SQL: unhandled user-defined exception SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise e_secrets; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 raise e_secrets; 18 when too_many_rows then 19 dbms_output.put_line('too many depts????'); 20 when value_error or invalid_number then 21 dbms_output.put_line('data type/size mismatch'); 22 when e_secrets then 23 dbms_output.put_line('secret depts????'); 24 raise e_secrets; 25 when others then 26 dbms_output.put_line('***'||sqlerrm||'****'); 27* end; SQL> / Enter value for a_dept_id: 55 old 6: v_id := &a_dept_id; new 6: v_id := 55; no such dept declare * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 17 ORA-01403: no data found SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise e_secrets; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 raise e_secrets; 18 when too_many_rows then 19 dbms_output.put_line('too many depts????'); 20 when value_error or invalid_number then 21 dbms_output.put_line('data type/size mismatch'); 22 when e_secrets then 23 dbms_output.put_line('secret depts????'); 24 raise; 25 when others then 26 dbms_output.put_line('***'||sqlerrm||'****'); 27* end; SQL> / Enter value for a_dept_id: 55 old 6: v_id := &a_dept_id; new 6: v_id := 55; no such dept declare * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 17 ORA-01403: no data found SQL> / Enter value for a_dept_id: 110 old 6: v_id := &a_dept_id; new 6: v_id := 110; secret depts???? declare * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 24 SQL> ed Wrote file afiedt.buf 1 declare 2 v_id departments.department_id%type; 3 v_department_name departments.department_name%type; 4 e_secrets exception; 5 begin 6 v_id := &a_dept_id; 7 if v_id >= 100 or v_id<=-100 then 8 raise e_secrets; 9 end if; 10 select department_name into v_department_name 11 from departments 12 where department_id = v_id; 13 dbms_output.put_line(v_department_name); 14 exception 15 when no_data_found then 16 dbms_output.put_line('no such dept'); 17 raise ; 18 when too_many_rows then 19 dbms_output.put_line('too many depts????'); 20 when value_error or invalid_number then 21 dbms_output.put_line('data type/size mismatch'); 22 when e_secrets then 23 dbms_output.put_line('secret depts????'); 24 raise; 25 when others then 26 dbms_output.put_line('***'||sqlerrm||'****'); 27* end; SQL> / Enter value for a_dept_id: old 6: v_id := &a_dept_id; new 6: v_id := ; v_id := ; * ERROR at line 6: ORA-06550: line 6, column 10: PLS-00103: Encountered the symbol ";" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe --we can use RAISE_APPLICATION_ERROR to print oracle like error messages and "crash" our program SQL> SQL> ed Wrote file afiedt.buf 1 begin 2 dbms_output.put_line('everything is ok'); 3* end; SQL> / everything is ok PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 -- dbms_output.put_line('everything is ok'); 3 RAISE_APPLICATION_ERROR(-20001, 'Something is wrong'); 4* end; SQL> / begin * ERROR at line 1: ORA-20001: Something is wrong ORA-06512: at line 3 SQL> ed Wrote file afiedt.buf 1 begin 2 dbms_output.put_line('everything is ok'); 3 raise no_data_found; 4 exception 5 when no_data_found then 6 RAISE_APPLICATION_ERROR(-20001, 'I lied there is nothing wrong'); 7* end; SQL> / everything is ok begin * ERROR at line 1: ORA-20001: I lied there is nothing wrong ORA-06512: at line 6 SQL> ed Wrote file afiedt.buf 1 declare 2 cursor c_regs is select region_id, region_name from regions; 3 r_reg regions%rowtype; --a record 4 begin 5 open c_regs; 6 loop 7 fetch c_regs into r_reg; 8 exit when c_regs%notfound; 9 dbms_output.put_line(r_reg.region_id||' '||r_reg.region_name); 10 end loop; 11 close c_regs; 12* end; 13 / 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_regs is select region_id, region_name from regions; 3 begin 4 for r_reg in c_regs 5 loop 6 dbms_output.put_line(r_reg.region_id||' '||r_reg.region_name); 7 end loop; 8* end; 9 / 1 Europe 2 Americas 3 Asia 4 Middle East and Africa PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for r_reg in (select region_id, region_name from regions) 3 loop 4 dbms_output.put_line(r_reg.region_id||' '||r_reg.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> ed Wrote file afiedt.buf 1 begin 2 for r_reg in (select region_id, region_name from regions) 3 loop 4 dbms_output.put_line(r_reg.region_id||' '||r_reg.region_name); 5 end loop; 6* end; SQL> spool off