SQL> --exceptions SQL> create tabel mycountries as select * from countries; create tabel mycountries as select * from countries * ERROR at line 1: ORA-00901: invalid CREATE command SQL> create table mycountries as select * from countries; create table mycountries as select * from countries * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> drop table mycountries; Table dropped. SQL> create table mycountries as select * from countries; Table created. SQL> blockterminator "." (hex 2e) pagesize 14 pagesize 14 linesize 80 select * from mycountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> set serveroutput on SQL> alter table mycountries add constraint mycountries_pk primary key; alter table mycountries add constraint mycountries_pk primary key * ERROR at line 1: ORA-00906: missing left parenthesis SQL> alter table mycountries add constraint mycountries_pk primary key(country_id); Table altered. SQL> desc mycountries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> insert into mycountries 2 values('JP','japonya',null); insert into mycountries * ERROR at line 1: ORA-00001: unique constraint (USER15.MYCOUNTRIES_PK) violated SQL> ed Wrote file afiedt.buf 1 begin 2 insert into mycountries 3 values('JP','japonya',null); 4* end; SQL> / begin * ERROR at line 1: ORA-00001: unique constraint (USER15.MYCOUNTRIES_PK) violated ORA-06512: at line 2 SQL> --this is a named exception we can handle easily by using the name SQL> ed Wrote file afiedt.buf 1 begin 2 insert into mycountries 3 values('JP','japonya',null); 4 exception 5 when dup_val_on_index then 6 dbms_output.put_line('pk violation! try again later'); 7* end; 8 / pk violation! try again later PL/SQL procedure successfully completed. SQL> alter table mycountries 2 add constraint check(country_name not like '%NORTH%'); add constraint check(country_name not like '%NORTH%') * ERROR at line 2: ORA-02438: Column check constraint cannot reference other columns SQL> ed Wrote file afiedt.buf 1 alter table mycountries 2* add constraint mycountries_ck check(country_name not like '%NORTH%') SQL> / Table altered. SQL> ed Wrote file afiedt.buf 1 alter table mycountries 2* add constraint mycountries_ck check(country_name not like '%NORTH%') SQL> ed Wrote file afiedt.buf 1 begin 2 insert into mycountries 3 values('NN','NORTH SOUTH',null); 4 exception 5 when dup_val_on_index then 6 dbms_output.put_line('pk violation! try again later'); 7* end; SQL> / begin * ERROR at line 1: ORA-02290: check constraint (USER15.MYCOUNTRIES_CK) violated ORA-06512: at line 2 SQL> --this is an unnamed exception SQL> --we have to find out the error number then create an exception name and SQL> --then use pragma exception_init SQL> ed Wrote file afiedt.buf 1 begin 2 insert into mycountries 3 values('NN','NORTH SOUTH',null); 4 exception 5 when dup_val_on_index then 6 dbms_output.put_line('pk violation! try again later'); 7 when others then 8 dbms_output.put_line('oracle error number ='||SQLCODE); 9 dbms_output.put_line('oracle error message ='||SQLERRM); 10* end; SQL> / oracle error number =-2290 oracle error message =ORA-02290: check constraint (USER15.MYCOUNTRIES_CK) violated PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new country 2 --handle one relevant predefined exception 3 --handle the unnamed exception with number -2290 4 --(handle the check violation: ora-02290:check constraint..) 5 --handle all other error conditions by print the oracle error message 6 declare 7 e_check_violate exception; 8 pragma exception_init(e_check_violate,-2290); 9 begin 10 insert into mycountries 11 values('NN','NORTH SOUTH',null); 12 exception 13 when dup_val_on_index then 14 dbms_output.put_line('pk violation! try again later'); 15 when e_check_violate then 16 dbms_output.put_line('country name cannot contain north'); 17 when others then 18 dbms_output.put_line('oracle error number ='||SQLCODE); 19 dbms_output.put_line('oracle error message ='||SQLERRM); 20* end; 21 / country name cannot contain north PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new country 2 --handle one relevant predefined exception 3 --handle the unnamed exception with number -2290 by setting the country name to 'NO NAME' 4 --(handle the check violation: ora-02290:check constraint..) 5 --handle all other error conditions by print the oracle error message 6 declare 7 e_check_violate exception; 8 pragma exception_init(e_check_violate,-2290); 9 begin 10 insert into mycountries 11 values('NN','NORTH SOUTH',null); 12 exception 13 when dup_val_on_index then 14 dbms_output.put_line('pk violation! try again later'); 15 when e_check_violate then 16 dbms_output.put_line('country name cannot contain north'); 17 insert into mycountries 18 values('NN','NO NAME',null); 19 when others then 20 dbms_output.put_line('oracle error number ='||SQLCODE); 21 dbms_output.put_line('oracle error message ='||SQLERRM); 22* end; 23 / country name cannot contain north PL/SQL procedure successfully completed. SQL> select * from mycountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 NN NO NAME 26 rows selected. SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new country 2 --read values for all attributes from the keyboard 3 --handle one relevant predefined exception 4 --handle the unnamed exception with number -2290 by setting the country name to 'NO NAME' 5 --(handle the check violation: ora-02290:check constraint..) 6 --handle all other error conditions by print the oracle error message 7 declare 8 e_check_violate exception; 9 pragma exception_init(e_check_violate,-2290); 10 v_country_id mycountries.country_id%type; 11 v_country_name mycountries.country_name%type; 12 v_region_id mycountries.region_id%type; 13 begin 14 v_country_id := &id; 15 v_country_name := &name; 16 v_region_id :=®ion; 17 insert into mycountries 18 values(v_country_id, v_country_name,v_region_id); 19 exception 20 when dup_val_on_index then 21 dbms_output.put_line('pk violation! try again later'); 22 when e_check_violate then 23 dbms_output.put_line('country name cannot contain north.changed to no name'); 24 insert into mycountries 25 values(v_country_id,'NO NAME',v_region_id); 26 when others then 27 dbms_output.put_line('oracle error number ='||SQLCODE); 28 dbms_output.put_line('oracle error message ='||SQLERRM); 29* end; 30 / Enter value for id: GG old 14: v_country_id := &id; new 14: v_country_id := GG; Enter value for name: old 15: v_country_name := &name; new 15: v_country_name := ; Enter value for region: old 16: v_region_id :=®ion; new 16: v_region_id :=; v_country_name := ; * ERROR at line 15: ORA-06550: line 15, column 20: 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: 'GG' old 14: v_country_id := &id; new 14: v_country_id := 'GG'; Enter value for name: 'GIBRALTAR' old 15: v_country_name := &name; new 15: v_country_name := 'GIBRALTAR'; Enter value for region: 1 old 16: v_region_id :=®ion; new 16: v_region_id :=1; PL/SQL procedure successfully completed. SQL> select * from mycountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 NN NO NAME GG GIBRALTAR 1 27 rows selected. SQL> --write an anonymous block to add a new country SQL> --read values for all attributes from the keyboard SQL> --handle one relevant predefined exception SQL> --handle the unnamed exception with number -2290 by setting the country name to 'NO NAME' SQL> --(handle the check violation: ora-02290:check constraint..) SQL> --handle all other error conditions by print the oracle error message SQL> declare 2 e_check_violate exception; 3 pragma exception_init(e_check_violate,-2290); 4 v_country_id mycountries.country_id%type; 5 v_country_name mycountries.country_name%type; 6 v_region_id mycountries.region_id%type; 7 begin 8 v_country_id := &id; 9 v_country_name := &name; 10 v_region_id :=®ion; 11 insert into mycountries 12 values(v_country_id, v_country_name,v_region_id); 13 exception 14 when dup_val_on_index then 15 dbms_output.put_line('pk violation! try again later'); 16 when e_check_violate then 17 dbms_output.put_line('country name cannot contain north.changed to no name'); 18 insert into mycountries 19 values(v_country_id,'NO NAME',v_region_id); 20 when others then 21 dbms_output.put_line('oracle error number ='||SQLCODE); 22 dbms_output.put_line('oracle error message ='||SQLERRM); 23 end; 24 / Enter value for id: 'QW' old 8: v_country_id := &id; new 8: v_country_id := 'QW'; Enter value for name: 'NORTH QWARTS' old 9: v_country_name := &name; new 9: v_country_name := 'NORTH QWARTS'; Enter value for region: 2 old 10: v_region_id :=®ion; new 10: v_region_id :=2; country name cannot contain north.changed to no name PL/SQL procedure successfully completed. SQL> select * from mycountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 NN NO NAME GG GIBRALTAR 1 QW NO NAME 2 28 rows selected. SQL> ED Wrote file afiedt.buf 1 declare 2 e_check_violate exception; 3 pragma exception_init(e_check_violate,-2290); 4 v_country_id mycountries.country_id%type; 5 v_country_name mycountries.country_name%type; 6 v_region_id mycountries.region_id%type; 7 begin 8 v_country_id := &id; 9 v_country_name := &name; 10 v_region_id :=®ion; 11 insert into mycountries 12 values(v_country_id, v_country_name,v_region_id); 13 RAISE DUP_VAL_ON_INDEX; 14 exception 15 when dup_val_on_index then 16 dbms_output.put_line('pk violation! try again later'); 17 when e_check_violate then 18 dbms_output.put_line('country name cannot contain north.changed to no name'); 19 insert into mycountries 20 values(v_country_id,'NO NAME',v_region_id); 21 when others then 22 dbms_output.put_line('oracle error number ='||SQLCODE); 23 dbms_output.put_line('oracle error message ='||SQLERRM); 24* end; 25 / Enter value for id: 'AA' old 8: v_country_id := &id; new 8: v_country_id := 'AA'; Enter value for name: 'AAAAAAA' old 9: v_country_name := &name; new 9: v_country_name := 'AAAAAAA'; Enter value for region: 1 old 10: v_region_id :=®ion; new 10: v_region_id :=1; pk violation! try again later PL/SQL procedure successfully completed. SQL> SELECT * FROM MYCOUNTRIES; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 NN NO NAME GG GIBRALTAR 1 QW NO NAME 2 AA AAAAAAA 1 29 rows selected. SQL> ED Wrote file afiedt.buf 1 declare 2 e_check_violate exception; 3 pragma exception_init(e_check_violate,-2290); 4 v_country_id mycountries.country_id%type; 5 v_country_name mycountries.country_name%type; 6 v_region_id mycountries.region_id%type; 7 begin 8 v_country_id := &id; 9 v_country_name := &name; 10 v_region_id :=®ion; 11 RAISE STORAGE_ERROR; 12 insert into mycountries 13 values(v_country_id, v_country_name,v_region_id); 14 RAISE DUP_VAL_ON_INDEX; 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk violation! try again later'); 18 when e_check_violate then 19 dbms_output.put_line('country name cannot contain north.changed to no name'); 20 insert into mycountries 21 values(v_country_id,'NO NAME',v_region_id); 22 when others then 23 dbms_output.put_line('oracle error number ='||SQLCODE); 24 dbms_output.put_line('oracle error message ='||SQLERRM); 25 end; 26* / SQL> / Enter value for id: 'RR' old 8: v_country_id := &id; new 8: v_country_id := 'RR'; Enter value for name: 'RRRRRR' old 9: v_country_name := &name; new 9: v_country_name := 'RRRRRR'; Enter value for region: 1 old 10: v_region_id :=®ion; new 10: v_region_id :=1; / * ERROR at line 26: ORA-06550: line 26, column 2: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. SQL> ED Wrote file afiedt.buf 1 declare 2 e_check_violate exception; 3 pragma exception_init(e_check_violate,-2290); 4 v_country_id mycountries.country_id%type; 5 v_country_name mycountries.country_name%type; 6 v_region_id mycountries.region_id%type; 7 begin 8 v_country_id := &id; 9 v_country_name := &name; 10 v_region_id :=®ion; 11 RAISE STORAGE_ERROR; 12 insert into mycountries 13 values(v_country_id, v_country_name,v_region_id); 14 RAISE DUP_VAL_ON_INDEX; 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk violation! try again later'); 18 when e_check_violate then 19 dbms_output.put_line('country name cannot contain north.changed to no name'); 20 insert into mycountries 21 values(v_country_id,'NO NAME',v_region_id); 22 when others then 23 dbms_output.put_line('oracle error number ='||SQLCODE); 24 dbms_output.put_line('oracle error message ='||SQLERRM); 25* end; SQL> / Enter value for id: 'YY' old 8: v_country_id := &id; new 8: v_country_id := 'YY'; Enter value for name: 'YYYYYYY' old 9: v_country_name := &name; new 9: v_country_name := 'YYYYYYY'; Enter value for region: 1 old 10: v_region_id :=®ion; new 10: v_region_id :=1; oracle error number =-6500 oracle error message =ORA-06500: PL/SQL: storage error PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 e_check_violate exception; 3 pragma exception_init(e_check_violate,-2290); 4 v_country_id mycountries.country_id%type; 5 v_country_name mycountries.country_name%type; 6 v_region_id mycountries.region_id%type; 7 begin 8 v_country_id := &id; 9 v_country_name := &name; 10 v_region_id :=®ion; 11 RAISE STORAGE_ERROR; 12 insert into mycountries 13 values(v_country_id, v_country_name,v_region_id); 14 RAISE DUP_VAL_ON_INDEX; 15 exception 16 when dup_val_on_index then 17 dbms_output.put_line('pk violation! try again later'); 18 when e_check_violate then 19 dbms_output.put_line('country name cannot contain north.changed to no name'); 20 insert into mycountries 21 values(v_country_id,'NO NAME',v_region_id); 22 when others then 23 dbms_output.put_line('oracle error number ='||SQLCODE); 24 dbms_output.put_line('oracle error message ='||SQLERRM); 25 RAISE ZERO_DIVIDE; 26* end; SQL> / Enter value for id: 'ZZ' old 8: v_country_id := &id; new 8: v_country_id := 'ZZ'; Enter value for name: 'ZZZZZZ' old 9: v_country_name := &name; new 9: v_country_name := 'ZZZZZZ'; Enter value for region: 1 old 10: v_region_id :=®ion; new 10: v_region_id :=1; oracle error number =-6500 oracle error message =ORA-06500: PL/SQL: storage error declare * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 25 ORA-06500: PL/SQL: storage error SQL> ED Wrote file afiedt.buf 1 --write an anonymous block to add a new country 2 --read values for all attributes from the keyboard 3 --handle one relevant predefined exception 4 --handle the unnamed exception with number -2290 by setting the country name to 'NO NAME' 5 --(handle the check violation: ora-02290:check constraint..) 6 --HANDLE THE CASE WHERE NAME IS WRITTEN IN LOWER CASE LETTERS AS A USER DEFINED EXCEPTION 7 --AND HANDLE IT BY PRINTING 'USE CAPSLOCK NEXT TIME YOU DUMMY!' 8 --AND ENTER THE NAME IN UPPER CASE LETTERS 9 --handle all other error conditions by print the oracle error message 10 declare 11 E_UPPER EXCEPTION; 12 e_check_violate exception; 13 pragma exception_init(e_check_violate,-2290); 14 v_country_id mycountries.country_id%type; 15 v_country_name mycountries.country_name%type; 16 v_region_id mycountries.region_id%type; 17 begin 18 v_country_id := &id; 19 v_country_name := &name; 20 v_region_id :=®ion; 21 IF COUNTRY_NAME!=UPPER(COUNTRY_NAME) THEN 22 RAISE E_UPPER; 23 END IF; 24 insert into mycountries 25 values(v_country_id, v_country_name,v_region_id); 26 exception 27 when dup_val_on_index then 28 dbms_output.put_line('pk violation! try again later'); 29 when e_check_violate then 30 dbms_output.put_line('country name cannot contain north.changed to no name'); 31 insert into mycountries 32 values(v_country_id,'NO NAME',v_region_id); 33 WHEN E_UPPER THEN 34 DBMS_OUTPUT.PUT_LINE('USE CAPSLOCK NEXT TIME YOU DUMMY!'); 35 insert into mycountries 36 values(v_country_id, UPPER(v_country_name),v_region_id); 37 when others then 38 dbms_output.put_line('oracle error number ='||SQLCODE); 39 dbms_output.put_line('oracle error message ='||SQLERRM); 40 end; 41* / 42 / Enter value for id: 'CC' old 18: v_country_id := &id; new 18: v_country_id := 'CC'; Enter value for name: 'ccccc' old 19: v_country_name := &name; new 19: v_country_name := 'ccccc'; Enter value for region: 3 old 20: v_region_id :=®ion; new 20: v_region_id :=3; / * ERROR at line 41: ORA-06550: line 41, column 2: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new country 2 --read values for all attributes from the keyboard 3 --handle one relevant predefined exception 4 --handle the unnamed exception with number -2290 by setting the country name to 'NO NAME' 5 --(handle the check violation: ora-02290:check constraint..) 6 --HANDLE THE CASE WHERE NAME IS WRITTEN IN LOWER CASE LETTERS AS A USER DEFINED EXCEPTION 7 --AND HANDLE IT BY PRINTING 'USE CAPSLOCK NEXT TIME YOU DUMMY!' 8 --AND ENTER THE NAME IN UPPER CASE LETTERS 9 --handle all other error conditions by print the oracle error message 10 declare 11 E_UPPER EXCEPTION; 12 e_check_violate exception; 13 pragma exception_init(e_check_violate,-2290); 14 v_country_id mycountries.country_id%type; 15 v_country_name mycountries.country_name%type; 16 v_region_id mycountries.region_id%type; 17 begin 18 v_country_id := &id; 19 v_country_name := &name; 20 v_region_id :=®ion; 21 IF COUNTRY_NAME!=UPPER(COUNTRY_NAME) THEN 22 RAISE E_UPPER; 23 END IF; 24 insert into mycountries 25 values(v_country_id, v_country_name,v_region_id); 26 exception 27 when dup_val_on_index then 28 dbms_output.put_line('pk violation! try again later'); 29 when e_check_violate then 30 dbms_output.put_line('country name cannot contain north.changed to no name'); 31 insert into mycountries 32 values(v_country_id,'NO NAME',v_region_id); 33 WHEN E_UPPER THEN 34 DBMS_OUTPUT.PUT_LINE('USE CAPSLOCK NEXT TIME YOU DUMMY!'); 35 insert into mycountries 36 values(v_country_id, UPPER(v_country_name),v_region_id); 37 when others then 38 dbms_output.put_line('oracle error number ='||SQLCODE); 39 dbms_output.put_line('oracle error message ='||SQLERRM); 40* end; 41 / Enter value for id: 'CC' old 18: v_country_id := &id; new 18: v_country_id := 'CC'; Enter value for name: 'ccccccc' old 19: v_country_name := &name; new 19: v_country_name := 'ccccccc'; Enter value for region: 3 old 20: v_region_id :=®ion; new 20: v_region_id :=3; IF COUNTRY_NAME!=UPPER(COUNTRY_NAME) THEN * ERROR at line 21: ORA-06550: line 21, column 6: PLS-00201: identifier 'COUNTRY_NAME' must be declared ORA-06550: line 21, column 3: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 --write an anonymous block to add a new country 2 --read values for all attributes from the keyboard 3 --handle one relevant predefined exception 4 --handle the unnamed exception with number -2290 by setting the country name to 'NO NAME' 5 --(handle the check violation: ora-02290:check constraint..) 6 --HANDLE THE CASE WHERE NAME IS WRITTEN IN LOWER CASE LETTERS AS A USER DEFINED EXCEPTION 7 --AND HANDLE IT BY PRINTING 'USE CAPSLOCK NEXT TIME YOU DUMMY!' 8 --AND ENTER THE NAME IN UPPER CASE LETTERS 9 --handle all other error conditions by print the oracle error message 10 declare 11 E_UPPER EXCEPTION; 12 e_check_violate exception; 13 pragma exception_init(e_check_violate,-2290); 14 v_country_id mycountries.country_id%type; 15 v_country_name mycountries.country_name%type; 16 v_region_id mycountries.region_id%type; 17 begin 18 v_country_id := &id; 19 v_country_name := &name; 20 v_region_id :=®ion; 21 IF v_COUNTRY_NAME!=UPPER(v_COUNTRY_NAME) THEN 22 RAISE E_UPPER; 23 END IF; 24 insert into mycountries 25 values(v_country_id, v_country_name,v_region_id); 26 exception 27 when dup_val_on_index then 28 dbms_output.put_line('pk violation! try again later'); 29 when e_check_violate then 30 dbms_output.put_line('country name cannot contain north.changed to no name'); 31 insert into mycountries 32 values(v_country_id,'NO NAME',v_region_id); 33 WHEN E_UPPER THEN 34 DBMS_OUTPUT.PUT_LINE('USE CAPSLOCK NEXT TIME YOU DUMMY!'); 35 insert into mycountries 36 values(v_country_id, UPPER(v_country_name),v_region_id); 37 when others then 38 dbms_output.put_line('oracle error number ='||SQLCODE); 39 dbms_output.put_line('oracle error message ='||SQLERRM); 40* end; SQL> / Enter value for id: 'CC' old 18: v_country_id := &id; new 18: v_country_id := 'CC'; Enter value for name: 'ccccccccccc' old 19: v_country_name := &name; new 19: v_country_name := 'ccccccccccc'; Enter value for region: 3 old 20: v_region_id :=®ion; new 20: v_region_id :=3; USE CAPSLOCK NEXT TIME YOU DUMMY! PL/SQL procedure successfully completed. SQL> select * from mycountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 NN NO NAME GG GIBRALTAR 1 QW NO NAME 2 AA AAAAAAA 1 CC CCCCCCCCCCC 3 30 rows selected. SQL> spool off