Assignment No. 2: Error and Exception Handling

An error occurred during the execution (run time) of program is called exception in PL/SQL. PL/SQL provides the facility to catch errors by declaring conditions in exception block in the program and necessary action to be taken to rectify the error. Exception can be User defined (these are logical error defined by user) or System defined. 

SET A:

Consider the following entities and their relationships. 
 Wholesaler (w_no, w_name, address, city) 
Product (product_no, product_name, rate) 
Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute. Constraint: Primary key, rate should be > 0. 

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1. Write a function to accept quantity from user. Quantity must be within range 50-200. If user enters the quantity out of range then raise an user defined exception “quantity_out_of _range” otherwise enter the record in table.

ANS

--table Creation

CREATE TABLE wholesaler (
wNo INT PRIMARY KEY,
wName VARCHAR2(30),
address VARCHAR2(50),
city VARCHAR2(15)
);

INSERT INTO wholesaler VALUES (1, 'Guru', 'MG Road', 'Mumbai');
INSERT INTO wholesaler VALUES (2, 'Ram', 'KG Road', 'Pune');
INSERT INTO wholesaler VALUES (3, 'Rahul', 'ST Road', 'Shrirampur');

SELECT * FROM wholesaler;


CREATE TABLE product (
productNo INT PRIMARY KEY,
productName VARCHAR2(30),
rate INT, 
CHECK(rate > 0)
);

INSERT INTO product VALUES (101, 'Mouse', 200);
INSERT INTO product VALUES (102, 'Keyboard', 250);
INSERT INTO product VALUES (103, 'Monitor', 1500);

SELECT * FROM product;

CREATE TABLE wp (
wNO INT,
FOREIGN KEY (wNo) REFERENCES wholesaler (wNo),
productNo INT,
FOREIGN KEY (productNo) REFERENCES product (productNo),
quantity INT
);

PL-SQL BLOCK

DECLARE
v_qua wp.quantity%TYPE:=:Quantity;
v_wno wholesaler.wNo%TYPE:=:wNo;
v_pno product.productNo%TYPE:=:pNo;

Quantity_Out_Of_Range EXCEPTION;

BEGIN


  IF v_qua>50 AND v_qua<200 THEN
    INSERT INTO wp VALUES (v_wno , v_pno , v_qua);
    dbms_output.put_line('Successful');
  ELSE
    RAISE Quantity_Out_Of_Range;
  END IF;

 EXCEPTION 
  WHEN Quantity_Out_Of_Range THEN
   dbms_output.put_line('Quantity Out of Range Please should be between 50 to 200');
 WHEN Too_Many_Rows THEN
 dbms_output.put_line('Too many Rows Fetch');
END;
/

------------------------------------------------------------------------------------------------------------------

2. Write a PL/SQL block which accept rate from user. If user enters rate less than or equal to zero then raise an user defined exception “Invalid_Rate_Value” otherwise display message “Correct Input”

ANS

DECLARE
 rate product.rate%TYPE:=:RATE;
 Invalid_Rate_Value EXCEPTION;

BEGIN
 IF rate<=0 THEN
 RAISE Invalid_Rate_Value;
ELSE
 dbms_output.put_line('Correct Input');
END IF;

EXCEPTION 
 WHEN Invalid_Rate_Value THEN
 dbms_output.put_line('Invalid Rate please Check');

END;
/
--------------------------------------------------------------------------------------------------------------

3. Write a function to accept product name as parameter. If entered product name is not valid then raise an user defined exception”Invalid_Product_Name” otherwise display product details of specified product.

ANS

DECLARE
 v_pname product.productName%TYPE:=:PRODUCTNAME;
 t_pname product.productName%TYPE;
 p_record product%ROWTYPE;
 Invalid_Product_Name EXCEPTION;
BEGIN
 SELECT productName INTO t_pname
 FROM product 
 WHERE productName = v_pname;

SELECT * INTO p_record FROM product WHERE productName = v_pname;

IF v_pname=t_pname THEN
 dbms_output.put_line('Product No. = '|| p_record.productNo);
 dbms_output.put_line('Product Name = '|| p_record.productName);
 dbms_output.put_line('Product Rate = '|| p_record.rate);
ELSE
 RAISE Invalid_Product_Name;
END IF;

