Oracle PLSQL Function For to Validate Mail Address

Blog Description:

In this new blog, we will know about how to write a function in oracle plsql for to validate the mail address of customer whenever we are accepting the inputs.

Function for to Validate Mail ID:

create or replace FUNCTION validateemailid (

      v_email_id   IN   VARCHAR2




      v_str_length     NUMBER;

      v_dot_position   NUMBER;

      v_at_position    NUMBER;


      v_dot_position := INSTR (v_email_id, ‘.’);

      v_at_position := INSTR (v_email_id, ‘@’);

      v_str_length := LENGTH (v_email_id);

      IF ((v_dot_position = 0) OR (v_at_position = 0)

          OR (v_dot_position = v_at_position + 1) OR (v_at_position = 1)

          OR (v_at_position = v_str_length)

          OR (v_dot_position = v_str_length)

          OR INSTR (v_email_id, ‘@’, 1, 2) <> 0

         ) THEN

         RETURN ‘Entered mail id is invalid, please pass correct’;

      END IF;

      IF INSTR (SUBSTR (v_email_id, v_at_position), ‘.’) = 0 THEN

         RETURN ‘Entered mail id is invalid, please pass correct mail id.’;

      END IF;

      RETURN ‘Entered mail id is valid…!!!’;

   END validateemailid;

With the help of above function we will validate the mail id of customer and validation message will be triggered out on the bases of inputs.


In this example, we will call function with the sample inputs for to check whether the function is working correctly or not?

Here I am writing an anonymous block for to call our created function for to pass the input,


   v_mailid   VARCHAR2 (100) := ‘’;  

  • –Tested for to check invalid mail id ‘’;

   v_msg varchar(100);  


   SELECT validateemailid (v_mailid)

   into v_msg


   dbms_output.put_line (v_msg);


You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: