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

#sql #oracle #plsql #oracle12c #oracle18c

Необходимо вставить зачения в таблицу из коллекции объявленой в пакете.
Создаю следующее:

create table t_test (col01 number, col02 number);

create or replace package pack is 
    type numtab is table of number;
    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 arg.first..arg.last loop pipe row (arg(i)); end loop;
        return;
    end;
end pack;
/


Пробую вызвать так (комментируя поочереди одну из двух вставок):

declare
    nt pack.numtab := pack.numtab (10,20,30);
begin
    insert into t1  
        select rownum, column_value from table(nt);
    insert into t1 
        select rownum, column_value from table(pack.getnt (nt));
end;
/ 


Ожидаю получить в таблице:

 col01 | col02
-------|-------
 1     | 10
 2     | 20
 3     | 30


Но в обоих случаях получаю соответственно следуюшие ошибки:  


  ORA-00902: invalid datatype
  PLS-00642: local collection types not allowed in SQL statements


В списке изменений к версии 12.1 говорится, что теперь не обязательно объявлять типы
коллекций на уровне схемы для того, чтобы их использовать в SQL, в частности с table
оператором:


  Other restrictions are also removed. The table operator can now be used in a PL/SQL
program on a collection whose data type is declared in PL/SQL. This also allows the
data type to be a PL/SQL associative array. (In prior releases, the collection's data
type had to be declared at the schema level.)


Что делаю не так, и что всё таки изменилось? 
    

Ответы

Ответ 1

Существует ряд ограничений для типов коллекций обьявленных в 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.