Re: Help Update table with cursor fetch

On Wed, 15 Sep 1999 19:45:28 GMT, gcoyle_at_cbs.webramp.net wrote:

the code for updating a found record does not work it comes back with an error.
Thanks
GC

>Hi,
>
>I need help to compare the trans_code colunm in the gctemp table and
>based on:
>
>{psudo code
>if tran_code:= " d" delete the record from mc_dir
>if tran_code:= " A" update the record to mc_dir
>if tran_code:= " A" and not found in mc_dir insert the record into
>mc_dir}
>
>Below is listed my code ANY Help would be great.
>Thanks
>GC
>
>declare
>loc_lastname MC_DIR.LASTNAME%TYPE;
>loc_firstname MC_DIR.FIRSTNAME%TYPE;
>loc_number_ MC_DIR.NUMBER_%TYPE;
>loc_trancode GCTEMP.TRAN_CODE%TYPE;
>loc_uid mc_dir.uid_%type;
>
> cursor compare_tables is
> select lastname,firstname,number_,TRAN_CODE,uid_
> from Mc_Dir MC, gctemp GC
> where
> UPPER(mc.lastname) = UPPER(gc.last_name) and
> UPPER(mc.firstname) = UPPER(gc.first_name) and
> UPPER(mc.number_) = UPPER(gc.extsn) ;
>begin
>dbms_output.enable;
> open compare_tables;
> loop
> fetch compare_tables
> into
>loc_lastname,loc_firstname,loc_number_,loc_trancode,loc_uid;
> if compare_tables%notfound
> then
> exit;
> elsif loc_trancode = 'D' then
> delete from mc_dir
> where
> mc_dir.lastname = loc_lastname and
> mc_dir.firstname = loc_firstname and
> mc_dir.number_ = loc_number_;
> commit;
>*************************************************Update help required*
> elsif loc_trancode = 'A' then
> update mc_dir
> set=
> mc_dir.lastname = loc_lastname and
> mc_dir.firstname = loc_firstname and
> mc_dir.number_ = loc_number_;
> commit;
> exit;
>
> end if;
>
> dbms_output.put_line('Processed student: '||loc_lastname );
> end loop;
> close compare_tables;
>end;
>/
>
>
Received on Thu Sep 16 1999 - 10:41:49 CDT