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

From: David Shi <dshi_at_magpage.com>
Date: 15 Jul 1998 22:54:39 GMT
Message-ID: <6ojbvf$i8d$0@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.

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 - 17:54:39 CDT