Search code examples
oracleemail-validationcheck-constraintsoracle-apex

Adding an email check constraint in APEX

I'm fairly new to Oracle and very new to APEX. I'm trying to add a constraint on a table to validate the email:

REGEXP_LIKE(CALLER_EMAIL, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')

Now if I'm right this would work fine inside a CONSTRAINT <name> CHECK(REGEXP_LIKE(...)) however I get this (confusing) error when I attempt to save it:

ORA-00920: invalid relational operator

I think it is because the generated query contains "CALLER_EMAIL":

alter table "CALL" add constraint
"CALL_EMAILFORMAT_CHK" check ( "CALLER_EMAIL" REGEXP_LIKE(CALLER_EMAIL, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}'))

Any ideas?

Solution

  • Try this:

    alter table "CALL" add constraint
"CALL_EMAILFORMAT_CHK" check 
   ( REGEXP_LIKE(CALLER_EMAIL, '[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}'));