Common Exceptions in PLSQL with sample example

CREATE OR REPLACE PROCEDURE APPS.XXLEARN_EXCEPTIONS_HANDLING(P_ID NUMBER)

IS

l_id number;

l_name varchar2(100);

l_fun number;

cursor c1

IS

select * from XXLEARN_STAGING

where id=90;

BEGIN

BEGIN

select id,name  into l_id,l_name
from XXLEARN_STAGING
where id=p_id;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('When NO_DATA_FOUND Exceptions');

when TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('When TOO_MANY_ROWS Exceptions');

WHEN INVALID_NUMBER THEN
--this occuers when we pass wrong value in where condition if column dataype is number and if we pass char/string or vice versa then this eeror wil occer 

DBMS_OUTPUT.PUT_LINE('When INVALID_NUMBER Exceptions');

WHEN VALUE_ERROR THEN

-- For example, when your program selects a column value into a character variable, 

--if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. 

--In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.) 

DBMS_OUTPUT.PUT_LINE('When VALUE_ERROR Exceptions');

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('When ZERO_DIVIDE Exceptions');

WHEN TIMEOUT_ON_RESOURCE THEN

--A time-out occurs while Oracle is waiting for a resource.

DBMS_OUTPUT.PUT_LINE('When TIMEOUT_ON_RESOURCE Exceptions');

WHEN ACCESS_INTO_NULL THEN

DBMS_OUTPUT.PUT_LINE('When ACCESS_INTO_NULL Exceptions');

WHEN PROGRAM_ERROR THEN

--PL/SQL has an internal problem.

DBMS_OUTPUT.PUT_LINE('When PROGRAM_ERROR Exceptions');

WHEN STORAGE_ERROR THEN

--PL/SQL runs out of memory or memory has been corrupted.

DBMS_OUTPUT.PUT_LINE('When STORAGE_ERROR Exceptions');
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('When Others Exceptions');
END;

BEGIN

dbms_output.put_line('cursor total count='||c1%ROWCOUNT);

for i in c1 loop
dbms_output.put_line('Inside cursor');
if c1%FOUND
then
dbms_output.put_line('cursor FOUND');
end if;

 if C1%NOTFOUND
then
dbms_output.put_line('cursor notfound');
end if;

 IF C1%ISOPEN then
dbms_output.put_line('cursor ISOPEN');
end if;
dbms_output.put_line('cursor count='||c1%ROWCOUNT);
END loop;

EXCEPTION WHEN INVALID_CURSOR THEN
dbms_output.put_line('INVALID_CURSOR');
END;
END;