ERROR Trapping Functions:
*************************
-->There are two error trapping functions supported by oracle.
a)SQLCODE
b)SQLERRM
SQLCODE:
********
-->It returns error number associated with the most recently raised exception.
SQLERRM:
*********
-->It returns the error number with error message
EX:
***
declare
a number(2);
b number(2);
c number(2);
lv_error_message varchar2(4000) :=null;
lv_error_code number :=null;
begin
a :=40;
b :=60;
c :=a+b;
dbms_output.put_line(c);
exception
when others then
--dbms_output.put_line(sqlcode);
--dbms_output.put_line(sqlerrm);
lv_error_message :=lv_error_message ||sqlerrm;
lv_error_code :=lv_error_code||sqlcode;
dbms_output.put_line('error code is :'||lv_error_code);
dbms_output.put_line('error message is :'||lv_error_message);
end;
/
DECLARE
X NUMBER(10);
Y NUMBER(20);
Z NUMBER(10);
BEGIN
X:=:X;
Y:=:Y;
Z:=X/Y;
DBMS_OUTPUT.PUT_LINE(Z);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
DECLARE
V_SAL NUMBER;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO=7369;
DBMS_OUTPUT.PUT_LINE(v_sal);
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(v_sal||','||SQLCODE||','||SQLERRM);
END;
/
User Defined Exceptions:
************************
-->when predefined exceptions not meeting our requirements then we define our own exceptions.
-->We can also create our own exception names and also raise whenever it is necessary,these types of exceptions are called user defined exceptions.
-->These exceptions are divided into 3 steps
a)Declare Exception --declare section
b)Raise Exception --executable section or exception
c)Handle Exception --executable section
Declare Exception:
******************
-->In declare section of the pl/sql program we are defining our own exception name using exception type.
syntax:
******
userdefinedexception_name exception;
ex:
***
DECLARE
A EXCEPTION;
Raise Exception:
****************
-->whenever it is required raise user defined exception either in executable section or exception section.In this case we are using raise keyword.
syntax:
*******
raise userdefinedexception_name ;
raise a;
ex:
***
declare
a exception;
begin
raise a;
end;
Handle Exception:
*****************
-->we can also handle user defined exceptions as same as predefined exception using predefined handler.
when userdefinedexception_name then
statements;
when userdefinedexception_name2 then
statements;
...........
...........
...........
when others then
statements;
EX:
****
DECLARE
A NUMBER(2);
B NUMBER(2);
C NUMBER;
--userdefinedexception_name exception; --syntax
E EXCEPTION; --USER DEFINED EXCEPTION DEFINING
BEGIN
A:=:A;
B:=:B;
IF B=1 THEN
--raise userdefinedexception_name ; syntax
RAISE E; --RAISE EXCEPTION
END IF;
C:=A/B;
DBMS_OUTPUT.PUT_LINE(C);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('SECOND NUMBER NOT BE ZERO');
WHEN E THEN --HANDLING EXCEPTION
DBMS_OUTPUT.PUT_LINE('USER EXCEPTION');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHERS :'||SQLERRM);
END;
/
DECLARE
V1 NUMBER:=:NO;
E1 EXCEPTION; --declare exception(userdefinedexception_name exception)
BEGIN
IF V1>25 THEN
RAISE E1; --Raise Exception (RAISE userdefinedexception_name)
ELSE
DBMS_OUTPUT.PUT_LINE(V1);
END IF;
EXCEPTION
WHEN E1 THEN --Handle Exception (when userdefinedexception_name then)
DBMS_OUTPUT.PUT_LINE('EXCEPTION E1 :'||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHERS :'||SQLERRM);
END;
/
DECLARE
V1 NUMBER:=:NO;
E1 EXCEPTION;
E2 EXCEPTION;
BEGIN
IF V1<25 THEN
RAISE E1;
ELSIF V1>25 THEN
RAISE E2;
ELSE
DBMS_OUTPUT.PUT_LINE(V1);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHERS :'||SQLCODE||','||SQLERRM);
END;
/
DECLARE
A EXCEPTION;
BEGIN
IF TO_CHAR(SYSDATE,'DY')='THU' THEN
RAISE A;
ELSE
DBMS_OUTPUT.PUT_LINE(SYSDATE+10);
END IF;
EXCEPTION
WHEN A THEN
DBMS_OUTPUT.PUT_LINE('MY EXCEPTION RAISED TODAY');
END;
/
DECLARE
A EXCEPTION;
LV_EMPNO NUMBER :=:EMPNO;
LV_SAL NUMBER;
BEGIN
UPDATE EMP1
SET SAL=SAL+1000
WHERE EMPNO=LV_EMPNO;
IF SQL%NOTFOUND THEN
RAISE A;
ELSE
DBMS_OUTPUT.PUT_LINE('UPDATED RECORDS SUCCESSFULLY');
SELECT SAL INTO LV_SAL FROM EMP1 WHERE EMPNO=LV_EMPNO;
DBMS_OUTPUT.PUT_LINE('UPDATED SALARY IS : '||LV_SAL);
COMMIT;
END IF;
EXCEPTION
WHEN A THEN
DBMS_OUTPUT.PUT_LINE('INVALID EMPNO');
END;
/
DECLARE
V_SAL NUMBER;
E EXCEPTION;
BEGIN
SELECT SAL INTO V_SAL FROM EMP1 WHERE EMPNO=:EMPNO;
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATE SALARY : '||V_SAL);
IF (V_SAL>2000) THEN
RAISE E;
ELSE
UPDATE EMP1 SET SAL=V_SAL+100 WHERE EMPNO=:EMPNO;
SELECT SAL INTO V_SAL FROM EMP1 WHERE EMPNO=:EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATE SALARY : '||V_SAL);
END IF;
EXCEPTION
WHEN E THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOESNOT ELIGIBLE FOR COMISSION');
DBMS_OUTPUT.PUT_LINE(SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
TRUNCATE TABLE EMP1;
/
INSERT INTO EMP1 SELECT * FROM EMP;
/
INSERT INTO EMP1 VALUES(1000,'A','HR',7800,SYSDATE,NULL,100,20);
/
INSERT INTO EMP1 VALUES(1001,'B','SALES',7801,SYSDATE,NULL,200,10);
/
COMMIT;
/
DECLARE
I EMP%ROWTYPE;
SAL_MISSING EXCEPTION;
LV_SAL NUMBER;
BEGIN
SELECT EMPNO,ENAME,SAL
INTO I.EMPNO,I.ENAME,I.SAL
FROM EMP1
WHERE EMPNO=:P_EMPNO;
IF I.SAL IS NULL THEN
RAISE SAL_MISSING;
ELSE
I.SAL :=I.SAL+I.SAL*0.25;
END IF;
UPDATE EMP1
SET SAL=I.SAL
WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS ARE : '||I.ENAME||' '||I.SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NUMBER DOES NOT EXIST');
WHEN SAL_MISSING THEN
UPDATE EMP1
SET SAL=3000
WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('THE EMPLOYEE IS NOT HAVING THE SAL SO GIVE THE SALARY AS 3000');
SELECT SAL
INTO LV_SAL
FROM EMP1
WHERE EMPNO=I.EMPNO;
DBMS_OUTPUT.PUT_LINE('AFTER UPDATE SALARY IS : '||LV_SAL);
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR');
END;
/
RAISE APPLICATION ERROR:
************************
-->If you want to display your own user defined exception number and exception mesage then we can use this raise application error.
-->If you want to display user defined exception messages in more descriptive form then we are using raise application error.
syntax:
*******
raise_application_error(error_number,error_message);
Error_Number: It is used to give the error numbers between -20000 to -20999
Error_Message: It is used to give the message upto 512 characters.
EX:
***
DECLARE
X NUMBER;
Y NUMBER;
Z NUMBER;
EX EXCEPTION;
BEGIN
X:=:X;
Y:=:Y;
IF Y=0 THEN
RAISE EX;
ELSE
Z:=X/Y;
DBMS_OUTPUT.PUT_LINE(Z);
END IF;
EXCEPTION
WHEN EX THEN
RAISE_APPLICATION_ERROR(-20457,'SECOND NUMBER NOT BE ZERO');
END;
/
DECLARE
LV_AGE NUMBER :=:AGE;
BEGIN
IF LV_AGE <18 THEN
RAISE_APPLICATION_ERROR(-20001,'HE IS NOT ELIGIBLE FOR VOTE');
ELSE
DBMS_OUTPUT.PUT_LINE('HE IS ELOGIBLE FOR VOTE :'||LV_AGE);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS :'||SQLERRM);
END;
/
DECLARE
A EXCEPTION
BEGIN
RAISE A;
EXCEPTION
WHEN A THEN
RAISE_APPLICATION_ERROR(-20001,'RAISE APPLICATION ERROR IS FIRED');
--DBMS_OUTPUT.PUT_LINE('EXCEPTION IS FIRED');
END;
/
DECLARE
V_SAL NUMBER;
Z EXCEPTION;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE EMPNO=7902;
IF V_SAL>2000 THEN
RAISE Z;
ELSE
UPDATE EMP SET SAL=V_SAL+100 WHERE EMPNO=7902;
END IF;
EXCEPTION
WHEN Z THEN
RAISE_APPLICATION_ERROR(-20150,'SALARY ALREADY HIGH');
END;
/
No comments:
Post a Comment