Как правильно создать локальную коллекцию для запроса с оператором TABLE?

Question

Участник @максим ильин спрашивал в этой теме (была удалена автором).

Пытаюсь создать локальную коллекцию с элементами ключ-значение, чтобы потом её использовать в SQL запросе с TABLE оператором как обычную таблицу.

Пробую так:

declare
    type tabtype is table of maptype index by binary_integer;
    tab tabtype;
    type valuesarr is table of varchar2 (16);
    res valuesarr;
begin
    select t.key, t.val bulk collect into tab
    from (
        select 'key'||rownum key, 'val'||rownum val
        from dual connect by level<=3) t;
        
    select val bulk collect into res
    from table (tab);
    dbms_output.put_line ('count of values='||res.count);
end;
/

Но получаю следующую ошибку:

Error report -
ORA-06550: Строка 13, столбец 17:
PLS-00382: выражение неправильного типа
ORA-06550: Строка 13, столбец 10: PL/SQL: ORA-22905: невозможно получить доступ к строкам элемента, не являющегося вложенной таблицей

Почему эта ошибка, и как правильно объявить локальную коллекцию?

Answer 1

Эта ошибка возникает, если SQL парсеру неизвестен тип данных, переданной ему переменной связывания.

Локальные коллекции, включая их элементы, должны быть заранее объявлены в спецификации пакета. При компиляции пакета, PL/SQL компилятор неявно создаст структуры, аналогичные типам данных создаваемых явно выражениями CREATE TYPE [...]. Эти структуры будут известны в SQL контексте во время парсинга запроса. Так как анонимный блок выполняется единоразово и не имеет имени, то никаках дополнительных структур для локальных коллекций и их элементов PL/SQL компилятор не создаёт.

Воспроизводимый пример, как нужно сделать:

create or replace package pack as
    type rectype is record (key varchar2(8), val varchar2 (16));
    type tabtype is table of rectype index by binary_integer;
end;
/

declare 
    tab pack.tabtype;
    type valuesarr is table of varchar2 (16);
    res valuesarr;
begin
    select t.key, t.val bulk collect into tab
    from (
        select 'key'||rownum key, 'val'||rownum val
        from dual connect by level<=3) t;

    select val bulk collect into res 
    from table (tab);
    dbms_output.put_line ('count of values='||res.count);
end;
/
PL/SQL procedure successfully completed.

count of values=3

БЛОГ НА HUSL

Как правильно создать локальную коллекцию для запроса с оператором TABLE?

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