Re: Error using Cursor ..Fetch

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