Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Unanswered: Exception handling

    I have a stored procedure which inserts records into a table. When a duplicate key is detected the process aborts. I want to continue to process the records. What code would I need to insert in the exception area in order to do this ?

    Thanks in advance to all who reply..

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    begin
    insert into my_table
    values(....);
    exception
    when dup_val_on_index then
    null;
    when others then
    ...handle the other errors
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    However, that would not ignore the error, but will halt the execution of the procedure once it finds a duplicate value, since the control is passed to the exception block.

    I would rather be looking into using BULK methods, with the SAVE EXCEPTION option.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    not true, if the block was on the procedure, then it would. If it is in-line it wouldn't. For example.

    =====================
    declare

    cursor my_cursor is
    select object_name
    from all_objects
    order by object_name;

    begin

    for pnt in my_cursor loop;
    begin
    insert into my_table (pnt.object_name);
    exception
    when others then null;
    end;
    end loop;
    end;
    ============================
    The code above will continue inserting, but will skip over any insert that fails for any reason.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •