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

From: <andreas.prusch_at_sintec.de>
Date: Thu, 16 Jul 1998 09:48:51 GMT
Message-ID: <6okia3$vul$1@nnrp1.dejanews.com>

You have to write

   cursor cur_camp is

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

You must declare the cursor with for update then you can use where current of in your update clause. Another option is to declare

   cursor cur_campis

     select HH_LEVEL2_KEY, rowid  from ccm.household
     where phone_number like '6%';

   r cur_camp%rowtype;
Then you can update

   update ccm.household set ... where rowid = r.rowid; The second option don't lock the rows until the update, the first locks the rows at the first fetch.

Hope this will help
Andreas Prusch

In article <6ojbvf$i8d$0_at_204.179.92.164>,   dshi_at_magpage.com (David Shi) wrote:
> 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.
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 16 1998 - 04:48:51 CDT