Exception Handling

Exception Handling

declare
A NUMBER(2):=0;
B NUMBER(2):=0;
C NUMBER(2);
begin
C:=A/B;
DBMS_OUTPUT.PUT_LINE('THE DIVISOR YOU GAVE IS ZERO');
DBMS_OUTPUT.PUT_LINE(C);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('THERE IS AN ERROR');
commit;
end;
/
-----------------------------------------------
CREATE TABLE STUD(NAME VARCHAR2(30),ROLL NUMBER(5),DOB DATE,PRIMARY KEY(ROLL));
INSERT INTO STUD VALUES('ARKA',12,'16-MAY-1985');
INSERT INTO STUD VALUES('INDIA',1,'15-AUG-1947');
INSERT INTO STUD VALUES('DIBYENDU',2,'1-DEC-2007');

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

/*NO_DATA_FOUND EXCEPTION*/

DECLARE
A VARCHAR2(10);
BEGIN
SELECT NAME INTO A FROM STUD WHERE NAME='STG';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SORY NO DATA IS FOUND');
END;
/
--------------------------------------------------

/*USER DEFINE EXCEPTION(USING "RAISE" STATEMENT)*/
DECLARE
INVALID EXCEPTION;
a number:=&x;
BEGIN
if a>20 THEN
insert into stud(roll) values(a);
else
RAISE INVALID;
END IF;
COMMIT;
EXCEPTION
WHEN INVALID THEN
DBMS_OUTPUT.PUT_LINE('SORY INVALID NUMBER');
END;
/

--------------------------------------------------
integrity constant violation (using PRAGMA)

define a=10
DECLARE
EX EXCEPTION;
PRAGMA EXCEPTION_INIT(EX,-2292);
BEGIN
DELETE FROM STUD
WHERE ROLL=&A;
COMMIT;
EXCEPTION
WHEN EX THEN
DBMS_OUTPUT.PUT_LINE('CANNOT REMOVE NAME STUDENT EXST');
END;
/
--------------------------------------------------

/*EXCEPTION DECLARATION USING
RAISE_APPLICATION_ERROR----RANGE(-20000 TO -20999)*/

DECLARE
a number:=&x;
BEGIN
if a>20 THEN
insert into stud(roll) values(a);
else
RAISE_APPLICATION_ERROR(-20005,'Number greater then 20');
END IF;
COMMIT;
END;
/
--------------------------------------------------