Select 抛出 ORA-01858 异常 [英] Select throws an ORA-01858 exception

问题描述

使用以下查询，抛出 Oracle 异常.但是，我不明白为什么.任何人都可以透露一些信息吗?

With the following query, the Oracle exception is thrown. However, I cant see why. Can anyone shed some light?

select visit_id, to_date(response, 'DD/MM/YYYY') as convertedDate from 
(
select *
from dat_results_ext
where item_name = 'CALLBACKDATE'
)
where to_date(response, 'DD/MM/YYYY')  > sysdate

我理解异常意味着它试图转换响应"字段，但它遇到了非数字.问题是它应该带回的行的所有内容都采用正确的格式.

I understand the exception to be mean that its trying to convert the 'response' field, but it is meeting a non-numeric. Problem is the row that it should bring back has everything in the right format.

'response' 字段是一个 varchar 字段，但所有返回带有 'item_name = 'CALLBACKDATE' 子句的行都是正确的格式.

The 'response' field is a varchar field, but all the rows coming back with the 'item_name = 'CALLBACKDATE' clause are all of the correct format.

有什么想法吗?

推荐答案

优化器可以在尝试找到最佳执行计划之前重写您的查询.在您的情况下，由于您没有阻止优化器执行此操作的提示，因此它可能会 取消嵌套您的子查询 并将您的查询重写为:

The optimizer can rewrite your query before trying to find the best execution plan. In your case since you have no hints that would prevent the optimizer from doing this, it will probably unnest your subquery and rewrite your query as:

SELECT *
  FROM dat_results_ext
 WHERE item_name = 'CALLBACKDATE'
   AND to_date(response, 'DD/MM/YYYY') > sysdate

您无法控制 WHERE 子句中语句的计算顺序，因此 Oracle 可能首先在不可转换为日期的行上计算 to_date 函数，因此错误.

You don't have control over the order of evaluation of the statements in the WHERE clause, so Oracle probably evaluated the to_date function first on a row that is not convertible to a date, hence the error.

我看到有两个选项可以强制 Oracle 按照您想要的顺序评估语句:

I see two options to force Oracle to evaluate the statements in the order you want:

1. 使用rownum.Rownum 将具体化子查询，防止 Oracle 将其与外部查询合并:

1. Use rownum. Rownum will materialize the subquery, preventing Oracle from merging it with the outer query:

SELECT visit_id, to_date(response, 'DD/MM/YYYY') AS convertedDate
  FROM (SELECT r.*, 
               rownum /* will materialize the subquery */
          FROM dat_results_ext r
         WHERE item_name = 'CALLBACKDATE')
 WHERE to_date(response, 'DD/MM/YYYY') > sysdate

使用NO_MERGE 提示:

SELECT visit_id, to_date(response, 'DD/MM/YYYY') AS convertedDate
  FROM (SELECT /*+ NO_MERGE */ *
          FROM dat_results_ext
         WHERE item_name = 'CALLBACKDATE')
 WHERE to_date(response, 'DD/MM/YYYY') > sysdate

这篇关于Select 抛出 ORA-01858 异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！