Использование коллекций, объявленных в спецификации PL/SQL пакетов, в SQL контексте

Существует ряд ограничений для типов коллекций обьявленных в PL/SQL пакетах (здесь и далее локальные коллекции), более подробно описанных в этой секции документации:

As of Oracle Database 12c, it is possible to bind values with PL/SQL-only data types to anonymous blocks (which are SQL statements), PL/SQL function calls in SQL queries and CALL statements, and the TABLE operator in SQL queries.

 Допустимо использовать локальные коллекции только в запросах с select. В запросах на изменение данных insert, update, delete и merge ограничения пока не сняты. В документе  генеральные направления развития продукта (см. слайд 24), ещё до выхода релиза 12c, этого никто и не обещал:

Cannot bind into insert, update, delete, or merge

 В зависимости от поставленной задачи, существуют различные решения.

1. Классика - объявить на уровне схемы

create or replace type numtab is table of number;

/

 

create or replace package pack is

function getnt (arg numtab) return numtab pipelined;

end pack;

/

create or replace package body pack 

is

function getnt (arg numtab) return numtab pipelined 

is

begin 

 

for i in 1..arg.count loop

pipe row (arg(i));

end loop;

 

return;

 

end;

 

end pack;

/

 И оба вызова из вопроса с удалением имени пакета в declare секции:

nt numtab := numtab (10,20,30);

 будут работать.

2. Использовать табличные функции

Как и ранее, возможно использовать в DML выражениях табличные функции  возвращающие как тип результата локальные вложенные таблицы. Но  передавать как аргумент функции такого типа, было и пока остаётся,  недопустимо:

create or replace package pack 

is

type numtab is table of number;

 

function getnt return numtab pipelined;

end pack;

/

create or replace package body pack 

is

function getnt return numtab pipelined 

is

nt numtab := numtab (10,20,30);

 

begin 

 

for i in 1..nt.count loop

pipe row (nt(i));

end loop;

 

return;

 

end;

 

end pack;

/

 Такой вызов будет работать:

begin

 

insert into t1

select rownum, column_value from table (pack.getnt);

 

end;

/

 3. Использовать PL/SQL bulk DML

Предположим, надо получить результаты вставки, например, id сгенерированного неявно. Оператор returning в массовой вставке из подзапроса select, не поддерживается. Выражение forall insert  пока единственный путь произвести изменения массово и вернуть результат  проведённых изменений, и здесь локальные типы коллекций очень даже  кстати:

drop table t1 purge;

create table t1 (id number generated always as identity primary key, col1 number);

create or replace package pack as

type numtab is table of number;

end pack;

/

declare 

 

sources pack.numtab := pack.numtab (10,20,30);

targets pack.numtab;

retids pack.numtab; 

 

begin

 

select * bulk collect into targets

from table (sources);

 

forall i in indices of targets insert into t1 (col1) values (targets (i))

returning id bulk collect into retids;

 

dbms_output.put_line (retids.count||' rows inserted.')

 

end;

/

 

3 rows inserted.

select * from t1;

ID COL1

---------- ----------

1 10

2 20

3 30

 Дополнение: объявление коллекции на уровне схемы невозможно

Если коллекция содержит типы элементов, которые известны только в  PL/SQL контексте, то и объявить такую коллекцию возможно только  локально. Например, коллекция с типом элементов rowtype теперь работает с table оператором: 

create or replace package pack 

is

type t1rows is table of t1%rowtype;

end pack;

/

declare 

 

sources pack.t1rows;

targets pack.t1rows;

 

begin

select * bulk collect into sources

from t1;

 

select * bulk collect into targets

from table (sources);

 

dbms_output.put_line ('targets: '||targets.count||' rows.');

 

end;

/

targets: 3 rows.

 Как использовать в DML выражениях, см. решения 2 и 3.

---

PS проверялось на серверах: 11.2, 12.1, 12.2, 18.3.

Источник здесь.