Home » SQL & PL/SQL » SQL & PL/SQL » Handling exceptions in bulk collect (Oracle 9i)
Handling exceptions in bulk collect [message #563779] Fri, 17 August 2012 01:32 Go to next message
ajaykumarkona
Messages: 399
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;
/

Re: Handling exceptions in bulk collect [message #563782 is a reply to message #563779] Fri, 17 August 2012 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58640
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Indent your code
2/ Post all what is needed for us to try what you did. For instance, we have not the type type_test1
3/ Database PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Querying Data with PL/SQL
Paragraph Selecting Multiple Rows: BULK COLLECT Clause
Paragraph Example 6-13, "Fetching Bulk Data With a Cursor".

There is NO_DATA_FOUND exception with BULK COLLECT, you have to check t.COUNT.

Regards
Michel

Re: Handling exceptions in bulk collect [message #563783 is a reply to message #563782] Fri, 17 August 2012 01:46 Go to previous message
Michel Cadot
Messages: 58640
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A complete example in:
Database PL/SQL User's Guide and Reference
Chapter 11 Tuning PL/SQL Applications for Performance
Section Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
Paragraph Retrieving Query Results into Collections with the BULK COLLECT Clause

Regards
Michel

Previous Topic: Across Schema Stored Procedure Access
Next Topic: Time Bucket Outer
Goto Forum:
  


Current Time: Fri Aug 01 03:05:38 CDT 2014

Total time taken to generate the page: 0.93564 seconds