sql - Some Syntax Error - Can't figure out where -

here script wrote , has weird syntax error @ exception block. if remove exception block script compiles properly. no sooner write gives me error

error(58,11): pls-00103: encountered symbol "exception" when expecting 1 of following:     ( begin case declare else elsif end exit goto if loop mod    null pragma raise return select update while    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge  

here script

loop   begin     savepoint check_point;      exit when dbms_sql.fetch_rows (cursor_handle) = 0;     dbms_sql.column_value (cursor_handle, 1,  cc , col_err, actual_len);     dbms_sql.column_value (cursor_handle, 2,  di, col_err, actual_len);      if instr (cc, '_') <> 0       cc := trim (cc);       cc := upper(cc);       cc := substr(cc,4,2);        execute immediate 'update  ' || dest || ' set cc = :v1 di = :v2'          using cc, di;        if sql%rowcount > 0         inserts := inserts + 1;         counter := counter + 1;         if counter > 500           counter := 0;           commit;         end if;       end if;        exception         when dup_val_on_index           dups := dups+1;           rollback check_point;         when value_error           valerr := valerr +1;           rollback check_point;         when others           dbms_output.put_line('errno: ' || to_char(sqlcode) || ' msg: ' || sqlerrm);           otherexc := otherexc +1;         if otherexc > 50            exit;         end if;         rollback check_point;                   end if;   end; end loop; 

i know annoying ask such kind question unable figure out error that. lehman @ pl/sql.

the error appears exception clause inside if instr (cc, '_') <> 0 if statements appear want match exception begin statement @ top of loop. believe want move end if; if instr (cc, '_') <> 0 before exception here

loop   begin     savepoint check_point;      exit when dbms_sql.fetch_rows (cursor_handle) = 0;     dbms_sql.column_value (cursor_handle, 1,  cc , col_err, actual_len);     dbms_sql.column_value (cursor_handle, 2,  di, col_err, actual_len);      if instr (cc, '_') <> 0       cc := trim (cc);       cc := upper(cc);       cc := substr(cc,4,2);        execute immediate 'update  ' || dest || ' set cc = :v1 di = :v2'          using cc, di;        if sql%rowcount > 0         inserts := inserts + 1;         counter := counter + 1;         if counter > 500           counter := 0;           commit;         end if; -- if counter > 500       end if; -- if sql%rowcount > 0     end if; -- instr (cc, '_') <> 0     exception     when dup_val_on_index       dups := dups+1;       rollback check_point;     when value_error       valerr := valerr +1;       rollback check_point;     when others       dbms_output.put_line('errno: ' || to_char(sqlcode) || ' msg: ' || sqlerrm);       otherexc := otherexc +1;       if otherexc > 50          exit;       end if;       rollback check_point;                 end; end loop; 

that being said, however, rewrite code bit. committing every 500 rows error. i'm dubious of when others exception handler-- think you'd want @ least write error table or populate collection of errors rather writing dbms_output buffer may or may not ever displayed.

loop   savepoint check_point;    exit when dbms_sql.fetch_rows (cursor_handle) = 0;   dbms_sql.column_value (cursor_handle, 1,  cc , col_err, actual_len);   dbms_sql.column_value (cursor_handle, 2,  di, col_err, actual_len);    if instr (cc, '_') <> 0     cc := trim (cc);     cc := upper(cc);     cc := substr(cc,4,2);      begin       execute immediate 'update  ' || dest || ' set cc = :v1 di = :v2'          using cc, di;        if sql%rowcount > 0         inserts := inserts + 1;       end if;      exception       when dup_val_on_index         dups := dups+1;         rollback check_point;       when value_error         valerr := valerr +1;         rollback check_point;       when others         dbms_output.put_line('errno: ' || to_char(sqlcode) || ' msg: ' || sqlerrm);         otherexc := otherexc +1;         if otherexc > 50            exit;         end if;         rollback check_point;                   end;    end if; -- instr (cc, '_') <> 0 end loop;