Capture violation restrictions in psql

Question

I am using an SQL developer and have added a restriction to one of my tables.

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

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

I want to add a “Catch” to the plsql procedure so that if valid_gender is veiled, I can set raise_application_error for it. Is it possible?

Answer 1

Oracle will throw an exception saying:

ORA-02290: validation violation (yourschema.valid_gender) violated

You can catch this in the exception handler and create your own exception using raise_application_error several ways.

1) You can specifically block the ORA-02290 exception as follows:

 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 catch all the exceptions and check 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 quite often happens that the exception handling procedure generalizes this and looks at the restriction-related message in the table.

Answer 2

You can check it out first:

 if gender_value not in ('M','F','I','T') then raise_application_error... end if; 

Answer 3

use an anonymous block in your code ...

  BEGIN INSERT or update... EXCEPTION WHEN dup_val_on_index THEN RISE... END;