Is This A Possible Oracle Bug Or Am I Missing Something?

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 :comme

Solution 1:

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 := 3052753select 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
    selectnullas 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 := 3052753select 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
    selectnullas 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
    whereEXISTS (select'x'from dual
                    where content_item.get_latest_revision(:comment_id) =r.revision_id);


/

Solution 2:

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