3

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...

APC
  • 144,005
  • 19
  • 170
  • 281
TrojanName
  • 4,853
  • 5
  • 29
  • 41
  • Agree 6038461 doesn't look like a good match now; in the [previous question](http://stackoverflow.com/q/11155855/266304) it wasn't clear if it was erroring or giving wrong results. I'm pretty sure it's *a* bug, but can't see an exact match. You might need to raise a service request with Oracle. – Alex Poole Jun 22 '12 at 17:04
  • Total shot in the dark, given that the query is running fine in TOAD, what version of SQL Plus are you using? – Ian Carpenter Jun 22 '12 at 18:18
  • 1
    You're running an unpatched version of 10gR2. Why is that? – APC Jun 22 '12 at 19:48
  • @IanCarpenter thanks for the comments. Version of SQL*Plus: Release 10.2.0.1.0 – TrojanName Jun 25 '12 at 08:25
  • That is a puzzler, given that the query runs fine from TOAD, that says the database optimizer can deal with the statement just fine. What is different about the session from SQLPlus. I'd run an 10046 and 10053 event trace, and open a service request from Oracle, though they are going to recommend patching to a minimum of 10.2.0.4. – spencer7593 Jun 25 '12 at 22:24
  • I'd also suggest you try replacing DUAL with some other table, and include a ROWNUM=1 predicate. The reason I suggest an 10046 trace is to rule out the possibility its recursive SQL that's raising the exception. – spencer7593 Jun 25 '12 at 22:27

2 Answers2

0

Not a big fan of AND/WHERE column = (SELECT column....), holistically its better to write AND/WHERE column IN (SELECT column...). But in your case it does not look like there are possibility of multiple rows or columns in the sub query. How about-

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

/

OR

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


/
Anjan Biswas
  • 7,746
  • 5
  • 47
  • 77
0

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

JPB
  • 1