Oracle Apex email validation

Question

I have an item to enter the email ids item_email

I need to give validation for email id item item_email that the email id should not exists already in table.

So I have to create email validation please help me to proceed on this.

Answer 1

You need to create a Validation (Processing tab) with type No Rows Returned, and put the following to the validation query:

select 1
  from table_with_emails
 where email_column = :P_EMAIL_ITEM

UPD
To check email: create a new validation (it is easier than add two checks in one) with type PL/SQL Expression, and add the following:

regexp_like (:P_EMAIL_ITEM, '^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')

Creating a new validation also allows you to define two different error messages: when email is already exists and when it has wrong format.

Answer 2

Additionally, consider creating UNIQUE INDEX on e-mail column. Apex validation would do the job, but won't prevent another ways of inserting data.