SQL> set serveroutput on SQL> desc countries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> select * from countries; 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 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- 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 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> insert into countries 2 values('CY','Cyprus', 1); insert into countries * ERROR at line 1: ORA-01031: insufficient privileges SQL> --countries table (in fact all tables you used in 212) belong to the user HR SQL> --we only have select prievelege on the HR's tables SQL> desc newcountries ERROR: ORA-04043: object newcountries does not exist SQL> create table newcountries as 2 select * from countries; Table created. SQL> desc newcountries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> select * from newcountries; 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 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- 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 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> --in effect we copies countries table into out account under the name newcountries SQL> insert into newcountries 2 values ('CY','Cyprus',1); 1 row created. SQL> select * from newcountries; 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 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- 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 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 CY Cyprus 1 26 rows selected. SQL> cmdsep OFF pagesize 14 pagesize 14 linesize 80 set pagesize 100 SQL> colsep " " pagesize 100 linesize 80 set pagesize 100 SQL> set pagesize 100 SQL> concat "." (hex 2e) pagesize 100 select * from newcountries; 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 CY Cyprus 1 26 rows selected. SQL> rollback; Rollback complete. SQL> select * from newcountries; 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> desc newcountries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> --create table as command unfortuately does not copy pk fk constrażnts SQL> alter table newcountries 2 add constraint newcountries_pk primary key(country_id); Table altered. SQL> ed Wrote file afiedt.buf 1 alter table newcountries 2* add constraint newcountries_pk primary key(country_id) SQL> insert into newcountries 2 values ('CY','Cyprus',1); 1 row created. SQL> ed Wrote file afiedt.buf 1 begin 2 insert into newcountries 3 values ('CY','Cyprus',1); 4* end; 5 / begin * ERROR at line 1: ORA-00001: unique constraint (USER15.NEWCOUNTRIES_PK) violated ORA-06512: at line 2 SQL> ed Wrote file afiedt.buf 1 begin 2 insert into newcountries 3 values ('TR','Turkey',1); 4* end; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 insert into newcountries 3 values ('TR','Turkey',1); 4* end; SQL> update newcountries 2 set country_name = 'Lala Land'; 27 rows updated. SQL> select * from newcountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Lala Land 2 AU Lala Land 3 BE Lala Land 1 BR Lala Land 2 CA Lala Land 2 CH Lala Land 1 CN Lala Land 3 DE Lala Land 1 DK Lala Land 1 EG Lala Land 4 FR Lala Land 1 IL Lala Land 4 IN Lala Land 3 IT Lala Land 1 JP Lala Land 3 KW Lala Land 4 ML Lala Land 3 MX Lala Land 2 NG Lala Land 4 NL Lala Land 1 SG Lala Land 3 UK Lala Land 1 US Lala Land 2 ZM Lala Land 4 ZW Lala Land 4 CY Lala Land 1 TR Lala Land 1 27 rows selected. SQL> rollback; Rollback complete. SQL> select * from newcountries; 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> begin 2 insert into newcountries 3 values ('TR','Turkey',1); 4 . SQL> ed Wrote file afiedt.buf 1 begin 2 update newcountries 3 set country_name = 'Lala Land'; 4* end; 5 / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 update newcountries 3 set country_name = 'Lala Land'; 4 dbms_output.put_line(SQL%ROWCOUNT); 5* end; SQL> / 25 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 update newcountries 3 set country_name = 'Lala Land'; 4 dbms_output.put_line(SQL%ROWCOUNT||' countries were renamed to lala land'); 5* end; SQL> / 25 countries were renamed to lala land PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 update newcountries 3 set country_name = 'Lala Land'; 4 dbms_output.put_line(SQL%ROWCOUNT||' countries were renamed to lala land'); 5 dbms_output.put_line('++++'||SQL%FOUND||'*****'); 6* end; 7 / dbms_output.put_line('++++'||SQL%FOUND||'*****'); * ERROR at line 5: ORA-06550: line 5, column 22: PLS-00306: wrong number or types of arguments in call to '||' ORA-06550: line 5, column 1: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 begin 2 update newcountries 3 set country_name = 'Lala Land'; 4 dbms_output.put_line(SQL%ROWCOUNT||' countries were renamed to lala land'); 5 dbms_output.put_line('++++'||9999||'*****'); 6* end; SQL> / 25 countries were renamed to lala land ++++9999***** PL/SQL procedure successfully completed. SQL> --we cannot print the boolean values %FOUND %NOTFOUND AND %ISOPEN ON SCREEN SQL> --WE CAN ONLY USE THEM AS CONDITIONS SQL> ED Wrote file afiedt.buf 1 begin 2 update newcountries 3 set country_name = 'Lala Land'; 4 dbms_output.put_line(SQL%ROWCOUNT||' countries were renamed to lala land'); 5 IF sql%found theN 6 dbms_output.put_line('found SOMETHING'); 7 ELSE 8 dbms_output.put_line('Did not find anything'); 9 end if; 10* end; 11 / 25 countries were renamed to lala land found SOMETHING PL/SQL procedure successfully completed. SQL> select country_name 2 from newcountries 3 where country_id='AR'; COUNTRY_NAME ---------------------------------------- Lala Land SQL> ed Wrote file afiedt.buf 1 begin 2 select country_name 3 from newcountries 4 where country_id='AR'; 5* end; 6 / select country_name * ERROR at line 2: ORA-06550: line 2, column 1: PLS-00428: an INTO clause is expected in this SELECT statement SQL> ed Wrote file afiedt.buf 1 declare 2 v varchar2(15); 3 begin 4 select country_name into v 5 from newcountries 6 where country_id='AR'; 7 dbms_output.put_line(v); 8* end; 9 / Lala Land PL/SQL procedure successfully completed. SQL> rollback; Rollback complete. SQL> declare 2 v varchar2(15); 3 begin 4 select country_name into v 5 from newcountries 6 where country_id='AR'; 7 dbms_output.put_line(v); 8 end; 9 / Argentina PL/SQL procedure successfully completed. SQL> select * from newcountries; 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> declare 2 v varchar2(15); 3 begin 4 select country_name into v 5 from newcountries 6 where country_id='AR'; 7 dbms_output.put_line(v); 8 end; 9 / Argentina PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v varchar2(15); 3 begin 4 select country_name into v 5 from newcountries 6 where country_id='&countryid'; 7 dbms_output.put_line(v); 8* end; SQL> / Enter value for countryid: NL old 6: where country_id='&countryid'; new 6: where country_id='NL'; Netherlands PL/SQL procedure successfully completed. SQL> / Enter value for countryid: ZW old 6: where country_id='&countryid'; new 6: where country_id='ZW'; Zimbabwe PL/SQL procedure successfully completed. SQL> / Enter value for countryid: CY old 6: where country_id='&countryid'; new 6: where country_id='CY'; declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 SQL> --this program crashed SQL> --if it was called by other programs, it could cause all of them to crash as well SQL> ed Wrote file afiedt.buf 1 declare 2 v varchar2(15); 3 begin 4 select country_name into v 5 from newcountries 6 where country_id='&countryid'; 7 dbms_output.put_line(v); 8 exception 9 when no_data_found then 10 dbms_output.put_line('no such country'); 11* end; 12 / Enter value for countryid: NL old 6: where country_id='&countryid'; new 6: where country_id='NL'; Netherlands PL/SQL procedure successfully completed. SQL> / Enter value for countryid: CY old 6: where country_id='&countryid'; new 6: where country_id='CY'; no such country PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v varchar2(15); 3 begin 4 select country_name into v 5 from newcountries; 6 dbms_output.put_line(v); 7 exception 8 when no_data_found then 9 dbms_output.put_line('no such country'); 10* end; 11 / 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 varchar2(15); 3 begin 4 select country_name into v 5 from newcountries; 6 dbms_output.put_line(v); 7 exception 8 when no_data_found then 9 dbms_output.put_line('no such country'); 10 when too_many_rows then 11 dbms_output.put_line('you must use explicit cursor!!!!'); 12* end; 13 / you must use explicit cursor!!!! PL/SQL procedure successfully completed. SQL> spool off