Wednesday, 6 March 2013

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;
         l_num := to_number(i_value);
   when others then

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

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.

   l_input varchar2(32767) := 'BJ1234567890        '; -- setting inputs default value
   l_errmsg varchar2(32767) := null;
/* 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');
      -- do some code here :)
      l_errmsg := 'all ok';
   end if;
    when others then
       raise_application_error(-20001,'Program stopped '||sqlcode);
 This code will return all ok message....
 Simple :)


Post a Comment