The blog for Oracle Data Integrator ( ODI )

IS_NUMBER (and IS_CHAR) at Oracle? A workaround…

Hi Friends,

Right now I’m in São Paulo airport waiting for a flying that is very delayed! With some time free, let me write about something that I want to publish there is a long time….

Surprise! Isn’t an Oracle Data Integrator issue! hehehehehehe……

I really like to work with ORACLE RDBMS but doesn’t exist a “Is Number” function is, in my opnion, a lack of funcionality that I can’t understand.

Every time that is necessary to check if the returned value from a column (or a substring from it ) is number, I created a database (PL/SQL) function that return true or false and then uses it in the query.

In this way works, but if I want to do the same in a Microsoft SQL Server, there is a “native” function to do it.

The big problem is that, sometimes, you don’t have an user that can create PL/SQL procedures and, then, you can’t do this type of validation.

For solve this, I created the following technique:

A single SQL query that can return if a column is number or alphanumeric

select case when trim(TRANSLATE('1234f','0123456789-,.', ' ')) is null
            then 'numeric'
            else 'alpha'
from dual

Where ‘1234f’ should be substituted by the column that you wish to validate.

The principle is very simple, if  the numbers from a string are removed and the result is different of null then the string isn’t numeric. The Trim funcion eliminates any remained space.

Plus, it is pretty usefull when there some non-visible ASCII char  in a column.

Well, I couldn’t let to show a use of this in ODI.

When there is a text file to be loaded and it is necessary to check (CKM) if the columns are with the right data (thru ODI constraint) this single query can send to the error table (E$) any record that has the wrong data.

See you around!


Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.