UDF That Checks Input Value is Number or Not

Here is a sample function that you can write in Oracle (PL/SQL). You can use it dynamically wherever required in the application program. Also, these save your 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.