Использование переменных связывания в SQL Plus с возвращением более одной строки?

Question

Это глупая проблема, но я не могу ее обойти. У меня есть запрос, который вызывает проблемы в программе OCI, поэтому я хочу запустить его вручную в SQL*Plus, чтобы проверить, есть ли там какая-либо разница. Это запрос:

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;

Я хотел связать comment_id со значением 3052753, поэтому я сделал следующее:

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

который дает эту ошибку:

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

Теперь я уже недоволен, потому что я не хочу радикально менять этот запрос, но в любом случае я продвигаюсь вперед и придумываю это (INTO и UNIONs не идут так гладко):

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

но теперь, конечно, потому что я возвращаю более 1 строки, я получаю совершенно предсказуемый

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

Теперь я могу пойти дальше и начать использовать курсоры и т. Д., Но мой маленький запрос все больше и больше искажается от его первоначального "я". Все, что я хотел сделать, это проверить, нормально ли выполняется запрос с этим значением comment_id. Конечно, я могу просто жестко закодировать comment_id в запросе, и это прекрасно работает. Но он также отлично работает в OCI, поэтому я должен воспроизвести в SQL*PLus проблему с переменными связывания, которую я вижу в коде OCI. Но почему это так сложно сделать в SQL*Plus? Я пропустил что-то действительно очевидное?

База данных - Oracle 10.2.0.1.0 - 64-разрядная версия, работающая на Red Hat Enterprise Linux ES выпуск 4 (Nahant Update 8)

Answer 1

Подобно подходу @Glenn, но вы можете объявить переменную связывания в SQL*Plus и использовать ее в простом запросе SQL. Сначала объявите это с var[iable] команда:

variable comment_id number;

Затем установите его с exec[ute] команда, которая по сути является анонимным блоком:

execute :comment_id := 3052753;

Затем запустите исходный запрос с :comment_id ссылки, и нет BEGIN или же 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);

Я не думаю, что между этими двумя подходами есть большая функциональная разница, помимо личных предпочтений, и оба они также работают в SQL Developer (при запуске в виде скрипта). Я нахожу это проще, когда запускаю SQL, скопированный из файла Pro*C, который уже использует : связывать форму, просто потому, что вам не нужно изменять код вообще.

---

Кстати, вы можете написать:

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

без дополнительного select, как:

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

Answer 2

Вместо того, чтобы создавать анонимный блок, вы, вероятно, захотите определить переменную окружения в sqlplus:

DEFINE comment_id = 3052753

Затем обратитесь к &comment_id в вашем запросе.

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