CODEWITHSUNDEEP
plsqloracle10g
user1636922
user1636922

Reputation: 2559

PL SQL Handling trigger errors

I have script files with thousands of sql INSERT INTO lines. Some of these lines will insert data that's already in the database, so I want to catch these errors and just bypass it. Is there a way to catch errors thrown by a trigger and ignoring it?

ERROR at line 1:
ORA-20053: Username must be unique
ORA-06512: at "MY_TRIGGER", line 18
ORA-04088: error during execution of trigger 'MY_TRIGGER'
ORA-06512: at line 3

I could do this with unique constraints by catching a DUP_VAL_ON_INDEX and then doing NULL, but this does not work for the above trigger error.

Upvotes: 1

Views: 488

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

You can define your own exception and use that just like you would use one of the default exception handlers like dup_val_on_index.

DECLARE
  l_unique_username_ex exception;
  pragma exception_init( l_unique_username_ex, -20053 );
BEGIN
  <<your INSERT statement>>
EXCEPTION
  WHEN l_unique_username_ex
  THEN
    NULL; -- Ignore the error
END;

In general, though, it would make more sense to modify your scripts so that they only try to insert rows that do not already exist rather than letting the trigger try to flag them and then ignoring the error.

Upvotes: 1

Related Questions