Using Collections of Packages Declared in the PL/SQL Specification in SQL Context

Question:

It is necessary to insert values ​​into the table from the collection declared in the package.
I create the following:

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;
/

I try to call it like this (commenting one of the two inserts in turn):

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;
/ 

I expect to get in the table:

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

But in both cases I get the following errors:

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

The changelog for version 12.1 says that it is no longer necessary to declare collection types at the schema level in order to use them in SQL, in particular with the table statement:

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.)

What am I doing wrong, and what has changed?

Answer:

There are a number of restrictions on collection types declared in PL/SQL packages (hereinafter local collections ), described in more detail in this section of the documentation :

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 .

You can only use local collections in select queries. In insert , update , delete , and merge data modification queries, restrictions have not yet been lifted. In the document general directions for product development (see slide 24) , even before the release of release 12c, no one promised this:

Cannot bind into insert, update, delete, or merge

Depending on the task at hand, there are various solutions.

1. Classic – declare at the schema level

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;
/

And both calls from the question with the package name removed in the declare section:

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

they will work.

2. Use table functions

As before, it is possible to use table functions in DML expressions that return local nested tables as a result type. But passing as an argument to a function of this type, it was and still remains, is unacceptable:

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;
/

A call like this would work:

begin
    insert into t1 
        select rownum, column_value from table (pack.getnt);
end;
/ 

3. Use PL/SQL bulk DML

Suppose we need to get the results of an insert, for example, an implicitly generated id . The returning in a bulk insert from a select subquery is not supported. The forall insert is currently the only way to make changes in bulk and return the result of the changes made, and local collection types come in handy here:

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

Addendum: Collection declaration at schema level is not possible

If a collection contains element types that are known only in the PL/SQL context, then such a collection can only be declared locally. For example, a collection with element type rowtype now works with a table statement:

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.

See solutions 2 and 3 for how to use in DML expressions.


PS tested on servers: 11.2, 12.1, 12.2, 18.3.

Scroll to Top