René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Inserting and deleting from/to a nested table

This example show how to insert and delete from/to a nested table.
create or replace type num_varchar as object (
  num   number,
  vc    Varchar2(10)
);
/


-- Nested table
declare
  type num_varchar_t is table of num_varchar;
  v    num_varchar_t := num_varchar_t();
begin
  v.extend;
  v(v.last) := num_varchar(1, 'one');

  v.extend;
  v(v.last) := num_varchar(2, 'two');

  v.extend;
  v(v.last) := num_varchar(3, 'three');

  v.extend;
  v(v.last) := num_varchar(4, 'four');

  v.extend;
  v(v.last) := num_varchar(5, 'five');

  v.extend;
  v(v.last) := num_varchar(6, 'six');

  v.extend;
  v(v.last) := num_varchar(7, 'seven');

  v.extend;
  v(v.last) := num_varchar(8, 'eight');

  v.extend;
  v(v.last) := num_varchar(9, 'nine');


  dbms_output.put_line('count of v: ' || v.count);
  dbms_output.put_line('last  of v: ' || v.last);

  dbms_output.put_line('deleting elem 7');
  v.delete(7);

  dbms_output.put_line('count of v: ' || v.count);
  dbms_output.put_line('last  of v: ' || v.last);

  dbms_output.put_line('deleting elem 9');
  v.delete(9);

  dbms_output.put_line('count of v: ' || v.count);
  dbms_output.put_line('last  of v: ' || v.last);

  dbms_output.put_line('deleting elem 8');
  v.delete(8);

  dbms_output.put_line('count of v: ' || v.count);
  dbms_output.put_line('last  of v: ' || v.last);
end;
/