Как из запроса вывести данные в табличном виде (table of tbl%rowtype)?

Question

Не получается вывести данные возвращаемые табличной функцией.

Тестовые данные:

create table tst (v1 varchar2(100), v2 varchar2(100))
/
insert all 
    into tst values ('XXX3', 'YYY1')
    into tst values ('XXX2', 'YYY2')
    into tst values ('XXX1', 'YYY3')
select * from dual
/
commit;

Так выглядит пакет:

create or replace package pkg_tst as
    type t_rec is table of tst%rowtype;
    function get_recs return t_rec;
end;
/
create or replace package body  pkg_tst as
    function get_recs return t_rec is
        ret_val t_rec;
    begin
        select * 
        bulk collect into ret_val
        from tst;
            
        return ret_val;
    end;
end;
/

Но следующий запрос возвращает ошибку (неверный тип данных):

select * from table(pkg_tst.get_recs);

ORA-00902: invalid datatype

В чем может быть причина?

_{Вопрос был задан участником @Анатолий Эрнст, но был удалён автором (виден от 10к реп.).}

Answer 1

Начиная с выпуска 12c стало возможным использовать локальные PL/SQL коллекции в SQL запросах. Но это возможно только для статических запросов в PL/SQL контексте. Для динамических SQL запросов надо по прежнему пользоваться конвейерными (pipelined) функциями.

В коде пакета из вопроса надо добавить одну функцию (так же на db<>fiddle):

create or replace package pkg_tst as
    type t_recs is table of tst%rowtype;
    function get_recstbl return t_recs;
    function get_recsppl return t_recs pipelined;
end;
/
create or replace package body pkg_tst as
    function get_recstbl return t_recs is
        ret t_recs;
    begin
        select * 
        bulk collect into ret from tst;
        return ret;
    end;
    function get_recsppl return t_recs pipelined is
    begin
        for r in (select * from tst) loop
            pipe row (r);
        end loop;
        return;
    end;
end;
/

Примеры использования и результаты:

select * from table (pkg_tst.get_recsppl) 
/
V1    V2   
----- -----
XXX3  YYY1 
XXX2  YYY2 
XXX1  YYY3 

var rc refcursor 
declare
    recs pkg_tst.t_recs := pkg_tst.get_recstbl;
begin 
    open :rc for select * from table (recs); 
end;
/
V1    V2   
----- -----
XXX3  YYY1 
XXX2  YYY2 
XXX1  YYY3 

select * from table (pkg_tst.get_recstbl)
/ 
Error report -
SQL Error: ORA-00902: invalid datatype

БЛОГ НА HUSL

Как из запроса вывести данные в табличном виде (table of tbl%rowtype)?

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