SQL> set serveroutput on SQL> desc myregions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> drop table myregions; Table dropped. SQL> create table myregions as 2 select * from regions; Table created. SQL> alter table myregions 2 add primary key (region_id); Table altered. SQL> alter table myregions 2 add check (region_name not like %NORTH%); add check (region_name not like %NORTH%) * ERROR at line 2: ORA-00911: invalid character SQL> ed Wrote file afiedt.buf 1 alter table myregions 2* add check (region_name not like '%NORTH%') SQL> / Table altered. SQL> begin 2 insert into myregions 3 values (1,'Magusa'); 4 end; 5 / begin * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0032339) violated ORA-06512: at line 2 SQL> ed Wrote file afiedt.buf 1 begin 2 insert into myregions 3 values (1,'Magusa'); 4* end; SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> ed Wrote file afiedt.buf 1 begin 2 insert into myregions 3 values (5,'Magusa'); 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 5 Magusa SQL> ed Wrote file afiedt.buf 1 begin 2 insert into myregions 3 values (5,'Magusa'); 4 exception 5 when others then 6 dbms_output.put_line('there is a violation!'); 7* end; 8 / there is a violation! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 insert into myregions 3 values (5,'Magusa'); 4 exception 5 when dup_val_on_index then 6 dbms_output.put_line('pk violation!'); 7 when others then 8 dbms_output.put_line('there is a violation!'); 9* end; 10 / pk violation! PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 insert into myregions 3 values (®_id,'®_name'); 4 exception 5 when dup_val_on_index then 6 dbms_output.put_line('pk violation!'); 7 when others then 8 dbms_output.put_line('there is a violation!'); 9* end; SQL> / Enter value for reg_id: 6 Enter value for reg_name: NORTH CYP old 3: values (®_id,'®_name'); new 3: values (6,'NORTH CYP'); there is a violation! PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 v_reg_id myregions.regions_id%type; 3 v_reg_name myregions.region_name%type; 4 begin 5 v_reg_id:=&Enter_reg_id; 6 v_reg_name:='&Enter_reg_name'; 7 insert into myregions 8 values (v_reg_id,v_reg_name); 9 exception 10 when dup_val_on_index then 11 dbms_output.put_line('pk violation!'); 12 when others then 13 dbms_output.put_line('there is a violation! CALLED' || SQLERRM); 14* end; 15 / Enter value for enter_reg_id: 6 old 5: v_reg_id:=&Enter_reg_id; new 5: v_reg_id:=6; Enter value for enter_reg_name: lekfe old 6: v_reg_name:='&Enter_reg_name'; new 6: v_reg_name:='lekfe'; v_reg_id myregions.regions_id%type; * ERROR at line 2: ORA-06550: line 2, column 20: PLS-00302: component 'REGIONS_ID' must be declared ORA-06550: line 2, column 10: PL/SQL: Item ignored ORA-06550: line 5, column 1: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 5, column 1: PL/SQL: Statement ignored ORA-06550: line 8, column 9: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 8, column 9: PL/SQL: ORA-00904: "V_REG_ID": invalid identifier ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored SQL> ed Wrote file afiedt.buf 1 declare 2 v_reg_id myregions.region_id%type; 3 v_reg_name myregions.region_name%type; 4 begin 5 v_reg_id:=&Enter_reg_id; 6 v_reg_name:='&Enter_reg_name'; 7 insert into myregions 8 values (v_reg_id,v_reg_name); 9 exception 10 when dup_val_on_index then 11 dbms_output.put_line('pk violation!'); 12 when others then 13 dbms_output.put_line('there is a violation! CALLED' || SQLERRM); 14* end; SQL> / Enter value for enter_reg_id: 6 old 5: v_reg_id:=&Enter_reg_id; new 5: v_reg_id:=6; Enter value for enter_reg_name: lefke old 6: v_reg_name:='&Enter_reg_name'; new 6: v_reg_name:='lefke'; PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_reg_id myregions.region_id%type; 3 v_reg_name myregions.region_name%type; 4 begin 5 v_reg_id:=&Enter_reg_id; 6 v_reg_name:='&Enter_reg_name'; 7 insert into myregions 8 values (v_reg_id,v_reg_name); 9 exception 10 when dup_val_on_index then 11 dbms_output.put_line('pk violation!'); 12 when others then 13 dbms_output.put_line('there is a violation! message' || SQLERRM); 14 dbms_output.put_line( 'VIOLATION CODE IS: ' || SQLCODE); 15* end; SQL> / Enter value for enter_reg_id: 7 old 5: v_reg_id:=&Enter_reg_id; new 5: v_reg_id:=7; Enter value for enter_reg_name: NORTH old 6: v_reg_name:='&Enter_reg_name'; new 6: v_reg_name:='NORTH'; there is a violation! messageORA-02290: check constraint (USER15.SYS_C0032341) violated VIOLATION CODE IS: -2290 PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 my_excpt exception; 3 pragma exception_init(my_excpt, -2290); 4 v_reg_id myregions.region_id%type; 5 v_reg_name myregions.region_name%type; 6 begin 7 v_reg_id:=&Enter_reg_id; 8 v_reg_name:='&Enter_reg_name'; 9 insert into myregions 10 values (v_reg_id,v_reg_name); 11 exception 12 when dup_val_on_index then 13 dbms_output.put_line('pk violation!'); 14 when my_excpt then 15 dbms_output.put_line('Check constraint violated'); 16 when others then 17 dbms_output.put_line('there is a violation! message' || SQLERRM); 18 dbms_output.put_line( 'VIOLATION CODE IS: ' || SQLCODE); 19* end; 20 / Enter value for enter_reg_id: 7 old 7: v_reg_id:=&Enter_reg_id; new 7: v_reg_id:=7; Enter value for enter_reg_name: NORTH old 8: v_reg_name:='&Enter_reg_name'; new 8: v_reg_name:='NORTH'; Check constraint violated PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 my_excpt exception; 3 pragma exception_init(my_excpt, -2290); 4 v_reg_id myregions.region_id%type; 5 v_reg_name myregions.region_name%type; 6 begin 7 v_reg_id:=&Enter_reg_id; 8 v_reg_name:='&Enter_reg_name'; 9 insert into myregions 10 values (v_reg_id,v_reg_name); 11 exception 12 when dup_val_on_index then 13 dbms_output.put_line('pk violation!'); 14 when my_excpt then 15 dbms_output.put_line('Check constraint violated'); 16 when others then 17 dbms_output.put_line('there is a violation! message' || SQLERRM); 18 dbms_output.put_line( 'VIOLATION CODE IS: ' || SQLCODE); 19* end; 20 / Enter value for enter_reg_id: 7 old 7: v_reg_id:=&Enter_reg_id; new 7: v_reg_id:=7; Enter value for enter_reg_name: north old 8: v_reg_name:='&Enter_reg_name'; new 8: v_reg_name:='north'; PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 my_excpt exception; 3 pragma exception_init(my_excpt, -2290); 4 v_reg_id myregions.region_id%type; 5 v_reg_name myregions.region_name%type; 6 my_upper exception; 7 begin 8 v_reg_id:=&Enter_reg_id; 9 v_reg_name:='&Enter_reg_name'; 10 if v_reg_name !=upper(v_reg_name) then 11 raise my_upper; 12 end if; 13 insert into myregions 14 values (v_reg_id,v_reg_name); 15 exception 16 when my_upper then 17 dbms_output.put_line('Reg name should be in upper case'); 18 when dup_val_on_index then 19 dbms_output.put_line('pk violation!'); 20 when my_excpt then 21 dbms_output.put_line('Check constraint violated'); 22 when others then 23 dbms_output.put_line('there is a violation! message' || SQLERRM); 24 dbms_output.put_line( 'VIOLATION CODE IS: ' || SQLCODE); 25* end; 26 / Enter value for enter_reg_id: 8 old 8: v_reg_id:=&Enter_reg_id; new 8: v_reg_id:=8; Enter value for enter_reg_name: north old 9: v_reg_name:='&Enter_reg_name'; new 9: v_reg_name:='north'; Reg name should be in upper case PL/SQL procedure successfully completed. SQL> / Enter value for enter_reg_id: 8 old 8: v_reg_id:=&Enter_reg_id; new 8: v_reg_id:=8; Enter value for enter_reg_name: NORTH old 9: v_reg_name:='&Enter_reg_name'; new 9: v_reg_name:='NORTH'; Check constraint violated PL/SQL procedure successfully completed. SQL> ED Wrote file afiedt.buf 1 declare 2 my_excpt exception; 3 pragma exception_init(my_excpt, -2290); 4 v_reg_id myregions.region_id%type; 5 v_reg_name myregions.region_name%type; 6 my_upper exception; 7 begin 8 v_reg_id:=&Enter_reg_id; 9 v_reg_name:='&Enter_reg_name'; 10 if v_reg_name !=upper(v_reg_name) then 11 raise my_upper; 12 end if; 13 insert into myregions 14 values (v_reg_id,v_reg_name); 15 exception 16 when my_upper then 17 dbms_output.put_line('Reg name should be in upper case'); 18 insert into myregions 19 values (v_reg_id,upper(v_reg_name)); 20 when dup_val_on_index then 21 dbms_output.put_line('pk violation!'); 22 when my_excpt then 23 dbms_output.put_line('Check constraint violated'); 24 insert into myregions 25 values (v_reg_id,'NO NAME'); 26 when others then 27 dbms_output.put_line('there is a violation! message' || SQLERRM); 28 dbms_output.put_line( 'VIOLATION CODE IS: ' || SQLCODE); 29* end; 30 / Enter value for enter_reg_id: 10 old 8: v_reg_id:=&Enter_reg_id; new 8: v_reg_id:=10; Enter value for enter_reg_name: north old 9: v_reg_name:='&Enter_reg_name'; new 9: v_reg_name:='north'; Reg name should be in upper case declare * ERROR at line 1: ORA-02290: check constraint (USER15.SYS_C0032341) violated ORA-06512: at line 18 ORA-06510: PL/SQL: unhandled user-defined exception SQL> / Enter value for enter_reg_id: 10 old 8: v_reg_id:=&Enter_reg_id; new 8: v_reg_id:=10; Enter value for enter_reg_name: NORTH old 9: v_reg_name:='&Enter_reg_name'; new 9: v_reg_name:='NORTH'; Check constraint violated PL/SQL procedure successfully completed. SQL> / Enter value for enter_reg_id: 11 old 8: v_reg_id:=&Enter_reg_id; new 8: v_reg_id:=11; Enter value for enter_reg_name: cyprus old 9: v_reg_name:='&Enter_reg_name'; new 9: v_reg_name:='cyprus'; Reg name should be in upper case PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 my_excpt exception; 3 pragma exception_init(my_excpt, -2290); 4 v_reg_id myregions.region_id%type; 5 v_reg_name myregions.region_name%type; 6 my_upper exception; 7 begin 8 v_reg_id:=&Enter_reg_id; 9 v_reg_name:='&Enter_reg_name'; 10 if v_reg_name !=upper(v_reg_name) then 11 raise my_upper; 12 end if; 13 insert into myregions 14 values (v_reg_id,v_reg_name); 15 exception 16 when my_upper then 17 dbms_output.put_line('Reg name should be in upper case'); 18 insert into myregions 19 values (v_reg_id,upper(v_reg_name)); 20 when dup_val_on_index then 21 dbms_output.put_line('pk violation!'); 22 when my_excpt then 23 dbms_output.put_line('Check constraint violated'); 24 insert into myregions 25 values (v_reg_id,'NO NAME'); 26 when others then 27 dbms_output.put_line('there is a violation! message' || SQLERRM); 28 dbms_output.put_line( 'VIOLATION CODE IS: ' || SQLCODE); 29* end; SQL> select * from myregions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa 5 Magusa 6 lefke 7 north 10 NO NAME 11 CYPRUS 9 rows selected. SQL> ed Wrote file afiedt.buf 1 declare 2 cursor my_cur is 3 select last_name 4 from employees 5 where salary>10000; 6 begin 7 for r in my_cur 8 loop 9 dbms_output.put_line(r.last_name); 10 end loop; 11* end; SQL> / King Kochhar De Haan Greenberg Raphaely Russell Partners Errazuriz Cambrault Zlotkey Vishney Ozer Abel Hartstein Higgins PL/SQL procedure successfully completed. SQL> spool off