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;
0 Comments