> 21 if loc_trancode = 'A' then
if loc_number = null then
> 22 insert into mc_dir
> (mc.lastname,mc.firstname,mc.number_)
> 23 values (loc_lastname,loc_firstname,loc_number);
> 24 commit;
end if;
> 25 elsif loc_trancode = 'D' then
> 26 delete from mc_dir
> 27 where mc_dir.lastname = loc_lastname and
> 28 mc_dir.firstname = loc_firstname and
> 29 mc_dir.number_ = loc_number;
> 30 commit;
> 31 end if;
<gcoyle_at_cbs.webramp.net> wrote in message
news:37e5987e.17212240_at_news.supernews.com...
> This is frustrating me to no end.
> This sql is need to update one table based on a transation code read
> from another table.
>
> I think I almost there but it keeps bombing out on me.
>
> Any Help would be fantastic.
> Thanks
> GC
>
> Please see the error codes at the bottom of this message.
>
> SQL> r
> 1 declare
> 2 loc_lastname MC_DIR.LASTNAME%TYPE;
> 3 loc_firstname MC_DIR.FIRSTNAME%TYPE;
> 4 loc_number MC_DIR.NUMBER_%TYPE;
> 5 loc_trancode GCTEMP.TRAN_CODE%TYPE;
> 6 cursor compare_tables is
> 7 select lastname,firstname,number_,TRAN_CODE
> 8 from Mc_Dir MC, gctemp GC
> 9 where
> 10 UPPER(mc.lastname)(+) = UPPER(gc.last_name) and
> 11 UPPER(mc.firstname)(+) = UPPER(gc.first_name)and
> 12 UPPER(mc.number_)(+) = UPPER(gc.extsn) ;
> 13 begin
> 14 dbms_output.enable;
> 15 open compare_tables;
> 16 loop
> 17 fetch compare_tables
> 18 into loc_lastname,loc_firstname,loc_number,loc_trancode;
> 19 /*if compare_tables%notfound then
> 20 exit; */
> 21 if loc_trancode = 'A' then if loc_number = null
> 22 insert into mc_dir
> (mc.lastname,mc.firstname,mc.number_)
> 23 values (loc_lastname,loc_firstname,loc_number);
> 24 commit;
> 25 elsif loc_trancode = 'D' then
> 26 delete from mc_dir
> 27 where mc_dir.lastname = loc_lastname and
> 28 mc_dir.firstname = loc_firstname and
> 29 mc_dir.number_ = loc_number;
> 30 commit;
> 31 end if;
> 32 dbms_output.put_line('Processed student: '||loc_lastname );
> 33 end loop;
> 34 close compare_tables;
> 35* end;
> declare
> *
> ERROR at line 1:
> ORA-06550: line 22, column 17:
> PLS-00103: Encountered the symbol "INSERT" when expecting one of the
> following:
> * & - + / mod rem then an exponent (**) and or ||
> The symbol "then" was substituted for "INSERT" to continue.
> ORA-06550: line 33, column 6:
> PLS-00103: Encountered the symbol "LOOP" when expecting one of the
> following:
> if
> ORA-06550: line 36, column 0:
> PLS-00103: Encountered the symbol ";" when expecting one of the
> following:
> begin function package pragma procedure form
>
>
> SQL> spool off
>
Received on Mon Sep 20 1999 - 08:43:10 CDT
