PLSQL check my email adress...

PL SQL Code for checking mail adress

So you want to put some security check on you're mail input field...
As you know mail can consist letters, numbers and dots....
It must contain only one @ sign...
How would you check you're email?


I do check on two different ways
No1.

Simple way of checking allowed signs in mail

function f_emailok (i_email in varchar2) return boolean is
  l_dot    NUMBER;
  l_at     NUMBER;
  l_length NUMBER;
BEGIN
  l_dot    := instr(i_email ,'.');
  l_at     := instr(i_email,'@');
  l_length := length(i_email);
  IF ((l_dot = 0) OR (l_at = 0) OR (l_dot = l_at + 1) OR
     (l_at = 1) OR (l_at = l_length) OR (l_dot = l_length))
  then
    RETURN (false);
  end if;
  IF instr(substr(i_email,l_at),'.') = 0 then
    RETURN (false);
  end if;
  return (true);
END f_emailok;

Let's explain code....

With instr function we are searching for sign in our string. In this case we search for . (dot), @ (at) and we are measuring length of our string input.
with 
   l_dot    := instr(i_email ,'.');
we get some numeric value in our l_dot variable.
let's say i_email is filled with value some.mail@gmail.com
l_dot will pick up value 5 from i_email input cause dot sign is on the fifth place in our string.
after we pick up @ value on 11th sign in string and length of  our string we can do some logic.
We say if value of l_dot is 0 or l_at = 0 or there is @ sign behind dot or @ sign is on the first place or there is all string filled with @ or with dots return false.

We still need one more check. Is there anything behind @ sign 
   instr(substr(i_email,l_at),'.') = 0
if value is zero return false...

If everything goes correctly we return true value.

But there is one bug in code :) you can pass email in incorrect format...
Figure out which and how to solve it...


No2.

Now we check email format with OWA pattern integrated function  

function f_emailok (i_email in varchar2) return boolean is

begin
         if owa_pattern.match(i_email,'\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||
            '\@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}\.\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then
         return(true);
      else
         return(false);
      end if;
   exception
      when others then
      return(false);
END f_emailok;

So what does this code do?
It searches for allowed signs in our code....
we pass some string and in order with our owa patterns we search if it consists of illegal characters...
here is list of pattern  

PLSQL list of pattern

Assertions:

• ^ Matches the beginning of a line (or string)
• $ Matches the end of a line (or string)
• Quantifiers:
• {n,m} Must match at least n times, but not more than m times
• {n,} Must match at least n times
• {n} Must match exactly n times.
• * 0 or more occurances
• + 1 or more occurances
• ? 0 or 1 occurance(s)

Legal atoms:

• . matches any character except \n
• A list of characters in square brackets [] is a class of characters,
• for example [0-9] indicates match any character from 0 to 9.
• \n matches newlines
• \t matches tabs
• \d matches digits [0-9]
• \D matches non-digits [^0-9]
• \w matches word characters (alphanumeric) [0-9a-z_A-Z]
• \W matches non-word characters [^0-9a-z_A-Z]
• \s matches whitespace characters [ \t\n]
• \S matches non-whitespace characters [^ \t\n]
• \b matches on “word” boundaries (between \w and \W)
• A backslashed x followed by two hexadecimal digits, such as \x7f,matches the character having that hexadecimal value.

• A backslashed 2 or 3 digit octal number such as \033 matches the character with the specified value.

• Any other “backslashed” character matches itself.

Read More

PLSQL numeric check

PL SQL numeric check

Assuming that you need some function to check input from the front-end and you want to assure that input is numerical data, you can do it by simple function that has one input parameter and is returning false or true...
So here it goes...

Function F_numeric_val (i_value IN VARCHAR2) return boolean is
l_num NUMBER;
BEGIN
         l_num := to_number(i_value);
        return(true);
exception
   when others then
   return(false);
end;

So basically what this function do?
You have some input on front end, let's say it is 123456A and you want to check it's number consistency.
So when front-end passes you that input you call Function F_numeric_val and check its content.
In this case function will return false value to input 123456A
example;

I have program that needs to check are there any letters from third place of input till it's end.
If there are any letters I don't want to continue my code.

declare
   l_input varchar2(32767) := 'BJ1234567890        '; -- setting inputs default value
   l_errmsg varchar2(32767) := null;
begin
/* substr command means that you're checking some range of chars from desired sign. let's say we have input from l_input parameter. We string it with substr('BJ1234567890       ',3,32767) and we get  '1234567890    ' values from third sign to end of string . If we add trim before input, it will crop spaces before and after string, but not inside the string. Substr(trim('BJ1234567890       '),3,32767) this expression would return 1234567890 value but we just want to see is there any alpha characters after third sign  and we don't mind if there are any spaces so we trim them */

if not f_numeric_val(substr(trim(l_input),3,32767)) then
      l_errmsg :=( 'You don''t have number in you''re data on right places');
      return;
   else
      -- do some code here :)
      l_errmsg := 'all ok';
   end if;
exception
    when others then
       raise_application_error(-20001,'Program stopped '||sqlcode);
end;

 This code will return all ok message....
 Simple :)

Read More