EXCEPTION
 
 WHEN Invalid_Product_Name THEN
 dbms_output.put_line('Invalid Product Name');
END;
/

select * from product;

-----------------------------------------------------------------------------------------------------------

SET B: 

Consider the following entities and their relationships. 
 Student (rollno, sname, class, timetable, mobileno)
 Lab (LabNo, LabName, capacity, equipment) 
Relation between Student and Lab is Many to One. 
Constraint: Primary Key, capacity should not be null. 

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1) Write a function to accept lab number from user as parameter. ” if user enters invalid lab number then raise an user defined exception “Invalid_Lab_No” otherwise display the student details of the same lab. 

ANS

-- Create Function

create or replace function stud_lab (p_lno in number)
return varchar2
is
cursor stud_del is 
select rollno, sname, class, mobile_no 
from students, lab 
where students.lno = p_lno and 
students.lno = lab.lno;

v_labno lab.lno%type;
v_count number:=0;

v_output varchar(500);

Begin
 select lab.lno into v_labno
 from lab 
 where lab.lno = p_lno;

 if v_labno is null then
 raise_application_error(-20001, 'Invalid Lab Number');
 end if;

 for rec in stud_del loop
  v_count := v_count + 1;
  v_output := v_output ||
  '. Roll No: ' || rec.rollno
  || ', Name: ' || rec.sname
  || ', Class: ' || rec.class
  || ', Mobile No:' || rec.mobile_no;
 end loop;

if v_count = 0 then
 v_output := v_output || 'No student found for this lab';
end if;

return v_output;
end stud_lab;

-----------------------------
--Execution Section 

declare

v_labno number :=:enter_labNo;
v_output varchar2(500);

Begin
v_output := stud_lab(v_labno);
dbms_output.put_line(v_output);

end;

----------------------------------

--Table Creation

create table lab (
lno int primary key,
lname varchar(50),
capacity int,
equipment varchar(50)
);

insert into lab values (1,'BCA', 80, 'computer');
insert into lab values (2,'Commerce', 40, 'computer');
insert into lab values (3,'M.Com', 50, 'computer');

select * from lab;








create table students (
rollno int primary key,
sname varchar(50),
class varchar(20),
timetable varchar2(50),
mobile_no int,
lno int,
foreign key (lno) references lab (lno)
);

insert into students values (101, 'Nick', 'FY', '11 To 2 PM', 9922586932, 3);
insert into students values (102, 'Sidd', 'FY', '11 To 2 PM', 9922236932, 3);

insert into students values (103, 'Gopps', 'FY', '11 To 2 PM', 9922210032, 1);
insert into students values (104, 'Rutu', 'FY', '11 To 2 PM', 9922215532, 2);
insert into students values (105, 'Rutuja', 'FY', '11 To 2 PM', 9925615532, 1);

select * from students;








---------------------------------------------------------------------------------------------------------------------

2. Write a PL/SQL block which accept a mobile number from user. If mobileno less than or more than 10 digits then raise an user defined exception “Invalid_Mobile No” otherwise display the “Correct input…! “. 

ANS

Declare
v_mno students.mobile_no%type:=:mobile_no;
Invalid_Mobile_No exception;

Begin
 if length(v_mno) <> 10 then
  raise Invalid_Mobile_No;
 else
  dbms_output.put_line('Correct Input');
 end if;
exception
 when Invalid_Mobile_No then
  dbms_output.put_line('Invalid Mobile No ');
end;
/

--------------------------------------------------------------------------------------------------------------

3) Write a PL/SQL block which accepts lab detail from user. If capacity is more than 40 then raise an user defined exception “Invalid_Capacity_Range” otherwise insert the record in the table.

ANS

declare
v_labno lab.lno%type:=:lno;
v_lname lab.lname%type:=:lname;
v_capacity lab.capacity%type:=:capacity;
v_equ lab.equipment%type:=:equipment;
Invalid_Capacity_Range exception;

Begin
 if v_capacity > 40 then
  raise Invalid_Capacity_Range;
 else
  insert into lab values (v_labno, v_lname, v_capacity, v_equ);
 end if;
exception 
 when Invalid_Capacity_Range then
 dbms_output.put_line('Invalid Capacity Range');
end;
/

SELECT * FROM LAB;