Почему при вызове пакетной табличной функции возникает ошибка: "PLS-00642: local collection types not allowed in SQL statements"?

Question

Допустим, есть такой пакет:

create or replace package pack as
    type rec is record (id int, name varchar2 (8));
    type arr is table of rec;
    function getarr return arr;   
end;    
/
create or replace package body pack as
    function getarr return arr is begin 
        return arr (rec (1, 'aaa'), rec (2, 'bbb'));
    end;    
end;    
/

Хочу использовать результат функции в связке с другой таблицей:

create table t1 (id) as select rownum from xmlTable ('1 to 3')
/
declare 
    ret pack.arr;
begin
    select a.* bulk collect into ret
    from t1
    join table (pack.getarr()) a on a.id = t1.id; 
end;
/

Но этот код не компилируется:

Error report -   
ORA-06550: line 7, column 22:  
PLS-00642: local collection types not allowed in SQL statements   
ORA-06550: line 7, column 10:   
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

Почему возникает эта ошибка?

Как описано в оф. документации table_collection_expression, так можно развернуть коллекцию в операторе table:

The collection_expression can be a subquery, a column, a function, or a collection constructor.

Сообщение об ошибке намекает, что локальные PL/SQL коллекции нельзя использовть в SQL запросах. Но в этом ответе говорится, что начиная с 12c их можно использовть, если они объявлены в специификации пакета. Стоит актуальная версия 19c.

Answer 1

Упомянутая подглава документации table_collection_expression описывает только поведение с типами данных объявленными на уровне схемы, и с ними будет всё работать.

На использование локальных PL/SQL коллекций в SQL контексте есть ряд ограничений. Приминительно к вопросу, в актуальной документации в подглаве 6.4.6 Querying a Collection сказано:

Note: In SQL contexts, you cannot use a function whose return type was declared in a package specification.

Невозможно использовать функции, тип возврата которых объявлен в спецификации пакета:

select pack.getarr() arr from dual
/
SQL Error: ORA-00902: invalid datatype

Функции нельзя, а переменные с тем же типом данных - можно. Это и будет решением:

var rc refcursor
declare 
    arr pack.arr := pack.getarr();
begin
    open :rc for
    select a.*
    from t1
    join table (arr) a on a.id = t1.id; 
end;
/

        ID NAME    
---------- --------
         1 aaa     
         2 bbb

Answer 2

Если цель объявления пользовательских типов данных в пакете, только их видимость в заданном пространстве имён, то можно создать аналогичные SQL типы данных, создав для них свою собственную схему, например - udt (user defined type owner).

Использование SQLтипов данных даст преимушество, что они не имеет ограничений (даже для более старых версий), обладают большей гибкостью и расширяемостью.

Простой пример:

create or replace type udt.objt is object (id int, name varchar2 (8))
/
create or replace type udt.arrt is table of udt.objt
/
create or replace function udt.getarr return udt.arrt is 
begin 
    return udt.arrt (udt.objt (1, 'aaa'), udt.objt (2, 'bbb'));
end;    
/

select udt.getarr () from dual
/

ARR(ID, NAME)
-------------------------------------
ARRT(OBJT(1, 'aaa'), OBJT(2, 'bbb'))

var rc refcursor
begin
    open :rc for select * from udt.getarr (); 
end;
/

        ID NAME            
---------- ----------------
         1 aaa             
         2 bbb

БЛОГ НА HUSL

Почему при вызове пакетной табличной функции возникает ошибка: "PLS-00642: local collection types not allowed in SQL statements"?

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