SQL Snippets ™: Tutorials for Oracle Developers

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 nt.trim ; 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