A while back, I was working with a client where the configuration for eBS hadn’t quite been documented correctly, and two developers were using the same descriptive flexfield for different purposes. The main problem with this, is that one developer was looking for numeric data, and the other was inserting a text string. Since the column definition is a VARCHAR2, both values were allowed, but when the first developer selected data with a TO_NUMBER function, it failed.
This is only to be expected, and once we realized what was going on, it was fairly straightforward to determine the problem – the issue was how to identify which records contained the non-numeric data, and which did not. Trying to select all records would fail and return an ORA-01722 error (invalid number) – so I knocked up the following function to identify the dodgy records:
CREATE OR REPLACE FUNCTION find_dodgy ( p_string IN VARCHAR2 ) AS v_num NUMBER; BEGIN v_num := p_string; RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END find_dodgy;
Running the following SQL statement finally identified the records that we were interested in:
SELECT * FROM oe_order_lines WHERE find_dodgy(ATTRIBUTE9) = 0;
From there, it was just a case of fixing the data – and updating the configuration documents to show what each flexfield meant!!