Re: Please help with fetching and Updating inSQL

These are all simple syntax errors. You are missing a THEN for the second IF on line 21. It would be better to put that IF on it's own line. Then you'll need another END IF for that second IF.

Fix your syntax errors first, then you might start on the logic errors. (Open your PL/SQL reference manual.)

In article <37e5987e.17212240_at_news.supernews.com>,   gcoyle_at_cbs.webramp.net wrote:
> 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
>
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3700 <<<NOTE new number

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 20 1999 - 08:02:00 CDT