Как открыть курсор для вложенной таблицы с элементами %ROWTYPE?

Question

Пробую так:

declare
    cursor cur0 is select no, dt from data123;
    type TempTabTyp0 is table of cur0%ROWTYPE index by pls_integer;
    temp_tab0 TempTabTyp0; 
    lrc sys_refcursor; 
begin
    open cur0;
    fetch cur0 bulk collect into temp_tab0;
    close cur0;
    for rec in 1 .. temp_tab0.count loop 
        temp_tab0(rec).no := 'new value' || temp_tab0(rec).no;
    end loop;  
    open lrc for select * from table (temp_tab0);
end;

Но получаю ошибку:

Error report -
ORA-06550: line 13, column 39:
PLS-00382: expression is of wrong type

Без последней строки всё работает.

Как тогда можно открыть sys_refcursor для вложенной таблицы temp_tab0?

_{Свободный перевод вопроса How can i select data from table of %ROWTYPE to refcursor? от участника @Sergei}

Answer 1

Причина ошибки - объявленный в анонимном блоке тип данных неизвестен в SQL контексте.

Достаточно обернуть имеющийся код в пакет. Вот так:

create table data123 (no, dt) as
    select cast ('abc' as varchar2 (30)), date'2020-09-21' from dual
/
create or replace package pack1 as
    cursor cur0 is select no, dt from data123;
    type TempTabTyp0 is table of cur0%rowtype index by pls_integer;
    procedure getcur (rc out sys_refcursor);
end;
/
create or replace package body pack1 as
    procedure getcur (rc out sys_refcursor) is 
        temp_tab0 TempTabTyp0;
    begin
        open cur0;
        fetch cur0 bulk collect into temp_tab0;
        close cur0;
        for i in 1..temp_tab0.count loop 
            temp_tab0(i).no := 'new value '||temp_tab0(i).no;
        end loop;
        open rc for select * from table (temp_tab0);
    end;
end;
/

Запуск и результат:

var rc refcursor
exec pack1.getcur (:rc)

ATTR_1                         ATTR_2             
------------------------------ -------------------
new value abc                  2020-09-21 00:00:00

PS некорректные заголовки обусловленны багом, решение см. в этой теме.

БЛОГ НА HUSL

Как открыть курсор для вложенной таблицы с элементами %ROWTYPE?

Ответы (1 шт):