SQL> --USING DMAL COMMANDS IN PL/SQL BLOCKS
SQL> DROP TABLE BUDDY;

Table dropped.

SQL> DESC REGIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REGION_ID                                 NOT NULL NUMBER
 REGION_NAME                                        VARCHAR2(25)

SQL> SELECT * FROM REGIONS;

 REGION_ID REGION_NAME                                                          
---------- -------------------------                                            
         1 Europe                                                               
         2 Americas                                                             
         3 Asia                                                                 
         4 Middle East and Africa                                               

SQL> INSERT INTO REGIONS(REGION_ID,REGION_NAME)
  2  VALUES(11,'wwww');
INSERT INTO REGIONS(REGION_ID,REGION_NAME)
            *
ERROR at line 1:
ORA-01031: insufficient privileges 


SQL> CREATE TABLE BUDDY
  2  ( ID NUMBER PRIMARY KEY,
  3    NAME VARCHAR2(10) CHECK(NAME NOT LIKE 'N%'));

Table created.

SQL> INSERT INTO BUDDY(ID,NAME)
  2  VALUES(1,'aYSE');

1 row created.

SQL> INSERT INTO BUDDY(ID,NAME)
  2  VALUES(2,'NAZIFE');
INSERT INTO BUDDY(ID,NAME)
*
ERROR at line 1:
ORA-02290: check constraint (USER15.SYS_C0017249) violated 


SQL> INSERT INTO BUDDY(ID,NAME)
  2  VALUES(2,'nAZIFE');

1 row created.

SQL> SELECT * FROM BUDDY;

        ID NAME                                                                 
---------- ----------                                                           
         1 aYSE                                                                 
         2 nAZIFE                                                               

SQL> 
SQL>  INSERT INTO BUDDY(ID,NAME)
  2   VALUES(2,'SSSS');
 INSERT INTO BUDDY(ID,NAME)
*
ERROR at line 1:
ORA-00001: unique constraint (USER15.SYS_C0017250) violated 


SQL> --IN THE ABOVE EXAMPLES WE FIRST VIOLATED THE CHECK CONSTRAINT THAT RAISED THE EXCEPTION ORA-02290
SQL> --IN THE SECOND EXAMPLE WE VIOLATED THE UNIQUENES OF THE PK AND THE EXCEPTION RAISED IS
SQL> --ORA-1
SQL> ED
Wrote file afiedt.buf

  1  DECLARE
  2    X NUMBER :=3;
  3  BEGIN
  4   INSERT INTO BUDDY(ID,NAME)
  5   VALUES(X,'ALI');
  6   X := X+1;
  7   INSERT INTO BUDDY(ID,NAME)
  8   VALUES(X,'VELI');
  9   X := X+1;
 10   INSERT INTO BUDDY(ID,NAME)
 11   VALUES(X,'OMAR');
 12    UPDATE BUDDY
 13    SET NAME=LOWER(NAME)
 14    WHERE ID <=2; 
 15   DELETE FROM BUDDY
 16   WHERE NAME LIKE '%V';
 17* END;
 18  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM BUDDY;

        ID NAME                                                                 
---------- ----------                                                           
         1 ayse                                                                 
         2 nazife                                                               
         3 ALI                                                                  
         4 VELI                                                                 
         5 OMAR                                                                 

SQL> ED
Wrote file afiedt.buf

  1  BEGIN
  2   INSERT INTO BUDDY
  3   VALUES(5,'WWW');
  4* END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (USER15.SYS_C0017250) violated 
ORA-06512: at line 2 


SQL> --WE MUST HANDLE EXCEPTIONS!!!!!!!!!!!!!!!!!!!
SQL> SELECT NAME
  2  FROM BUDDY
  3  WHERE ID=1;

NAME                                                                            
----------                                                                      
ayse                                                                            

SQL> ED
Wrote file afiedt.buf

  1  DECLARE
  2   V_NAME VARCHAR2(10); 
  3  BEGIN
  4  SELECT NAME INTO V_NAME
  5  FROM BUDDY
  6  WHERE ID=1;
  7  DBMS_OUTPUT.PUT_LINE(V_NAME);
  8* END;
  9  /
ayse                                                                            

PL/SQL procedure successfully completed.

SQL> ED
Wrote file afiedt.buf

  1  DECLARE
  2   V_NAME VARCHAR2(10); 
  3   V_ID NUMBER;
  4  BEGIN
  5  SELECT ID, NAME INTO V_ID,V_NAME
  6  FROM BUDDY
  7  WHERE ID=1;
  8  DBMS_OUTPUT.PUT_LINE(V_ID||' '||V_NAME);
  9* END;
SQL> /
1 ayse                                                                          

PL/SQL procedure successfully completed.

SQL> ED
Wrote file afiedt.buf

  1  DECLARE
  2   V_NAME VARCHAR2(10); 
  3   V_ID NUMBER;
  4  BEGIN
  5  SELECT NAME, ID INTO V_ID,V_NAME
  6  FROM BUDDY
  7  WHERE ID=1;
  8  DBMS_OUTPUT.PUT_LINE(V_ID||' '||V_NAME);
  9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error 
ORA-06512: at line 5 


SQL> --THERE MUST BE A ONE-TO-ONE CORRESPONDENCE IN THE LISTS ON THE LEFT AND RIGHT OF THE ưnto
SQL> --THE DATA TYPES MUST BE UNION COMPATIBLE
SQL> --
SQL> 
SQL> 
SQL> 
SQL> --SELECT INTO WORKS ONLY WHEN IT RETURNS EXACTLY ONE ROW
SQL> ED
Wrote file afiedt.buf

  1  DECLARE
  2   V_NAME VARCHAR2(10); 
  3   V_ID NUMBER;
  4  BEGIN
  5  SELECT ID, NAME INTO V_ID,V_NAME
  6  FROM BUDDY;
  7  DBMS_OUTPUT.PUT_LINE(V_ID||' '||V_NAME);
  8* END;
  9  .
SQL> SELECT ID, NAME
  2  FROM BUDDY;

        ID NAME                                                                 
---------- ----------                                                           
         1 ayse                                                                 
         2 nazife                                                               
         3 ALI                                                                  
         4 VELI                                                                 
         5 OMAR                                                                 

SQL> DECLARE
  2   V_NAME VARCHAR2(10);
  3   V_ID NUMBER;
  4  BEGIN
  5  SELECT ID, NAME INTO V_ID,V_NAME
  6  FROM BUDDY;
  7  DBMS_OUTPUT.PUT_LINE(V_ID||' '||V_NAME);
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 5 


SQL> --IN CASES WHERE THE SELECT STATEMENT MAY RETURN MORE THAN ONE ROW, WE MUST USE CURSORS (NEXT WEEK)
SQL> ED
Wrote file afiedt.buf

  1  DECLARE
  2   V_NAME VARCHAR2(10);
  3   V_ID NUMBER;
  4  BEGIN
  5  SELECT ID, NAME INTO V_ID,V_NAME
  6  FROM BUDDY
  7  WHERE ID=6;
  8  DBMS_OUTPUT.PUT_LINE(V_ID||' '||V_NAME);
  9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found 
ORA-06512: at line 5 


SQL> --SELECT INTO FAILS IF NO ROWS ARE RETURNED, THE ORA-1403 EXCEPTION IS RAISED
SQL> SPOOL OFF