EdStevens wrote:
> Maxim Demenko wrote:
>> 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
>
> Maxim and Daniel,
>
> Thanks for the pointers and your patience. I'm still trying to get a
> good firm grasp of the different collection types and how they are
> handled. That's part of the purpose of this current exercise I've
> given myself.
When you figure it out please let us know. ;-)
--
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
Received on Wed Jan 03 2007 - 17:55:11 CST
