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;
