Oracle“ORA-00932:不一致的数据类型:预期的数字得到了时间戳”在插入但没有选择

Running the following command in SQL Developer:

在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

ORA-00932:不一致的数据类型:期望数得到时间戳

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

如果我删除第一部分(插入到table2中)，select命令运行良好，并且按照预期不会返回任何记录。

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

表1和表2有70多列的VARCHAR2、时间戳和数字。

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.

表1和表2是由一个应用程序创建的，它遍历表1列并创建表2，唯一的区别是表1有很多索引，而表2只有一个索引。

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.

我已经验证了表1和表2具有相同的列和数据类型。然而，“desc表1”和“desc表2”的输出以不同的顺序列出了列，但它们都是匹配的。

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.

当我在以类似方式创建但列数较少(它们仍然具有相同的列类型)的其他表上运行该命令时，insert可以正常工作，并且不会显示错误。针对这些表运行“desc”命令，以相同的顺序列出列。

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

我在谷歌上搜索了很多次，但没有找到答案。我的Oracle版本是11g。

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

知道为什么会返回这个错误以及如何解决它吗?

1 个解决方案

#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 *ing datatypes!

您说您的两个表具有相同的列，但是顺序不同——这很可能是您问题的原因，因为如果您自己不指定列，那么您将依赖于默认的列排序(例如)。表1的第一个col会匹配表2的第一个col等)。如果两个表中的列订单不匹配，那么当遇到冲突数据类型之类的问题时，不要感到惊讶!

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);

#1