Re: Error using Cursor ..Fetch

I understand your point. However, even the record found by the cursor, I still would get the error if I put the section above the EXIT WHEN.. cursor%NOTFOUND. It seems to me that oracle is not assigning the value to the variable until after it checks whether cursor found anything internally. So I have to use something such as cursor%NOTFOUND or cursor%.. to move the cursor OUT OF the FETCH scope to finalize the value to the variable. otherwise just saying FETCH .. INTO variable does not assign the value from query to the vairable. Am I right?

Sybrand Bakker wrote:

>On Fri, 20 Aug 2004 10:58:33 -0400, cschang <cschang_at_maxinter.net>
>wrote:
>
>>I use a cursor to query a single record in a procedure. At the OPEN
>>Cursor part
>>...
>>OPEN v_cursor FOR v_sqlStmt; <-- v_sqlStmt is the select statement
>> LOOP
>> FETCH v_cursor
>> INTO p_poID(v_count), p_poLine(v_count), p_userID(v_count),
>>p_errorMsg(v_count), p_waybill(v_count), p_packinglist(v_count);
>> EXIT WHEN v_cursor%NOTFOUND;
>> IF p_errorMsg(v_count) IS NOT NULL THEN
>> v_poInfo:= getPOInfo(p_poID(v_count), p_poLine(v_count),
>>p_condStr);
>> p_errorMsg(v_count):= p_errorMsg(v_count) || '<br>' || v_poInfo;
>> END IF;
>> v_count:= v_count + 1;
>> END LOOP;
>>..
>>
>>if I put the section:
>>
>> IF p_errorMsg(v_count) IS NOT NULL THEN
>> v_poInfo:= getPOInfo(p_poID(v_count), p_poLine(v_count), p_condStr);
>> p_errorMsg(v_count):= p_errorMsg(v_count) || '<br>' || v_poInfo;
>> END IF;
>>
>>before the "EXIT WHEN v_cursor%NOTFOUND;" I will get a data not found
>>error at run time for p_errorMsg(v_count), can any one tell me why? The
>>spec of the p_errorMsg(v_count) is an IN OUT type. My system is 8.1.7
>>on NT 4. Thanks.
>>
>>C Chang
>>
>
>This to be expected. As soon as you run into the NOTFOUND situation
>(which is being caused by the FETCH ), your variables in the INTO
>clause are undefined. You'll always need to trap NOTFOUND directly
>following a FETCH before doing anything else.
>
>
>--
>Sybrand Bakker, Senior Oracle DBA
>
Received on Fri Aug 20 2004 - 16:07:20 CDT