Here is Logic to Write UDF to Check Input is Number or not

Here is a logic to write udf in PL/SQL. Udfs you can use wherever required in the application program to save time.

A function that needs input and it validates it and returns the output. The learning item is – function always returns a value.

Here, the below function checks the input value supplied is Varchar2 of number or not. If it is not a number, it displays NULL. If the input value is a number, then it displays a number.

PL/SQL code to create a function

CREATE OR REPLACE FUNCTION to_number_or_null (
aiv_number IN     varchar2 )
return     number is 
    return to_number(aiv_number);
   when OTHERS then
     return NULL;
end to_number_or_null;

Here each statement ends with a semi-colon. The return value is the number. If the input is not a number, the logic goes to an exception where it returns NULL.

How to test the function

I used here DUAL to test the function in SQL developer. When you give a number, it displays the same.

select to_number_or_null('2') from DUAL;

The output is:


When you give alpha value, it displays as NULL

select to_number_or_null('A') from DUAL;

The output is:



  • When you find return in PL/SQL code, you can say – that is definition of a function
  • A function always returns a value

Related posts

Get new content delivered directly to your inbox.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.