Using bind variables in SQL Plus with more than one row returned?

Question

This is a stupid problem, but I can't seem to get around it. I have a query that's causing trouble in an OCI program, so I want to run it manually in SQL*Plus to check if there is any difference there. This is the query:

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

I wanted to bind the comment_id to the value 3052753, so I did the following:

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

which gives this error:

ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

Now, I'm already unhappy because I don't want to be radically changing this query, but anyway I forge ahead and come up with this (INTO and UNIONs don't go together so smoothly):

DECLARE
 comment_id number := 3052753;
 x_label VARCHAR2(50);
 x_url VARCHAR2(500);
 x_item number;
 x_thing VARCHAR2(50);
BEGIN
select label, url, item_id, thing into x_label, x_url, x_item, x_thing from (
select e.label  ,
                       e.url,
                       i.item_id,
                       'multi' as thing  
                from cr_items i, cr_extlinks e
                where i.parent_id = :comment_id
                and e.extlink_id = i.item_id
               UNION
                select null  ,
                       utl_raw.cast_to_varchar2(DBMS_LOB.SUBSTR(r.content, 2000, 1))  as url,
                       r.item_id,
                       'single' as thing 
                from cr_revisions r
                where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)) ;
END;
/

but now, of course because I'm returning more than 1 row, I get the utterly predictable

ORA-01422: exact fetch returns more than requested number of rows

Now, I can go ahead and start using cursors etc, but my little query is getting more and more distorted from its original self. All I wanted to do was to check if the query ran ok with that value of comment_id. Of course, I can just hardcode the comment_id into the query, and that works fine. But it also works fine in the OCI so I'm to reproduce in SQL*PLus the issue with bind variables that I'm seeing in the OCI code. But why is it such a struggle to do this in SQL*Plus? Have I missed something really obvious?

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

Answer 1

Similar to @Glenn's approach, but you can declare a bind variable in SQL*Plus and use it in a plain SQL query. First declare it with the var[iable] command:

variable comment_id number;

Then set it with the exec[ute] command, which is essentially an anonymous block:

execute :comment_id := 3052753;

Then run your original query with the :comment_id references, and no BEGIN or END:

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

I don't think there's much functional difference between the two approaches beyond personal preference, and both also work in SQL Developer (when run as a script). I find this easier when running SQL copied from a Pro*C file which already uses the : bind form, purely because you don't have to modify the code at all.

---

Incidentally, you can write:

where r.revision_id = ( select content_item.get_latest_revision(:comment_id) from dual)

without the extra select, as:

where r.revision_id = content_item.get_latest_revision(:comment_id)

Answer 2

Rather than creating an anonymous block, you probably want to define an environment variable in sqlplus:

DEFINE comment_id = 3052753

Then refer to &comment_id in your query.

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