Oracle "ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP" on INSERT INTO but not SELECT

Question

Running the following command in SQL Developer:

insert into table2
select *
from   table1
where  id_ in (select fileid
               from   table3
               where  status in ('DELETED', 'TODELETE')
               and    softdeletedate < to_date('11/08/2015 01:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'))
and    id_ not in (select id_ from table2);

Results in this error:

ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

If I remove the first part (insert into table2), the select command runs fine and as expected does not return any records.

Table1 and table2 have 70+ columns of VARCHAR2, TIMESTAMP and NUMBER.

Table1 and table2 are created by an APP which goes through table1 columns and creates table2, the only difference being that table1 has many indexes while table2 has a single index.

I have verified that table1 and table2 have the same exact columns and data types. However, the output of "desc table1" and "desc table2" lists the columns in different order, but they all do match.

When I run the command on other tables which were created in similar fashion but have fewer columns (they still have the same column types), the insert works and displays no error. Running "desc" command against those tables lists the columns in the same order.

I went through many Google searches for the error but could not find an answer. My Oracle version is 11g.

Any idea why this error is returned and how to get around it?

Answer 1

You say that your two tables have the same columns, but not in the same order - that is highly likely to be the cause of your issue as, if you don't specify the columns yourself, you're relying on the default column ordering (eg. the first col of table1 will match to the first col of table2, etc.). If your column orders in both tables don't match then don't be surprised when you run into issues such as clashing datatypes!

If I were you, I would explicitly state the columns being selected and inserted into, rather than relying on the default column ordering.

So, it should look something like:

insert into table2 (id_,
                    other_col_1,
                    other_col_2,
                    ...)
select id_,
       other_col_1,
       other_col_2,
       ...
from   table1
where  id_ in (select fileid
               from   table3
               where  status in ('DELETED', 'TODELETE')
               and    softdeletedate < to_date('11/08/2015 01:00:00 AM', 'mm/dd/yyyy hh:mi:ss AM'))
and    id_ not in (select id_ from table2);