Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: what's wrong with my "update ... current of ..."

Re: what's wrong with my "update ... current of ..."

From: rok <rok_at_MCI2000.com>
Date: Thu, 16 Jul 1998 02:45:59 GMT
Message-ID: <01bdb064$19d130c0$470537a6@raghus-computer>


You need to do what exactly the message says. That is, declare your cursor as

cursor cur_camp is

    select HH_LEVEL2_KEY from ccm.household     where phone_number like '6%'
    for update;

Then it will let you update.

raghuvir  

David Shi <dshi_at_magpage.com> wrote in article <6ojbvf$i8d$0_at_204.179.92.164>...
> I kept getting the message
>
> PLS-00404: cursor 'CUR_CAMP' must be declared with FOR UPDATE to use with
> CURRENT OF
>
> from the following script. If I comment out the "update ..." line,
everything is
> fine, so cursor is defined and is fetched fine. Where is the mistake?
>
> Thanks for your help.
>
> David
>
> /*===================================================================
> || this script simulate a promotion
> */
>
> truncate table ccm.product;
> truncate table ccm.vendor;
> truncate table ccm.promotion;
> truncate table ccm.solicitation;
>
>
> set define #
>
> declare
> var_prod_id number;
> var_hhkey ccm.household.HH_LEVEL2_KEY%type;
> var_promo_key ccm.promotion.promo_key%type;
>
> cursor cur_camp is
> select HH_LEVEL2_KEY from ccm.household
> where phone_number like '6%';
>
> begin
>
> insert into ccm.product
> ( PRODUCT_ID, PRODUCT, PRODUCT_LINE, PRODUCT_TYPE ) values
> ( 1, 'AD&D', 'HEALTH INSURANCE', 'INSURANCE' )
> ;
>
> insert into ccm.vendor
> ( VENDOR_ID, VENDOR_NAME, VENDOR_DATA ) values
> ( 1, 'CG', 'ETC' )
> ;
>
> select product_id
> into var_prod_id
> from ccm.product
> where product = 'AD&D';
>
> select ccm.promo_key_seq.nextval
> into var_promo_key
> from dual;
>
> insert into ccm.promotion
> ( PROMO_KEY, PROMO_DESC, SHIP_DATE, EXPIRE_DATE, PRODUCT_ID ) values
> ( var_promo_key, 'A TEST', sysdate, sysdate + 60, var_prod_id );
>
> open cur_camp;
>
> loop
> fetch cur_camp into var_hhkey;
> exit when cur_camp%notfound;
>
> update ccm.household
> set reserve_flag1 = '1'
> where current of cur_camp;
>
> insert into ccm.solicitation
> ( HH_LEVEL2_KEY, PROMO_KEY, DMOL_NUMBER, ORIGINAL_SOURCE_CODE,
> FINAL_SOURCE_CODE, ATTEMP_COUNT,
> MODEL_SCORE1, MODEL_RANK1, MODEL_SCORE2, MODEL_RANK2,
FINAL_DISP_CODE,
> FINAL_DISP_DATE, FINAL_DISP_AMOUNT ) values
> ( var_hhkey, var_promo_key, null, null, null, 0,
> 700, 1, 600, 2, null,
> null, null );
>
> end loop;
>
> close cur_camp;
>
> end;
> /
>
> set define &
>
> show errors
>

>




> after execution, I got:
>
>
> Table truncated.
>
>
> Table truncated.
>
>
> Table truncated.
>
>
> Table truncated.
>
> where current of cur_camp;
> *
> ERROR at line 33:
> ORA-06550: line 33, column 22:
> PLS-00404: cursor 'CUR_CAMP' must be declared with FOR UPDATE to use with
> CURRENT OF
> ORA-06550: line 31, column 5:
> PL/SQL: SQL Statement ignored
>
>
> No errors.
>
>
Received on Wed Jul 15 1998 - 21:45:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US