Convert DFF (Descriptive Flex field) to Number

Often data in Oracle Applications is stored in extended columns known as Descriptive Flex field columns. These are character columns which you can map it to store any kind of information. On the front end, it is possible to apply validations like numeric, date or character but the ultimate value that gets stored inside these flex columns is just character strings.

The trouble comes during the time of reporting. Its not a problem if you just want to display the value stored in the column. The pain starts when you want to do some logic on top of those numeric or date values. In this article we just consider numeric values and how can they be utilized.

The problem can be simplified by using the to_number function that Oracle provides but this assumes that the column infact has *ONLY NUMERIC* values and no “white space,” or “non-numeric” characters in it. If it does have non-numeric characters then the to_number function is going to bomb right in your face. Descriptive Flex fields are context dependent ie. each attribute column can store different data types for a given row, so for row 1 the attribute1 column might store a numeric value, row2 might store a date value and so on…

To deal with the above situation, we can create a new generic function as below

function get_number(p_val varchar2) return number is
l_value number;
l_value := to_number(p_val);
return l_value;
when others then
return -1;

In the above wrapper function, it tries to convert the character value into number, if it succeeds then returns the value else returns a -1.

So you can modify this function to return any value depending on the requirement e.g return a zero value instead of -1. The reason we chose -1 is to highlight any data issues. If we return a zero value then there is a possibility that the user might overlook the data error. So depending on the situation return a value which would tell the user that the data is not correct e.g like a huge negative value.

You can enhance the function to strip out non-numeric values like spaces etc using ltrim, rtrim or replace functions but caution should be exercised to not overload this function with more logic and calculations as it will effect the performance of your reports.