Re: Help : simple PL/SQL question

From: <manu_b_at_my-deja.com>
Date: 2000/05/16
Message-ID: <8frtml$gbc$1@nnrp1.deja.com>#1/1

Hi Sebastien,

Here some recommendations to help you to solve your problem : 1. Put Exit when <condition> immediatly after fetch statement 2. Use %TYPE datatype to be sure that your variables are the same type than table columns

3. Use dbms_output to debug your program.
4. 666 is not a good number.
5. Meaby it's not necessary to commit for ech row return by cursor
6. And last but not least I_IDENTIF seems a variable. Remove quotes

around I_IDENTIF in the update statements if you want to test the value inside the variable and not the name of the variable. I think it will be better.

Regards

In article <39216464.CB8E999A_at_wineandco.com>,   Sebastien FERRANDEZ <sferrandez_at_wineandco.com> wrote:
> DECLARE
>
> CURSOR my_cursor IS SELECT PRICE,PROD_ID FROM PRODUCT;
> I_IDENTIF VARCHAR2(80); // exactly the same data type as
 PROD_ID
>
> I_PRIX NUMBER(18,2); // exactly the same data type as
> PRICE
>
> BEGIN
>
> OPEN my_cursor;
>
> LOOP
> FETCH my_cursor INTO I_PRIX,I_IDENTIF;
> UPDATE PRODUCT SET PRICE_ENTIER=(I_PRIX*100) WHERE
> PROD_ID='I_IDENTIF';
> EXIT WHEN (my_cursor%ROWCOUNT > 666) OR
 (my_cursor%NOTFOUND)
>
> ;
> COMMIT;
> END LOOP;
>
> CLOSE my_cursor;
>
> END;
>
> I get "PL/SQL procedure successfully completed." as a result of
> executing this script.
> But nothing (PRICE_ENTIER) is updated in the PRODUCT table...I've been
> spending time looking at this query and I can't see the error...
> I_IDENTIF (which is supposedly updating my ID field) is of VARCHAR
 type
> (sounds very strange but I didn't do the database schema myself).
> PRICE_ENTIER is a NUMBER(38).
> In fact, through the script, I'm converting values from NUMBER(18,2)
> type to NUMBER(38), to get rid of my decimals...
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 16 2000 - 00:00:00 CDT