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.