SQL> --exceptions SQL> select * from departments; 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 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 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 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected. SQL> create table mydept as 2 select * from departments 3 where department_id<100; Table created. SQL> --create table statement creates a new table that has the same format as the output of the select statement and also populates it with the output of the select statemnt SQL> select * from mydept; 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 9 rows selected. SQL> --the create table statemnt does NOT create any constażnts other than the not null constraint SQL> desc mydept Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> alter table mydept 2 add primary(department_id); add primary(department_id) * ERROR at line 2: ORA-00902: invalid datatype SQL> ed Wrote file afiedt.buf 1 alter table mydept 2* add primary key(department_id) SQL> / Table altered. SQL> desc mydept Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> create table myemp( 2 id number(2) primary key, 3 name varchar2(10), 4 gender char(1) check(lower(gender) in ('f','m')), 5 department_id references mydept(department_id) ); Table created. SQL> insert into myemp(id,name,gender,department_id) 2 values(&id,&name,&gender,&department_id); Enter value for id: 1 Enter value for name: 'ali' Enter value for gender: 'm' Enter value for department_id: 10 old 2: values(&id,&name,&gender,&department_id) new 2: values(1,'ali','m',10) 1 row created. SQL> select * from myemp; ID NAME G DEPARTMENT_ID ---------- ---------- - ------------- 1 ali m 10 SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number(2); 3 v_name myemp.name%type; 4 v_gender myemp.gender%type; 5 v_department_id myemp.department_id%type; 6 begin 7 v_id := &id; 8 v_name := &name; 9 v_gender := &gender; 10 v_department_id := &department_id 11 insert into myemp(id,name,gender,department_id) 12 values(v_id,v_name,v_gender,v_department_id); 13* end; 14 / Enter value for id: 2 old 7: v_id := &id; new 7: v_id := 2; Enter value for name: 'ayse' old 8: v_name := &name; new 8: v_name := 'ayse'; Enter value for gender: 'f' old 9: v_gender := &gender; new 9: v_gender := 'f'; Enter value for department_id: 20 old 10: v_department_id := &department_id new 10: v_department_id := 20 insert into myemp(id,name,gender,department_id) * ERROR at line 11: ORA-06550: line 11, column 2: PLS-00103: Encountered the symbol "INSERT" 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 "INSERT" to continue. SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number(2); 3 v_name myemp.name%type; 4 v_gender myemp.gender%type; 5 v_department_id myemp.department_id%type; 6 begin 7 v_id := &id; 8 v_name := '&name'; 9 v_gender := '&gender'; 10 v_department_id := &department_id; 11 insert into myemp(id,name,gender,department_id) 12 values(v_id,v_name,v_gender,v_department_id); 13* end; SQL> / Enter value for id: 2 old 7: v_id := &id; new 7: v_id := 2; Enter value for name: ayse old 8: v_name := '&name'; new 8: v_name := 'ayse'; Enter value for gender: f old 9: v_gender := '&gender'; new 9: v_gender := 'f'; Enter value for department_id: 20 old 10: v_department_id := &department_id; new 10: v_department_id := 20; PL/SQL procedure successfully completed. SQL> select * from myemp; ID NAME G DEPARTMENT_ID ---------- ---------- - ------------- 1 ali m 10 2 ayse f 20 SQL> declare 2 v_id number(2); 3 v_name myemp.name%type; 4 v_gender myemp.gender%type; 5 v_department_id myemp.department_id%type; 6 begin 7 v_id := &id; 8 v_name := '&name'; 9 v_gender := '&gender'; 10 v_department_id := &department_id; 11 insert into myemp(id,name,gender,department_id) 12 values(v_id,v_name,v_gender,v_department_id); 13 end; 14 / Enter value for id: 1 old 7: v_id := &id; new 7: v_id := 1; Enter value for name: ece old 8: v_name := '&name'; new 8: v_name := 'ece'; Enter value for gender: f old 9: v_gender := '&gender'; new 9: v_gender := 'f'; Enter value for department_id: 30 old 10: v_department_id := &department_id; new 10: v_department_id := 30; declare * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0017688) violated ORA-06512: at line 11 SQL> --our program crashed! SQL> --the exception with number 0ra-0001 and message ORA-00001: unique constraint (USER15.SYS_C0017688) violated is raised SQL> --this is a predefined oracle exception with name DUP_VAL_ON_INDEX SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number(2); 3 v_name myemp.name%type; 4 v_gender myemp.gender%type; 5 v_department_id myemp.department_id%type; 6 begin 7 v_id := &id; 8 v_name := '&name'; 9 v_gender := '&gender'; 10 v_department_id := &department_id; 11 insert into myemp(id,name,gender,department_id) 12 values(v_id,v_name,v_gender,v_department_id); 13 exception 14 when DUP_VAL_ON_INDEX then 15 dbms_output.put_line('pk uniqueness violated'); 16* end; SQL> / Enter value for id: 3 old 7: v_id := &id; new 7: v_id := 3; Enter value for name: veli old 8: v_name := '&name'; new 8: v_name := 'veli'; Enter value for gender: m old 9: v_gender := '&gender'; new 9: v_gender := 'm'; Enter value for department_id: 20 old 10: v_department_id := &department_id; new 10: v_department_id := 20; PL/SQL procedure successfully completed. SQL> / Enter value for id: 1 old 7: v_id := &id; new 7: v_id := 1; Enter value for name: can old 8: v_name := '&name'; new 8: v_name := 'can'; Enter value for gender: m old 9: v_gender := '&gender'; new 9: v_gender := 'm'; Enter value for department_id: 10 old 10: v_department_id := &department_id; new 10: v_department_id := 10; pk uniqueness violated PL/SQL procedure successfully completed. SQL> / Enter value for id: 4 old 7: v_id := &id; new 7: v_id := 4; Enter value for name: olaf old 8: v_name := '&name'; new 8: v_name := 'olaf'; Enter value for gender: m old 9: v_gender := '&gender'; new 9: v_gender := 'm'; Enter value for department_id: 6 old 10: v_department_id := &department_id; new 10: v_department_id := 6; declare * ERROR at line 1: ORA-02291: integrity constraint (USER15.SYS_C0017689) violated - parent key not found ORA-06512: at line 11 SQL> --the program crashed!!!!! SQL> --this constraint does not have a name it is non predefined oracle exception SQL> ed Wrote file afiedt.buf 1 declare 2 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when DUP_VAL_ON_INDEX then 17 dbms_output.put_line('pk uniqueness violated'); 18 when e_fk then 19 dbms_output.put_line('no such department'); 20* end; 21 / Enter value for id: 4 old 9: v_id := &id; new 9: v_id := 4; Enter value for name: lou old 10: v_name := '&name'; new 10: v_name := 'lou'; Enter value for gender: f old 11: v_gender := '&gender'; new 11: v_gender := 'f'; Enter value for department_id: 30 old 12: v_department_id := &department_id; new 12: v_department_id := 30; PL/SQL procedure successfully completed. SQL> select * from myemp; ID NAME G DEPARTMENT_ID ---------- ---------- - ------------- 1 ali m 10 2 ayse f 20 3 veli m 20 4 lou f 30 SQL> declare 2 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when DUP_VAL_ON_INDEX then 17 dbms_output.put_line('pk uniqueness violated'); 18 when e_fk then 19 dbms_output.put_line('no such department'); 20 end; 21 / Enter value for id: 4 old 9: v_id := &id; new 9: v_id := 4; Enter value for name: aaaa old 10: v_name := '&name'; new 10: v_name := 'aaaa'; Enter value for gender: m old 11: v_gender := '&gender'; new 11: v_gender := 'm'; Enter value for department_id: 10 old 12: v_department_id := &department_id; new 12: v_department_id := 10; pk uniqueness violated PL/SQL procedure successfully completed. SQL> / Enter value for id: 5 old 9: v_id := &id; new 9: v_id := 5; Enter value for name: yoyo old 10: v_name := '&name'; new 10: v_name := 'yoyo'; Enter value for gender: f old 11: v_gender := '&gender'; new 11: v_gender := 'f'; Enter value for department_id: 12 old 12: v_department_id := &department_id; new 12: v_department_id := 12; no such department PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when others then 17 dbms_output.put_line('something is wrong!'); 18* end; SQL> / Enter value for id: 3 old 9: v_id := &id; new 9: v_id := 3; 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'; Enter value for department_id: 10 old 12: v_department_id := &department_id; new 12: v_department_id := 10; something is wrong! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when others then 17 dbms_output.put_line('something is wrong!'); 18 when DUP_VAL_ON_INDEX then 19 dbms_output.put_line('pk uniqueness violated'); 20 when e_fk then 21 dbms_output.put_line('no such department'); 22* 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'; Enter value for department_id: 10 old 12: v_department_id := &department_id; new 12: v_department_id := 10; when others then * ERROR at line 16: ORA-06550: line 16, column 3: 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 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when others then 17 dbms_output.put_line('something is wrong!'); 18 when DUP_VAL_ON_INDEX then 19 dbms_output.put_line('pk uniqueness violated'); 20 when e_fk then 21 dbms_output.put_line('no such department'); 22 when others then 23 dbms_output.put_line('something is wrong!'); 24* end; SQL> / Enter value for id: 1 old 9: v_id := &id; new 9: v_id := 1; Enter value for name: yyy old 10: v_name := '&name'; new 10: v_name := 'yyy'; Enter value for gender: w old 11: v_gender := '&gender'; new 11: v_gender := 'w'; Enter value for department_id: 5 old 12: v_department_id := &department_id; new 12: v_department_id := 5; when others then * ERROR at line 16: ORA-06550: line 16, column 3: 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 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when DUP_VAL_ON_INDEX then 17 dbms_output.put_line('pk uniqueness violated'); 18 when e_fk then 19 dbms_output.put_line('no such department'); 20 when others then 21 dbms_output.put_line('something is wrong!'); 22* end; SQL> / Enter value for id: 1 old 9: v_id := &id; new 9: v_id := 1; Enter value for name: ttt old 10: v_name := '&name'; new 10: v_name := 'ttt'; Enter value for gender: w old 11: v_gender := '&gender'; new 11: v_gender := 'w'; Enter value for department_id: 4 old 12: v_department_id := &department_id; new 12: v_department_id := 4; something is wrong! PL/SQL procedure successfully completed. SQL> / Enter value for id: 1 old 9: v_id := &id; new 9: v_id := 1; Enter value for name: ttt old 10: v_name := '&name'; new 10: v_name := 'ttt'; Enter value for gender: m old 11: v_gender := '&gender'; new 11: v_gender := 'm'; Enter value for department_id: 34 old 12: v_department_id := &department_id; new 12: v_department_id := 34; pk uniqueness violated PL/SQL procedure successfully completed. SQL> / Enter value for id: 9 old 9: v_id := &id; new 9: v_id := 9; Enter value for name: qqq old 10: v_name := '&name'; new 10: v_name := 'qqq'; Enter value for gender: m old 11: v_gender := '&gender'; new 11: v_gender := 'm'; Enter value for department_id: old 12: v_department_id := &department_id; new 12: v_department_id := ; v_department_id := ; * ERROR at line 12: ORA-06550: line 12, column 22: 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 / Enter value for id: 9 old 9: v_id := &id; new 9: v_id := 9; Enter value for name: www old 10: v_name := '&name'; new 10: v_name := 'www'; Enter value for gender: w old 11: v_gender := '&gender'; new 11: v_gender := 'w'; Enter value for department_id: 10 old 12: v_department_id := &department_id; new 12: v_department_id := 10; something is wrong! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when DUP_VAL_ON_INDEX then 17 dbms_output.put_line('pk uniqueness violated'); 18 when e_fk then 19 dbms_output.put_line('no such department'); 20 when others then 21 dbms_output.put_line('something is wrong!'); 22 dbms_output.put_line('error code'||sqlcode); 23 dbms_output.put_line('error message'||sqlerrm); 24* end; 25 / Enter value for id: 8 old 9: v_id := &id; new 9: v_id := 8; Enter value for name: aaa old 10: v_name := '&name'; new 10: v_name := 'aaa'; Enter value for gender: a old 11: v_gender := '&gender'; new 11: v_gender := 'a'; Enter value for department_id: 10 old 12: v_department_id := &department_id; new 12: v_department_id := 10; something is wrong! error code-2290 error messageORA-02290: check constraint (USER15.SYS_C0017687) violated PL/SQL procedure successfully completed. 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: e old 11: v_gender := '&gender'; new 11: v_gender := 'e'; Enter value for department_id: 34 old 12: v_department_id := &department_id; new 12: v_department_id := 34; something is wrong! error code-2290 error messageORA-02290: check constraint (USER15.SYS_C0017687) violated PL/SQL procedure successfully completed. SQL> l 1 declare 2 e_fk exception; 3 pragma exception_init(e_fk, -2291); 4 v_id number(2); 5 v_name myemp.name%type; 6 v_gender myemp.gender%type; 7 v_department_id myemp.department_id%type; 8 begin 9 v_id := &id; 10 v_name := '&name'; 11 v_gender := '&gender'; 12 v_department_id := &department_id; 13 insert into myemp(id,name,gender,department_id) 14 values(v_id,v_name,v_gender,v_department_id); 15 exception 16 when DUP_VAL_ON_INDEX then 17 dbms_output.put_line('pk uniqueness violated'); 18 when e_fk then 19 dbms_output.put_line('no such department'); 20 when others then 21 dbms_output.put_line('something is wrong!'); 22 dbms_output.put_line('error code'||sqlcode); 23 dbms_output.put_line('error message'||sqlerrm); 24* end; SQL> ed Wrote file afiedt.buf 1 declare 2 e_ck exception; 3 pragma exception_init(e_ck, -2290); 4 e_fk exception; 5 pragma exception_init(e_fk, -2291); 6 v_id number(2); 7 v_name myemp.name%type; 8 v_gender myemp.gender%type; 9 v_department_id myemp.department_id%type; 10 begin 11 v_id := &id; 12 v_name := '&name'; 13 v_gender := '&gender'; 14 v_department_id := &department_id; 15 insert into myemp(id,name,gender,department_id) 16 values(v_id,v_name,v_gender,v_department_id); 17 exception 18 when e_ck then 19 dbms_output.put_line('gender value not acceptable'); 20 when DUP_VAL_ON_INDEX then 21 dbms_output.put_line('pk uniqueness violated'); 22 when e_fk then 23 dbms_output.put_line('no such department'); 24 when others then 25 dbms_output.put_line('something is wrong!'); 26 dbms_output.put_line('error code'||sqlcode); 27 dbms_output.put_line('error message'||sqlerrm); 28* end; SQL> / Enter value for id: 1 old 11: v_id := &id; new 11: v_id := 1; Enter value for name: aaa old 12: v_name := '&name'; new 12: v_name := 'aaa'; Enter value for gender: a old 13: v_gender := '&gender'; new 13: v_gender := 'a'; Enter value for department_id: 10 old 14: v_department_id := &department_id; new 14: v_department_id := 10; gender value not acceptable PL/SQL procedure successfully completed. SQL> / Enter value for id: 7 old 11: v_id := &id; new 11: v_id := 7; Enter value for name: aliiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii old 12: v_name := '&name'; new 12: v_name := 'aliiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii'; Enter value for gender: m old 13: v_gender := '&gender'; new 13: v_gender := 'm'; Enter value for department_id: 10 old 14: v_department_id := &department_id; new 14: v_department_id := 10; something is wrong! error code-6502 error messageORA-06502: PL/SQL: numeric or value error: character string buffer too small PL/SQL procedure successfully completed. SQL> -- as a programmer you MUST think of all possible exceptions SQL> --write a program to print name of a given region 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)=upper('&any_letter'); 7 dbms_output.put_line(v_name); 8 exception 9 when too_many_rows then 10 dbms_output.put_line('more than regions was returmed'); 11 when no_data_found then 12 dbms_output.put_line('no region was returmed'); 13 when others then 14 dbms_output.put_line(sqlerrm); 15* end; SQL> / Enter value for any_letter: e old 6: where substr(region_name,1,1)=upper('&any_letter'); new 6: where substr(region_name,1,1)=upper('e'); Europe PL/SQL procedure successfully completed. 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 substr(region_name,1,1)=upper('&any_letter'); 7 dbms_output.put_line(v_name); 8 exception 9 when too_many_rows then 10 dbms_output.put_line('more than regions was returmed'); 11 when no_data_found then 12 dbms_output.put_line('no region was returmed'); 13 when others then 14 dbms_output.put_line(sqlerrm); 15 end; 16 / Enter value for any_letter: a old 6: where substr(region_name,1,1)=upper('&any_letter'); new 6: where substr(region_name,1,1)=upper('a'); more than regions was returmed PL/SQL procedure successfully completed. SQL> / Enter value for any_letter: y old 6: where substr(region_name,1,1)=upper('&any_letter'); new 6: where substr(region_name,1,1)=upper('y'); no region was returmed PL/SQL procedure successfully completed. SQL> spool off