Database is Oracle 10.2.0.1.0 - 64bit 运行在 Red Hat Enterprise Linux ES Release 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 行,其中 1 行来自 UNION 的每个部分。如果我按如下方式将调用更改为 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真正疯狂的是,上面的第二个示例是唯一一个中断的情况。例如,如果我采用上面示例 2 中的查询,只是从联合的两侧删除doc_name字段,一切突然再次工作。或者,如果我删除 utl_raw.cast_to_varchar2 位或联合本身(并分别运行每个部分)。只是 UNION、AND 子句和函数调用的精确组合中断了。
有人建议这可能是错误6038461,"使用 UNION 和快速 DUAL 子查询的 SQL 的错误结果",但我认为这不太合适。
有人知道第二个查询是怎么回事吗?
PS 我应该补充一点,在 TOAD 中没有错误 - 查询运行良好......
不是AND/WHERE column = (SELECT column....)
的忠实粉丝,从整体上讲,最好写AND/WHERE column IN (SELECT column...)
。但在您的情况下,子查询中似乎不存在多行或多列的可能性。怎么样——
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 IN ( select content_item.get_latest_revision(:comment_id)
from dual);
/
或
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 EXISTS (select 'x'
from dual
where content_item.get_latest_revision(:comment_id) =r.revision_id);
/
我认为它不起作用,因为您有一个空行;SQLPlus讨厌他们。