Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> problem w/ exception handling in pl/sql
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;Received on Tue Jan 02 2007 - 19:37:14 CST
<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;