Re: [Newbie] help :PL/SQL cursor

It helps a lot. I remember using something like 'Whenever notfound continue' or something like that in Pro*C some years ago. I didn't found something in standard SQL that'd say "exception when no_data_found continue"

Sybrand Bakker wrote:

> 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