All collection types can be created in PL/SQL blocks and or in a package. If type is created as schema object, it can be used in object-types and stored in tables.
Because associative arrays are initially empty, they need not (and cannot) be initialized with a constructor. VARRAYs and nested tables on the other hand are required to be initialized with a constructor.
Associative arrays were formerly referred to as PL/SQL tables and/or index-by tables.
Constructors
VARRAYs and nested tables (but not associative arrays) must be initialized with a constructor whose name is equal to the name of the type of the variable that is being constructed.
The constructor accepts one or more parameters whose type is equal to the type of the elements. The constructor initializes the elements with the values passed to the constructor:
delete deletes all items. delete(n) deletes item number n, delete(n,m) deletes items between n and m. delete(n) and delete(n,m) does not shrink the collection if collection is not an associative array.
trim
trim removes 1, trim(n) removes n elements from the collection's end.
extend
extend adds a null, extend(n) adds n null elements to the end of the collection. extend(n,e) adds n copies of the element whose index is e.
exists
exists(n) checks if the n-th element exists. This method is particularly useful in combination with the delete method.
first, last
Return first and last element, respectively
count
Returns number of elements in collection. For VARRAYs, count is equal to last.
limit
Maximum number of elements in a VARRAY, otherwise returns null (because other collections don't have a maximum number of elements.
prior, next
Used to iterate over elements in collections, taking into account elements that might have been deleted.
Local type vs public item
A collection type that is defined in a PL/SQL block is referred to as a local type.
If the collection type is defined in a package specification, it is referred to as public item and can thus be referenced from outside the package (pkg_name.type_name).
Difference to records
A record consists of a given number of named components, referred to as fields, each of which has a data type that is independent from the other fields.
Fields are not accessed with parentheses, but with a dot:
val := rec.fieldName;
It is possible to create a collection of records.
It is not possible to create a record type at schema level
Basic demonstration of declaration of types and variables
The following SQL script tries to demonstrate how the different collection types are created and initialized and where it is possible to do so and where it is not:
drop type obj;
create or replace package collection_types_and_records as
type tab_of_vc2 is table of varchar2(20);
type tab_of_vc2_index_by is table of varchar2(20) index by pls_integer;
type tab_of_tab_of_vc2 is table of tab_of_vc2;
type tab_of_tab_of_vc2_index_by is table of tab_of_vc2_index_by index by varchar2(10);
-- 5 is the maximum number of elements.
-- instances can be created with less than 5 elements
type varray_5_of_vc2 is varray(5) of varchar2(10);
type rec is record (
f_num number,
f_vc2 varchar2(10),
--
-- A record can store
-- collection types:
--
f_tab_of_vc2 tab_of_vc2,
f_tab_of_vc2_index_by tab_of_vc2_index_by,
f_varray_5_of_vc2 varray_5_of_vc2
);
--
-- It's possible to create collection types whose elements
-- are records:
type tab_of_rec is table of rec;
type tab_of_rec_index_by is table of rec index by varchar2(10);
type varray_3_of_rec is varray(3) of rec;
procedure run;
end collection_types_and_records;
/
show errors
create or replace package body collection_types_and_records as
procedure iterate_over(tab tab_of_tab_of_vc2) is
ix_outer varchar2(10);
ix_inner pls_integer;
begin
ix_outer := tab.first;
while ix_outer is not null loop
dbms_output.put_line(ix_outer);
ix_inner := tab(ix_outer).first;
while ix_inner is not null loop
dbms_output.put_line(' ' || ix_inner || ': ' || tab(ix_outer)(ix_inner));
ix_inner := tab(ix_outer).next(ix_inner);
end loop;
ix_outer := tab.next(ix_outer);
end loop;
end iterate_over;
procedure iterate_over(tab tab_of_tab_of_vc2_index_by) is
ix_outer varchar2(10);
ix_inner pls_integer;
begin
ix_outer := tab.first;
while ix_outer is not null loop
dbms_output.put_line(ix_outer);
ix_inner := tab(ix_outer).first;
while ix_inner is not null loop
dbms_output.put_line(' ' || ix_inner || ': ' || tab(ix_outer)(ix_inner));
ix_inner := tab(ix_outer).next(ix_inner);
end loop;
ix_outer := tab.next(ix_outer);
end loop;
end iterate_over;
procedure iterate_over(tab varray_5_of_vc2) as begin
dbms_output.put_line('Iterating over varray_5_of_vc2');
for ix in 1 .. tab.count loop
dbms_output.put_line(' ' || tab(ix));
end loop;
end iterate_over;
procedure run is
v_tab_of_vc2 tab_of_vc2; -- Must be initialized
v_tab_of_tab_of_vc2 tab_of_tab_of_vc2;
v_tab_of_vc2_index_by tab_of_vc2_index_by; -- No initialization necessary
v_tab_of_tab_of_vc2_index_by tab_of_tab_of_vc2_index_by;
v_varray_5_of_vc2 varray_5_of_vc2 := -- Must be initialized
varray_5_of_vc2('I', 'II', 'III', 'IV', 'V');
begin
v_tab_of_vc2 := tab_of_vc2();
v_tab_of_vc2.extend; v_tab_of_vc2(1) := '1/1';
v_tab_of_vc2.extend; v_tab_of_vc2(2) := '1/2';
v_tab_of_tab_of_vc2 := tab_of_tab_of_vc2 (
v_tab_of_vc2,
tab_of_vc2('2/1', '2/2')
);
dbms_output.put_line('v_tab_of_tab_of_vc2 has ' || v_tab_of_tab_of_vc2.count || ' elements');
iterate_over(v_tab_of_tab_of_vc2);
-----------------------------------------------------------------
v_tab_of_vc2_index_by(42) := 'forty-two';
v_tab_of_vc2_index_by(99) := 'ninty-nine';
v_tab_of_tab_of_vc2_index_by('numbers') := v_tab_of_vc2_index_by;
v_tab_of_tab_of_vc2_index_by('fruits' )(1) := 'Apple';
v_tab_of_tab_of_vc2_index_by('fruits' )(2) := 'Banana';
dbms_output.put_line('v_tab_of_tab_of_vc2_index_by has ' || v_tab_of_tab_of_vc2_index_by.count || ' elements');
iterate_over(v_tab_of_tab_of_vc2_index_by);
-----------------------------------------------------------------
dbms_output.put_line('v_varray_5_of_vc2 has ' || v_varray_5_of_vc2.count || ' elements');
iterate_over(v_varray_5_of_vc2);
--
-- Create a new instance with less elements:
v_varray_5_of_vc2 := varray_5_of_vc2('one', 'two', 'three');
dbms_output.put_line('v_varray_5_of_vc2 has ' || v_varray_5_of_vc2.count || ' elements');
iterate_over(v_varray_5_of_vc2);
end run;
end;
/
show errors
exec collection_types_and_records.run
--
-- Schema level collection types can be used in object types
--
create or replace type tab_of_num is table of number;
/
create or replace type varray_3_of_num is varray(3) of number;
/
-- create or replace type assoc_array_t is table of number index by varchar2(10);
-- PLS-00355: use of pl/sql table not allowed in this contex
-- create type rec is record ( a number );
-- /
-- PLS-00103: Encountered the symbol "RECORD" when expecting one of
-- the following:
-- array varray table object fixed varying opaque sparse
declare
x collection_types_and_records.tab_of_vc2;
begin
x := collection_types_and_records.tab_of_vc2('foo', 'bar', 'baz');
end;
/
create or replace type obj as object (
num number,
--
-- Collection types defined in packages cannot be used in objects:
-- tab_of_vc2 collection_types_and_records.tab_of_vc2 , -- PLS-00201: identifier 'COLLECTION_TYPES_AND_RECORDS.TAB_OF_VC2' must be declared
-- tabl_of_vc2_index_by collection_types_and_records.tab_of_vc2_index_by, -- same
-- --------------
-- Schema level collection types can be used in object types
--
tab tab_of_num,
var varray_3_of_num
);
/
/*
select * from user_coll_types;
select * from user_plsql_coll_types;
select * from user_plsql_types;
*/
select
obj.owner,
obj.object_name,
-- obj.object_type, -- remove from output
-- typ.typecode, -- remove from output
typ.persistable,
cty.elem_type_owner,
cty.elem_type_name,
cty.length,
cty.nulls_stored
from
all_objects obj join
all_types typ on obj.owner = typ.owner and obj.object_name = typ.type_name join
dba_coll_types cty on typ.owner = cty.owner and typ.type_name = cty.type_name
where
elem_type_name = 'VARCHAR2' and
1 = 1
order by
length(obj.object_name) – search for a real short one
-- elem_type_name
;