Re: problem w/ exception handling in pl/sql

EdStevens wrote:
> 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;
There is code in 10g specifically for handling sparse matrix:

Go to Morgan's Library at www.psoug.org
Click on Array Processing
Scroll down to the SPARSE COLLECTION DEMO

That should work for you for real work ... can't say one way or the other about using it with DBMS_OUTPUT.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org