Often, data in Oracle applications is stored in extended columns known as Descriptive Flex Field (DFF) columns. These are character columns which you can map to store any kind of information. On the front end, it is possible to apply validations like numeric, date, or character but the ultimate values that get stored inside these flex columns are usually just character strings.

This causes trouble when it comes time for reporting. Though it’s not a problem if you just want to display the values stored in the column, the pain starts when you want to do some logic on top of those numeric values.

This problem can be ameliorated by using the to_number function that Oracle provides?however, this assumes that the column contains only numeric values and no “white space,” or, “non-numeric” characters. If the column contains non-numeric characters, the to_number function will bomb right in your face. Descriptive Flex Fields are context dependent, which means that each attribute column can store different data types for a given row. So, for row1, the attribute1 column might store a numeric value, row2 might store a date value and so on.

To deal with the above situation, create a new generic function:

function get_number(p_val varchar2) return number isl_value number;beginl_value := to_number(p_val);return l_value;exceptionwhen others thenreturn -1;end;

The above wrapper function tries to convert the character value into a number. If it succeeds, then it returns the value. If it fails, it returns a -1.

You can modify this function to return any value depending on the requirement?for example, to return a zero value instead of -1. This example uses -1 to highlight any data issues. If you return a zero value, there is a possibility that the user might overlook the data error. Depending on the situation, you should return a value which will tell the user that the data is not correct?like a huge negative value.

You can also enhance this function to strip out non-numeric values (like spaces, etc.) using the ltrim, rtrim, or replace functions but take care not to overload the wrapper function with more logic and calculations as this will effect the performance of your reports.