Catching a constraint violation in psql

Question

I am using sql developer, and have added a constraint to one of my tables.

constraint valid_gender check(gender in ('M','F','I','T'))

When I try to add an entry with say 'x' for gender using a plsql procedure, it fails with constraint violation (as it should).

I want to add a "Catch" to the plsql procedure so that if valid_gender is voilated I can raise_application_error specific to it. Is this possible?

Answer 1

Oracle will raise an exception that says:

ORA-02290: check constraint (yourschema.valid_gender) violated

You can catch that in an exception handler and raise your own exception instead using raise_application_error in a couple of ways.

1) You can specifically trap the ORA-02290 exception like this:

declare
  e_check_violated exception
  pragma exception_init (e_check_violated, -2290);
begin
  insert ...
exception
  when e_check_violated then
    if sqlerrm like '%(yourschema.valid_gender)%' then
       raise_application_error(-20001,'Invalid gender');
    else
      raise;
    end if;
end;

2) You can trap all exceptions and inspect them:

begin
  insert ...
exception
  when others then
    if sqlerrm like 'ORA-02290:%(yourschema.valid_gender)%' then
       raise_application_error(-20001,'Invalid gender');
    else
      raise;
    end if;
end;

In a large application it is quite common to have an exception handling procedure to generalise this and look up the constraint-specific message in a table.