这可能是Oracle错误吗?还是我遗漏了一些东西? [英] Is this a possible Oracle bug or am I missing something?

问题描述

数据库是Oracle 10.2.0.1.0-64位在Red Hat Enterprise Linux ES发行版4(Nahant Update 8)上运行

在SQL * Plus中，以下代码可以完美运行:

var comment_id number
exec :comment_id := 3052753
select e.label as doc_name,
           e.url,
           i.item_id,
           'multi' as form_type
    from cr_items i, cr_extlinks e
    where i.parent_id = :comment_id
    and e.extlink_id = i.item_id
   UNION
    select null as doc_name,
           utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
           r.item_id,
           'single' as form_type
    from cr_revisions r
    where r.revision_id = content_item.get_latest_revision(:comment_id);
/

在这种情况下，它返回2行，而UNION的每个部分都返回1行. 如果我按如下方式更改对content_item.get_latest_revision的调用，则会中断如下:

var comment_id number
exec :comment_id := 3052753
select e.label as doc_name,
          e.url,
           i.item_id,
           'multi' as form_type
    from cr_items i, cr_extlinks e
    where i.parent_id = :comment_id
    and e.extlink_id = i.item_id
   UNION
    select null as doc_name,
           utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
           r.item_id,
           'single' as form_type
    from cr_revisions r
    where r.revision_id = ( select content_item.get_latest_revision(:comment_id) 
                          from dual);

/

错误:

SQL> where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)
                                               *
ERROR at line 14:
ORA-00904: : invalid identifier

现在，对这段SQL真正的疯狂是，上面的第二个示例是打破了 only 的情况.例如，如果我在上面的示例2中进行查询，只是从联合的两边删除了doc_name字段，一切突然又可以正常工作了.或者，如果我删除utl_raw.cast_to_varchar2位或联合本身(并分别运行每个部分).正是UNION，AND子句和函数调用的精确组合中断了.

有人建议说它可能是错误6038461，带有UNION和快速DUAL子查询的SQL错误结果"，但我认为不合适.

有人知道第二次查询怎么了吗?

PS，我应该补充一点，在TOAD中没有错误-查询运行正常...

解决方案

我认为它不起作用，因为您有一个空行. SQLPlus讨厌他们.

Database is Oracle 10.2.0.1.0 - 64bit running on Red Hat Enterprise Linux ES release 4 (Nahant Update 8)

In SQL*Plus following code run perfectly:

var comment_id number
exec :comment_id := 3052753
select e.label as doc_name,
           e.url,
           i.item_id,
           'multi' as form_type
    from cr_items i, cr_extlinks e
    where i.parent_id = :comment_id
    and e.extlink_id = i.item_id
   UNION
    select null as doc_name,
           utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
           r.item_id,
           'single' as form_type
    from cr_revisions r
    where r.revision_id = content_item.get_latest_revision(:comment_id);
/

In this case, it returns 2 rows, 1 from each part of the UNION. If I change the call to content_item.get_latest_revision as follows, it breaks as follows:

var comment_id number
exec :comment_id := 3052753
select e.label as doc_name,
          e.url,
           i.item_id,
           'multi' as form_type
    from cr_items i, cr_extlinks e
    where i.parent_id = :comment_id
    and e.extlink_id = i.item_id
   UNION
    select null as doc_name,
           utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
           r.item_id,
           'single' as form_type
    from cr_revisions r
    where r.revision_id = ( select content_item.get_latest_revision(:comment_id) 
                          from dual);

/

The error:

SQL> where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)
                                               *
ERROR at line 14:
ORA-00904: : invalid identifier

Now, what's really crazy about this piece of SQL is that the 2nd example above is the only case that breaks. For example, if I take the query in example 2 above and just remove the doc_name field from both sides of the union everything suddenly works again. Or if I remove the utl_raw.cast_to_varchar2 bit, or the union itself (and run each part separately). It's just that precise combination of UNION, AND clauses and function call that breaks.

Somebody suggested that it might be bug 6038461, 'Wrong results from SQL with UNION and a fast DUAL subquery', but I don't think that's a good fit.

Anyone have a clue what's up with the 2nd query?

PS I should add that in TOAD there is no error - the queries run fine...

解决方案

I think it doesn't work because you have an empty line; SQLPlus hate them.

这篇关于这可能是Oracle错误吗?还是我遗漏了一些东西?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！