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

   )

      RETURN VARCHAR2

   IS

      v_str_length     NUMBER;

      v_dot_position   NUMBER;

      v_at_position    NUMBER;

   BEGIN

      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.

Demo:

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,

DECLARE

   v_mailid   VARCHAR2 (100) := ‘jain.pankaj@gmail.com’;  

• –Tested for to check invalid mail id ‘jain.pankaj#gmail.com’;

   v_msg varchar(100);  

BEGIN

   SELECT validateemailid (v_mailid)

   into v_msg

   FROM DUAL;

   dbms_output.put_line (v_msg);

END;