declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
number pls_integer := 1;
begin
--VALS := WARRAY(null, null);
--LLISTA := WARRAY(null);
--LLISTA := VALS;
dbms_output.put_line(number);
loop
exit when LLISTA(number)%NOTFOUND;
LLISTA(number) := VALS(number);
Number:= Number + 1;
end loop;
end;
Error report:
ORA-06550: line 0, column 0:
PLS-00707: unsupported construct or internal error [2704]
ORA-06550: line 12, column 2:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
There are several things worth noting in this tiny PL/SQL snippet, but let's start off with the main issue.
PLS-00707 is raised because Oracle doesn't support the %NOTFOUND construct with collections as it is meant to be used with cursors. I must guess that the user got confused with the EXISTS method that allows to check for the presence of non-initiatilized elements in collections (not to be confused with null elements!).
declareIf the intention of this procedure was to copy the elements of collection VALS into LLISTA, then it must be noted that if both varrays are exactly of the same type and dimension, then we could get rid of the loop altogether:
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd');
--LLISTA := VALS;
loop
--exit when LLISTA(number)%NOTFOUND;
exit when not LLISTA.EXISTS(number);
LLISTA(number) := VALS(number);
number:= number + 1;
end loop;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number - 1));
end;
Entire collections can be copied as if they were simple variables when the aforementioned conditions are met.
declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;
Note also that the third element of LLISTA ('e') has disappeared after the copy, so, don't expect that Oracle copies only the subset of the collection containing the initialized elements, you'll need to do it yourself (see later on).
Let's see what happens when the type looks equal but is not, as in the following example where i duplicated they custom type WARRAY:
declareThe collection assignment doesn't work anymore because even if WARRAY and WARRAY2 appear to be perfectly equivalent, they are not the same type, strictly speaking.
type WARRAY is varray(324) OF varchar2(200);
type WARRAY2 is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY2;
number pls_integer := 1;
begin
VALS := WARRAY2('a', 'b');
LLISTA := WARRAY('c', 'd');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;
Error report:
ORA-06550: line 10, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Finally, let's go back to the previous step, the working one, and assume that we want to copy only a subset of the elements of VALS. I guess that for some reason VALS contains either updated or saved values that at a certain point we want to restore in LLISTA, but without erasing the elements of LLISTA that have no corresponding subscript in VALS:
declare
LLISTA string_table_type;
VALS string_table_type;
BKP string_table_type;
n pls_integer := 1;
begin
VALS := string_table_type('a','b');
LLISTA := string_table_type('c','d','e','f');
BKP := LLISTA;
n := VALS.count;
select c
bulk collect into LLISTA
from (
select column_value as c
from table(VALS)
union all
select c
from (select rownum r, column_value as c
from table(BKP))
where r > n
);
dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
dbms_output.put_line(LLISTA(i));
end loop;
end;
4
a
b
e
f
Again, some remarks: first of all i had to use an externally defined collection type (string_table_type), not a locally defined collection type because PL/SQL doesn't allow me to use local collections in SQL statements:
create or replace TYPE "STRING_TABLE_TYPE" as tableSecondly, i had to create a third collection named BKP where i staged the data of LLISTA because otherwise i'd get an empty subset. Oracle is implicitly erasing LLISTA before performing BULK COLLECT so i cannot access its elements while performing the query.
of varchar2(200)
Is this memory wasting approach faster than manually copying each and every element from VALS to LLISTA?
declareThe performance comparison test is left to the reader as exercise, as some professors say when they are running late for lunch... ;-)
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
n pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e', 'f');
loop
exit when not VALS.EXISTS(n);
LLISTA(n) := VALS(n);
n:= n + 1;
end loop;
dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
dbms_output.put_line(LLISTA(i));
end loop;
end;
4
a
b
e
f
See message translations for PLS-00707 and search additional resources.
