This topic covers unexpected or counter-intuitive behaviours related to PL/SQL collections.
Using Both TRIM and DELETE with Nested Tables
In the following code some might expect that the line
would remove the row containing the value 'b'.
In fact, all it does is release the space previously used by the row with value 'c'.
declare nt p.nt_type := p.nt_type( 'a', 'b', 'c' ) ; begin p.print( '' ); p.print( 'Before:' ); p.print( nt ); nt.delete(3); -- deletes the row containing 'c' p.print( '' ); p.print( 'After .delete:' ); p.print( nt ); nt.trim ; -- releases the space previously occupied by 'c' p.print( '' ); p.print( 'After .trim:' ); p.print( nt ); end; / Before: (1) a (2) b (3) c .first = 1 .last = 3 .count = 3 .limit = NULL After .delete: (1) a (2) b .first = 1 .last = 2 .count = 2 .limit = NULL After .trim: (1) a (2) b .first = 1 .last = 2 .count = 2 .limit = NULL
This is explained best at Oracle® Database PL/SQL User's Guide and Reference - Decreasing the Size of a Collection (TRIM Method).
"TRIM operates on the internal size of a collection.
If TRIM encounters deleted elements, it includes them in its tally.
This refers to deleted elements after using DELETE(n),
but not DELETE without parameters which completely removes all elements.
In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND."
Comparing Identically Defined Nested Tables
In the following code variables NT_1 and NT_2 can be compared for equality since they were both defined using the same type.
declare type nt_type is table of number ; nt_1 nt_type := nt_type( 1,2,3 ); nt_2 nt_type := nt_type( 1,2,3 ); begin if nt_1 = nt_2 then p.print( 'They are equal' ); else p.print( 'They are NOT equal' ); end if; end; / They are equal
In the next snippet however, NT_1 and NT_2 can not be compared for equality since they were both defined using different, though identically structured, types.
declare type nt_1_type is table of number ; type nt_2_type is table of number ; nt_1 nt_1_type := nt_1_type( 1,2,3 ); nt_2 nt_2_type := nt_2_type( 1,2,3 ); begin if nt_1 = nt_2 then p.print( 'They are equal' ); else p.print( 'They are NOT equal' ); end if; end; / if nt_1 = nt_2 * ERROR at line 11: ORA-06550: line 11, column 11: PLS-00306: wrong number or types of arguments in call to '=' ORA-06550: line 11, column 3: PL/SQL: Statement ignored