Re: [Newbie] help :PL/SQL cursor

Sebastien FERRANDEZ <sferrandez_at_wineandco.com> schreef in berichtnieuws 392AA0A0.6E41BB0F_at_wineandco.com...
> I get the following message
>
> ORA-01403: no data found (the fetch line)
> ORA-06512: at line 21
>
> I told myself that the cursor was empty but when I write :
> EXIT WHEN Mon_curseur%ROWCOUNT=10 ;
> as the exit condition from my loop, it works !!!
> The type of the variables in which the results of the cursor is fetched
> are OK.
>
> DECLARE
>
> I_IDENTIF VARCHAR2(80);
> I_PRIX NUMBER(18,2);
>
> I_CAT VARCHAR2(128);
> I_OID NUMBER(38);
> LOCAL_LIV FLOAT(126);
> LOCAL_PRICE NUMBER(18,2);
> ARRONDI NUMBER(18,2);
>
> CURSOR Mon_curseur IS SELECT PRICE,PROD_ID,OID,CATEGORIE FROM PRODUCT;
>
> BEGIN
>
> OPEN Mon_curseur;
>
> LOOP
> FETCH Mon_curseur INTO I_PRIX,I_IDENTIF,I_OID,I_CAT;
>
> SELECT WINE_UNITES_LIV_PRIX INTO LOCAL_LIV FROM WINEANDCO_UNITES_LIV
> WHERE OID=I_OID AND WINE_UNITES_LIV_CATEGORIE IS NULL AND
> WINE_UNITES_LIV_LINE=1;
>
> IF (I_CAT<>'X') THEN
> ARRONDI := ROUND (((1+(LOCAL_LIV/100))*1.196),4);
> ELSE
> ARRONDI := ROUND ((1+(LOCAL_LIV/100)),4);
> END IF;
>
> LOCAL_PRICE := ROUND ((I_PRIX*ARRONDI),2);
> UPDATE PRODUCT SET PRICE_ENTIER=(LOCAL_PRICE*100) WHERE
> PROD_ID=I_IDENTIF;
> COMMIT;
>
> EXIT WHEN Mon_curseur%NOTFOUND ; -- THE BONE OF CONTENTION !!!!
>
> END LOOP;
>
> CLOSE Mon_curseur;
>
> END;
> /
>

Well I did count down to get at line 21
and this
> SELECT WINE_UNITES_LIV_PRIX INTO LOCAL_LIV FROM WINEANDCO_UNITES_LIV
> WHERE OID=I_OID AND WINE_UNITES_LIV_CATEGORIE IS NULL AND
> WINE_UNITES_LIV_LINE=1;

is the offending select.
As you don't handle no_data_found situations here, it will simply raise an exception.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue May 23 2000 - 00:00:00 CDT