Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Please help with fetching and Updating inSQL

Re: Please help with fetching and Updating inSQL

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 20 Sep 1999 04:22:16 GMT
Message-ID: <37e5b5d1.1274958@news.demon.nl>


On line 21 the second if should read
if loc_number IS NULL THEN
etc.

BTW you are likely to get the infamous ora-1555 error (snapshot too old), as you are updating the table you are selecting from and updating the same table, committing your update inside the loop. You need to relocate the commit outside the loop.

Hth,

Sybrand Bakker, Oracle DBA

On Mon, 20 Sep 1999 02:17:53 GMT, 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
>
Received on Sun Sep 19 1999 - 23:22:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US