EXCEPTIONS:
**********
-->A warning or error in PL/SQL program is called Exception
-->It is used to handle the errors at run time.
-->An error occurs during the program execution is called exception in PLSQL.
-->There are two types of exceptions.
1)Predefined Exception(System Defined Exception)
a)Named Exception
b)Unnamed Exception
2)Userdefined Exception
Predefined Exception:
*********************
-->It is one of the exception which are defined by oracle.
-->There are 20 exceptions available.
Predefined Exceptions are:
-->no_data_found
-->too_many_rows
-->invalid_cursor
-->cursor_already_open
-->invalid_number
-->value_error
-->zero_divide
-->others
syntax:
*******
EXCEPTION
when <exceptionname1> then
statements;
when <exceptionname2> then
statements;
when others then
statements;
a)no_data_found:
****************
-->when a pl/sql block contains select---into clause and also if requested data not available in a table,oracle server returns an error
-->To handle this error we are using no_data_found exception.
-->Error is ORA-01403:no data found
DECLARE
LV_ENAME VARCHAR2(20);
LV_SAL NUMBER;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP
WHERE EMPNO=&NO;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||','||LV_SAL);
END;
/
declare
lv_ename varchar2(20);
lv_sal number;
begin
select ename,sal
into lv_ename,LV_sal
FROM EMP
where empno=&no;
dbms_output.put_line(lv_ename||lv_sal);
exception
when no_data_found then
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
dbms_output.put_line('EMPLOYEE DOES NOT EXIST IN TABLE');
END;
/
DECLARE
I EMP1%ROWTYPE;
LV_EMPNO NUMBER :=&NO;
LV_SAL NUMBER;
BEGIN
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
INTO I
FROM EMP1
WHERE EMPNO=LV_EMPNO;
DBMS_OUTPUT.PUT_LINE(I.EMPNO||','||I.ENAME||','||I.JOB||','||I.MGR||','||I.HIREDATE||','||I.SAL||','||I.COMM||','||I.DEPTNO);
IF I.SAL<3000 THEN
UPDATE EMP1
SET SAL=I.SAL+I.SAL*0.25
WHERE EMPNO=LV_EMPNO;
SELECT SAL INTO LV_SAL FROM EMP1 WHERE EMPNO=LV_EMPNO;
DBMS_OUTPUT.PUT_LINE('UPDATED SALARY :'||LV_SAL);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE DOES NOT EXIST');
END;
/
declare
lv_invoice_id number;
begin
select invoice_id
into lv_invoice_id
from ap_invoices_all
where invoice_num='abc';
dbms_output.put_line('invoice id is : '||lv_invoice_id);
exception
when no_data_found then
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
dbms_output.put_line('invalid invoice num');
end;
/
DECLARE
LV_INVOICE_NUM VARCHAR2(20);
LV_INVOICE_CURRENCY_CODE VARCHAR2(10);
LV_INVOICE_AMOUNT NUMBER;
BEGIN
SELECT INVOICE_NUM,INVOICE_CURRENCY_CODE,INVOICE_AMOUNT
INTO LV_INVOICE_NUM,LV_INVOICE_CURRENCY_CODE,LV_INVOICE_AMOUNT
FROM AP_INVOICES_ALL
WHERE 1=1
AND INVOICE_ID='145054123';
DBMS_OUTPUT.PUT_LINE(LV_INVOICE_NUM||','||LV_INVOICE_CURRENCY_CODE||','||LV_INVOICE_AMOUNT);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INVOICE NUMBER IS NOT EXIST');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR CODE IS : '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END;
/
DECLARE
LV_ORGANIZATION_ID NUMBER;
BEGIN
SELECT ORGANIZATION_ID
INTO LV_ORGANIZATION_ID
FROM MTL_PARAMETERS --ORG_ORGANIZATION_DEFINITIONS
WHERE 1=1
AND ORGANIZATION_CODE=:NO;
DBMS_OUTPUT.PUT_LINE(LV_ORGANIZATION_ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('ORGANIZATION CODE DOES NOT EXIST');
END;
/
DECLARE
LV_ORGANIZATION_ID NUMBER;
BEGIN
SELECT ORGANIZATION_ID
INTO LV_ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE 1=1
AND ORGANIZATION_CODE='&NO';
DBMS_OUTPUT.PUT_LINE(LV_ORGANIZATION_ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('ORGANIZATION CODE DOES NOT EXIST');
END;
/
drop table emp1;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
select *
from emp1;
/
insert into emp1 values (1000,NULL,'HR',100,SYSDATE,2000,100,10);
/
insert into emp1 values (1001,'ABCD','HR',100,SYSDATE,2000,100,10);
/
COMMIT;
/
declare
cursor c1 is select * from emp1;
lv_empno varchar2(20);
LV_ERROR_MESSAGE VARCHAR2(4000);
begin
for i in c1
loop
LV_ERROR_MESSAGE :=NULL;
begin
select empno
into lv_empno
from emp1
where ename=i.ename;
dbms_output.put_line('empno is :'||lv_empno);
EXCEPTION
when no_data_found then
LV_ERROR_MESSAGE := LV_ERROR_MESSAGE || ' Invalid ename';
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
dbms_output.put_line('Error message is :'||LV_ERROR_MESSAGE);
When others then
LV_ERROR_MESSAGE :='Error while validating Ename:- '||SQLERRM;
dbms_output.put_line('Error while validating Organization code : ' ||LV_ERROR_MESSAGE);
END;
end loop;
end;
/
b) too_many_rows:
****************
-->when a select..into clause try to return more than one record or more than one value then oracle server returns an error.
-->Error is ora-01422:exact fetch returns more than requested number of rows.
-->To handle this error we are using too_many_rows exception.
DECLARE
LV_ENAME VARCHAR2(50);
LV_SAL NUMBER;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||LV_SAL);
END;
/
DECLARE
LV_ENAME VARCHAR2(50);
LV_SAL NUMBER;
BEGIN
SELECT ENAME,SAL
INTO LV_ENAME,LV_SAL
FROM EMP;
DBMS_OUTPUT.PUT_LINE(LV_ENAME||LV_SAL);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('not to return more rows');
END;
/
INSERT INTO EMP1 VALUES(1001,'A','HR',999,SYSDATE,2000,100,20);
/
INSERT INTO EMP1 VALUES(1002,'A','HR',1000,SYSDATE,3000,200,30);
/
declare
cursor c1 is select * from emp1;
lv_empno varchar2(20);
LV_ERROR_MESSAGE VARCHAR2(4000);
begin
for i in c1
loop
LV_ERROR_MESSAGE :=null;
begin
select empno
into lv_empno
from emp1;
dbms_output.put_line('empno is :'||lv_empno);
exception
when TOO_MANY_ROWS then
LV_ERROR_MESSAGE := LV_ERROR_MESSAGE || ' -- MORETHAN ONE ROW |';
dbms_output.put_line('Error message is :'||LV_ERROR_MESSAGE);
When others then
LV_ERROR_MESSAGE :='Error while validating Ename:- '||SQLERRM;
dbms_output.put_line('Error while validating Organization code : ' ||LV_ERROR_MESSAGE);
END;
end loop;
end;
/
c)Invalid_Cursor:
*****************
-->whenever we are performing invalid operations on the cursor,oracle returns an error.
-->If you are try to close the cursor without opening cursor then oracle server returns an error.
-->ora-01001 : Invalid cursor
-->to handle this error we are using cursor exception.
DECLARE
CURSOR C IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
LOOP
FETCH C INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL);
END LOOP;
CLOSE C1;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL);
END LOOP;
CLOSE C1;
EXCEPTION
WHEN INVALID_CURSOR THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('FIRST WE MUST OPEN THE CURSOR');
END;
d) cursor_already_open:
**********************
-->when we are try to reopen the cursor without closing the cursor oracle server returns an error.
-->ora-06511; cursor already open
-->to handle this error we are using cursor_already_open exception
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL);
END LOOP;
OPEN C1;
END;
/
DECLARE
CURSOR C1 IS SELECT * FROM EMP;
I EMP%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO I;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.ENAME||','||I.SAL);
END LOOP;
OPEN C1;
EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('BEFORE REOPEN WE MUST CLOSE THE CURSOR');
END;
e)Invalid_Number:
*****************
-->whenever we are to convert string type into number type oracle server returns an error.
-->ora-01722 :invalid number
-->to handle this error we are using invid_number exception
BEGIN
INSERT INTO EMP(EMPNO,SAL) VALUES (222,'XYZ');
END;
BEGIN
INSERT INTO EMP(EMPNO,SAL) VALUES (222,'XYZ');
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE('INSERT PROPER DATA ONLY');
END;
f)value_error:
**************
-->whenever we are try to store large amount of data than the specified data type size in variable declaration then oracle server returns an error.
-->ora-06502 :numeric or value error: character to number conversion error.
-->to handle this error we are using value_error exception.
DECLARE
Z NUMBER(3);
BEGIN
Z:='&X'+'&Y';
DBMS_OUTPUT.PUT_LINE(Z);
END;
/
DECLARE
Z NUMBER(3);
BEGIN
Z:='&X'+'&Y';
DBMS_OUTPUT.PUT_LINE(Z);
EXCEPTION
WHEN VALUE_ERROR THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('ENTER PROPER DATA ONLY');
END;
g)zero_divide:
**************
-->whenever we are try to divide by zero then oracle server returns an error.
-->ora-01476:Divisor is equal to zero
-->to handle this error we are using zero_divide exception.
DECLARE
A NUMBER(20);
B NUMBER(20);
C NUMBER(20);
BEGIN
A:=5;
B:=0;
C:=A/B;
DBMS_OUTPUT.PUT_LINE(C);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('B can not be 0');
END;
h)OTHERS:
*********
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP
WHERE EMPNO=:EMPNO;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('EMPNO DOES NOT EXIST');
END;
/
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMPNO DOES NOT EXIST');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('MORETHAN ONE ROW WILL EXIST');
WHEN OTHERS THEN
dbms_output.put_line('Error Code is : '||sqlcode);
dbms_output.put_line('Error message is : '||sqlerrm);
DBMS_OUTPUT.PUT_LINE('EMPNO DOES NOT EXIST');
END;
/
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP
WHERE EMPNO =:EMPNO;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR CODE IS :'||SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END;
/
DECLARE
LV_ENAME VARCHAR2(20);
BEGIN
SELECT ENAME
INTO LV_ENAME
FROM EMP;
DBMS_OUTPUT.PUT_LINE(LV_ENAME);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR CODE IS :'||SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END;
/
DECLARE
A NUMBER(2) :=90;
B NUMBER(2) :=80;
C NUMBER(2);
BEGIN
C :=A+B;
DBMS_OUTPUT.PUT_LINE(C);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR CODE IS :'||SQLCODE);
DBMS_OUTPUT.PUT_LINE('ERROR MESSAGE IS : '||SQLERRM);
END;
No comments:
Post a Comment