| Handling exceptions in bulk collect [message #563779] |
Fri, 17 August 2012 01:32  |
ajaykumarkona
Messages: 261
Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
For the following procedure if
I send the existed employee number of emp table as input
the procedure is executing successfully.
But if I send the employee number as input which does not exist in the emp table
the exection block does not handling the exception.
I am geeting the following error.
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "RAKULA.SP_TEST_EXCEPTION_BULK", line 8
ORA-06512: at line 7
If I use WHEN OTHERS exception then I am able to handle that exception.
Why it's happening like this.
Please explain me.
CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception_bulk(i_empno NUMBER)
IS
t type_test1;
BEGIN
SELECT deptno BULK COLLECT INTO t
FROM emp
WHERE empno=i_empno;
FOR indx IN t.FIRST..t.LAST
loop
dbms_output.put_line(t(indx));
end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('employee number' ||i_empno|| 'does not exist');
END sp_test_exception_bulk;
/
Please help me how to handle that exception.
If I create the procedure without using
BULK COLLECT then I am able to handle that exception using WHEN NO_DATA_FOUND
In the following procedure I am able to handle the exception.
CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception(i_empno NUMBER,v_dept_no OUT NUMBER)
IS
BEGIN
SELECT deptno INTO v_dept_no FROM emp
WHERE empno=i_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('employee number' ||i_empno|| 'does not exist');
END sp_test_exception;
/
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
