Adding an email check constraint in APEX

Question

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?

You should be careful with this approach. There are a large number of valid email addresses that may be rejected. — Jon Heller

DCookie DCookie · Accepted Answer · 2010-12-08T16:05:45

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}'));

Adding an email check constraint in APEX

1 Answers