Re: Help with Cursor Update/delete/insert

See remarks in-line below:

gcoyle_at_cbs.webramp.net wrote:
>
> On Fri, 17 Sep 1999 21:24:12 GMT, michael_bialik_at_my-deja.com wrote:
>
> Michael,
>
> Thanks for all your help. I've run into some errors (listed at the
> bottom) that are stumping me.
>
> Any help would be great.
>
> Thanks again for your reponse.
>
> GC
>
> 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

        Above: you need a THEN for the second IF.

> 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;

	Above: you have two IFs and only one END IF.  You must end the 
	second IF before you can end the LOOP.

> 34 close compare_tables;
> 35* end;

	Fix the first two errors and the third will probably go away,
	or at least become less mysterious.

	However, since you commented out your test for NOT%FOUND, you
	have an infinite loop.

> 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

Scott McKellar mck9_at_swbell.net
Free PL/SQL utilities at http://home.swbell.net/mck9/pls/ Received on Sun Sep 19 1999 - 21:34:48 CDT