Oracle PL/SQL exceptions.
Oracle has it's own inbuilt exception handlers to handle PL/SQL errors in different situations.
Here we are going to discuss about a few PL/SQL exceptions, it's occurrence and it's solutions
Since we are not working with any database here we will try to avoid any specific table releated queries
1. NO_DATA_FOUND
NO_DATA_FOUND is a exception handler which can be used when a certain sql query returns no
results.
------------------------------------------------------------------------------------------------------------------------
declare
x umber;
begin
select 1 into x from dual where 1<>1;
-- As you can see a query has been firedwith a false condition.
dbms_output.put_line(x||' returned as output') ;
exception
when NO_DATA_FOUND then
dbms_output.put_line('Query returns no result') ;
end;
------------------------------------------------------------------------------------------------------------------------
If you run this pl/sql block output will be obviously "Query returns no result", but suppose you change the query as such select 1 into x from dual where 1=1; then output will be "1 returned as output".
2. TOO_MAY_ROWS
TOO_MAY_ROWS exception occurs when you attempt to get value from records by select into
clause which returns more than one rows. Say you have a table called employee, which supposed
to have unique department code in a particular year. The query should be like this
Field name : dept_code, year (yyyy)
Possible data :
emp_code emp_name dept_code year
111 Sam 13 2010
111 Sam 17 2011
111 Sam 25 2012
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Query returned more than one rows"
/* now see i have not used where year = value, therefore the query returned 3 rows i the above
case, but if I have used where year= (say) 2010, then it returned one single row and exception
didn't have occured */
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee where year=2010;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Dept Code is 13"
3. ZERO_DIVIDE
ZERO_DIVIDE is an exception which occurs when you try to divide any number by zero i your
plsql block. Here is a simple query to show you how to catch this exception.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select 1/0 into n from dual;
exception
when ZERO_DIVIDE then
dbms_output.put_line('Attempting to divide a number by zero');
end;
------------------------------------------------------------------------------------------------------------------------
-- Output : Attempting to divide a number by zero
As soon as plsql engine parse the query and find 1/0 it jumps to exception ZERO_DIVIDE.
4. INVALID_NUMBER
This exception occurs when you attempt to convert a non-numeric value to numeric value.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select to_number('123abcd') into n from dual;
exception
when INVALID_NUMBER then
dbms_output.put_line('Attempting to convert a non-numeric value to numeric one');
end;
------------------------------------------------------------------------------------------------------------------------
--output : Attempting to convert a non-numeric value to numeric one
5. CURSOR_ALREADY_OPEN
This exception occurs when a cursor which is already opened is opened again without closing it.
Here is an example
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
open rec;
open rec;
loop
fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when CURSOR_ALREADY_OPEN then
dbms_output.put_line('Attempting to open a cursor which is already opened');
end;
--output : Attempting to open a cursor which is already opened'
------------------------------------------------------------------------------------------------------------------------
As you can see the above plsql block the statement open rec is called twice, and exception occurred when it is called second time. If you remove the second occurrence of open rec the error will not occur.
6. ROW_TYPE_MISMATCH
This exception occurs when attempt to fetch record into a different type of rowtype
Just go through this example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
dept department%rowtype;
begin
open rec;
open rec;
loop
fetch rec into dept;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when ROW_TYPE_MISMATCH then
dbms_output.put_line('Attempting to fetch into object of a different rowtype');
end;
--output : Attempting to fetch into object of a different rowtype'
------------------------------------------------------------------------------------------------------------------------
Follow the fetch statement, it fetching employee data into department data.
7. INVALID_CURSOR
This error occurs if you attempt to fetch record from a cursor which is not opened.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
loop fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when INVALID_CURSOR then
dbms_output.put_line('Attempting to fetch record from cursor which is not opened');
end;
--output : Attempting to fetch record from cursor which is not opened'
------------------------------------------------------------------------------------------------------------------------
8. OTHERS
You can use OTHERS exception when you cannot determine the type of exception occurred.
You can use OTHERS instead for all the above problems
=======================================================================
You can also watch the video here : https://www.youtube.com/watch?v=XjisTe5YMxE
Press the LIKE button if you like the video.
You can also subscribe to my channel :- https://www.youtube.com/subhro190776
Oracle has it's own inbuilt exception handlers to handle PL/SQL errors in different situations.
Here we are going to discuss about a few PL/SQL exceptions, it's occurrence and it's solutions
Since we are not working with any database here we will try to avoid any specific table releated queries
1. NO_DATA_FOUND
NO_DATA_FOUND is a exception handler which can be used when a certain sql query returns no
results.
------------------------------------------------------------------------------------------------------------------------
declare
x umber;
begin
select 1 into x from dual where 1<>1;
-- As you can see a query has been firedwith a false condition.
dbms_output.put_line(x||' returned as output') ;
exception
when NO_DATA_FOUND then
dbms_output.put_line('Query returns no result') ;
end;
------------------------------------------------------------------------------------------------------------------------
If you run this pl/sql block output will be obviously "Query returns no result", but suppose you change the query as such select 1 into x from dual where 1=1; then output will be "1 returned as output".
2. TOO_MAY_ROWS
TOO_MAY_ROWS exception occurs when you attempt to get value from records by select into
clause which returns more than one rows. Say you have a table called employee, which supposed
to have unique department code in a particular year. The query should be like this
Field name : dept_code, year (yyyy)
Possible data :
emp_code emp_name dept_code year
111 Sam 13 2010
111 Sam 17 2011
111 Sam 25 2012
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Query returned more than one rows"
/* now see i have not used where year = value, therefore the query returned 3 rows i the above
case, but if I have used where year= (say) 2010, then it returned one single row and exception
didn't have occured */
------------------------------------------------------------------------------------------------------------------------
declare
deptcode number;
begin
select dept_code into deptcode from employee where year=2010;
dbms_output.put_line('Dept Code is '||deptcode);
exception
when TOO_MANY_ROWS then
dbms_output.put_line('Query returned more than one rows');
end;
------------------------------------------------------------------------------------------------------------------------
-- output : "Dept Code is 13"
3. ZERO_DIVIDE
ZERO_DIVIDE is an exception which occurs when you try to divide any number by zero i your
plsql block. Here is a simple query to show you how to catch this exception.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select 1/0 into n from dual;
exception
when ZERO_DIVIDE then
dbms_output.put_line('Attempting to divide a number by zero');
end;
------------------------------------------------------------------------------------------------------------------------
-- Output : Attempting to divide a number by zero
As soon as plsql engine parse the query and find 1/0 it jumps to exception ZERO_DIVIDE.
4. INVALID_NUMBER
This exception occurs when you attempt to convert a non-numeric value to numeric value.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
n number;
begin
select to_number('123abcd') into n from dual;
exception
when INVALID_NUMBER then
dbms_output.put_line('Attempting to convert a non-numeric value to numeric one');
end;
------------------------------------------------------------------------------------------------------------------------
--output : Attempting to convert a non-numeric value to numeric one
5. CURSOR_ALREADY_OPEN
This exception occurs when a cursor which is already opened is opened again without closing it.
Here is an example
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
open rec;
open rec;
loop
fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when CURSOR_ALREADY_OPEN then
dbms_output.put_line('Attempting to open a cursor which is already opened');
end;
--output : Attempting to open a cursor which is already opened'
------------------------------------------------------------------------------------------------------------------------
As you can see the above plsql block the statement open rec is called twice, and exception occurred when it is called second time. If you remove the second occurrence of open rec the error will not occur.
6. ROW_TYPE_MISMATCH
This exception occurs when attempt to fetch record into a different type of rowtype
Just go through this example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
dept department%rowtype;
begin
open rec;
open rec;
loop
fetch rec into dept;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when ROW_TYPE_MISMATCH then
dbms_output.put_line('Attempting to fetch into object of a different rowtype');
end;
--output : Attempting to fetch into object of a different rowtype'
------------------------------------------------------------------------------------------------------------------------
Follow the fetch statement, it fetching employee data into department data.
7. INVALID_CURSOR
This error occurs if you attempt to fetch record from a cursor which is not opened.
Here is an example.
------------------------------------------------------------------------------------------------------------------------
declare
cursor rec is select * from employee;
emp employee%rowtype;
begin
loop fetch rec into emp;
exit when SQL%NOTFOUND
end loop;
close rec;
exception
when INVALID_CURSOR then
dbms_output.put_line('Attempting to fetch record from cursor which is not opened');
end;
--output : Attempting to fetch record from cursor which is not opened'
------------------------------------------------------------------------------------------------------------------------
8. OTHERS
You can use OTHERS exception when you cannot determine the type of exception occurred.
You can use OTHERS instead for all the above problems
=======================================================================
You can also watch the video here : https://www.youtube.com/watch?v=XjisTe5YMxE
Press the LIKE button if you like the video.
You can also subscribe to my channel :- https://www.youtube.com/subhro190776
No comments:
Post a Comment