Finding non-numerics in a column

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!!

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close