Re: problem w/ exception handling in pl/sql

EdStevens schrieb:
> Oracle 10.2.0.1
>
> Working on a 'self-education' project in pl/sql, getting more familiar
> with bulk collect ... In the code below, if my SELECT returns zero
> rows, I'd expect to drop into the EXCEPTIONS section with a
> NO_DATA_FOUND from the select. Instead, I'm getting a VALUE_ERROR on
> the FOR loop, due to the .FIRST and .LAST being unitialized. I can
> code around it, but really don't understand why I didn't just get a
> NO_DATA_FOUND before getting that far.
>
> Here's the code. I don't think the snipped out parts should be
> relevant to this issue.
>
> DECLARE
>
> <snip>
>
> TYPE usernameTYP IS TABLE OF dba_users.username%TYPE
> INDEX BY BINARY_INTEGER;
> username_t usernameTYP;
>
> <snip more similar to above>
>
> BEGIN
> select username,
>
> <snip>
>
> bulk collect into username_t,
>
> <snip>
>
> from (
> select u.username,
>
> <snip>
>
> from dba_users u,
>
> <snip>
>
> where <snip>
> );
> --
> FOR i IN username_t.FIRST .. username_t.LAST
> LOOP
> DBMS_OUTPUT.PUT_LINE(username_t(i) ...
> );
> END LOOP;
>
> <snip>
>
> EXCEPTION
> when NO_DATA_FOUND then
> DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND ');
> DBMS_OUTPUT.PUT_LINE('');
>
> when COLLECTION_IS_NULL then
> DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL ');
>
> when SUBSCRIPT_BEYOND_COUNT then
> DBMS_OUTPUT.PUT_LINE('SUBSCRIPT_BEYOND_COUNT ');
>
> when SUBSCRIPT_OUTSIDE_LIMIT then
> DBMS_OUTPUT.PUT_LINE('SUBSCRIPT_OUTSIDE_LIMIT ');
>
> when VALUE_ERROR then
> DBMS_OUTPUT.PUT_LINE('VALUE_ERROR ');
>
> when OTHERS then
> DBMS_OUTPUT.PUT_LINE('some error occurred');
>
>
> END;
>

Ed, it is all here (but, sometimes, really hard to find ;-))... Note, you have to test differently whether your collection has no items for nested tables/varrays on one side and associative arrays on the other side.

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2213 <quote>
Because the processing of the BULK COLLECT INTO clause is similar to a FETCH loop, it does not raise a NO_DATA_FOUND exception if no rows match the query. You must check whether the resulting nested table or varray is null, or if the resulting associative array has no elements, as shown in Example 11-10.
</quote>

Best regards

Maxim Received on Wed Jan 03 2007 - 08:01:01 CST