DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to override exception?

  1. #1
    Join Date
    Jul 2000
    Posts
    70
    I run a pl/sql script that updates records in a table. In some cases a record that it tries to update to, already exists in the table which causes a unique constraint voilation exception.
    What changes can I make to the script whereby whenever a unique constraint is violated, it simply does not update that specific record and move on to the next record and continue with the update as normal?
    Would appreciate examples for clarification.
    Thhanks.

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    If you really want to do this, put the update statement in a PL/QSLblock with an exception. You know the errorcode, use it in the OTHERS exception or even better use DUP_VAL_ON_INDEX exception.

    You get something like:

    BEGIN
    UPDATE your_table
    SET unique_col = new_val
    WHERE condition
    EXCEPTION
    WHEN OTHERS THEN
    IF SQLCODE = -1
    THEN
    NULL;
    ELSE
    RAISE;
    END IF;
    END;

    or:

    BEGIN
    UPDATE your_table
    SET unique_col = new_val
    WHERE condition
    EXCEPTION
    WHEN dup_val_on_index THEN
    NULL:
    END;


Posting Permissions

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


Click Here to Expand Forum to Full Width