How to avoid Invalid Number on implicit conversion

Question

I know Oracle (I am working on Oracle11gR2) can convert data-types into each other implicitly if it is able to do so. For example if I try to insert a number into varchar area, it implicitly converts number into a varchar or vice versa a varchar would be converted into a number if it is a valid number.

Also Oracle does this implicit conversion on joins. If I join a number column to a varchar column, it will implicitly convert the varchar into number and complete the query. But if there is any invalid numbers in the varchar column it would throw an ORA-01722 : Invalid Number You can see this setting by running the codes below:

create table test_1(
  id varchar2(20),
  val number);

create table test_2(
  id number,
  name varchar2(20)
  );

insert into test_1 values ('abc', 10);
insert into test_1 values ('1', 11);
insert into test_2 values (1,'abc');
insert into test_2 values (2,'def');

-- Throw error
select 
  * 
from
 test_1, test_2
where
 test_1.id = test_2.id

-- work
select 
  test_1.id, val, name 
from
 test_1, test_2
where
 test_1.id = test_2.id
 and test_1.id = '1'

You can also see and run the samples on : http://sqlfiddle.com/#!4/fdce3/9/0

Now my question, is there any option or configuration parameter to force Oracle to make this implicit conversion into varchar instead of number? Or exactly see the source of error of invalid number (which column or which join)?

I know that I can explicitly do the conversion to avoid error. Like below, but I do not want that as solution.

select 
  * 
from
 test_1, test_2
where
 test_1.id = to_char(test_2.id)

You can visit also http://sqlfiddle.com/#!4/fdce3/10 to see the code above works properly.

Thanks

Answer 1

" can explicitly do the conversion to avoid error ... I do not want that as solution".

So you don't want to use good practice? Why on earth not? Although, if you're comparing a numeric column to a string column perhaps that horse has already bolted.

Anyhoo. The ORA-01722 is the database telling us some useful information: it's telling us 'you were expecting test1.id to be numeric but you should know it contains non-numeric values'. Now we are in a position to resolve the error.

First off, is this right? Do we expect test1.id to contain non-numeric values? If the answer is 'no' then we have a data quality issue (not to mention a data modelling issue) and we should raise a bug.

But if we know test1.id can legitimately contain non-numeric strings then we have to write our query accordingly. Which means we need to apply to_char() on the other side of the WHERE clause. This not only handles the error it waves a little flag to some future co-worker looking at the query: 'by the way, test1.id contains non-numeric values: crazy, huh?'

"is there any option or configuration parameter to force Oracle to make this implicit conversion into varchar instead of number?"

Suppressing exceptions is always a bad idea. We need to know when something is amiss so we can handle it properly.

"is there any option or configuration parameter to ... exactly see the source of error of invalid number (which column or which join)?"

Alas no. To be fair, it's not unreasonable to expect that you are familiar with the data model you're working with. But if you don't know which columns are causing the problem there isn't an alternative to going through the data dictionary (i.e. all_tab_columns).

If you want to find out which rows in your string column contain non-numeric values then you will need to query it. In Oracle 12cR2 there's the highly neat VALIDATE_CONVERSION() function. Find out more. If you're using an earlier version then you need to write your own, such as this function in another StackOverflow thread.

---

"I am working in an ETL team and I do not have any control over source data model."

Indeed, one of biggest problems with ETL is handling bad data from the source system. There are various approaches, such as loading the data into staging tables, validating it, then either publishing it to final tables or quarantined tables depending on its quality. Alternatively we can use DML error logging; that at least will isolate the records which throw the exceptions. Find out more.

Answer 2

You don't need any special tricks, just join on only those id's that are "numbers" by matching using a regex for "only digits":

select 
  test_1.id, val, name 
from
 test_1, test_2
where
 test_1.id = test_2.id
 and regexp_like (test_1.id, '^[0-9]+$')

See regexp_like documentation.