PL/SQL Nested Table DELETE

Oracle PL/SQL Nested Table DELETE

PL/SQL nested table DELETE operation creates a gap in the sequence as shown in the example below. The PRIOR and NEXT methods are used to iterate through the PL/SQL nested table. The example below uses PRIOR, NEXT and EXISTS to iterate through the collection. When the 8 ^th element is deleted, the last value is still 9 as shown below and has to be skipped over to avoid NO_DATA_FOUND exception.

DECLARE
TYPE nested_tab IS TABLE OF NUMBER;
nt_list nested_tab := nested_tab(0,1,3,5,7,9,11,13,15);

PROCEDURE nt_op(i_nt IN nested_tab) IS
BEGIN
   FOR idx IN i_nt.FIRST .. i_nt.LAST LOOP
    IF i_nt.EXISTS(idx) THEN
     DBMS_OUTPUT.PUT('['||idx||'] = '||LPAD(i_nt(idx),2,' '));

      IF i_nt.EXISTS(i_nt.PRIOR(idx)) THEN
       DBMS_OUTPUT.PUT(' PRIOR['||idx||'] = '|| LPAD(i_nt(i_nt.PRIOR(idx)),2,' '));
      ELSE
      DBMS_OUTPUT.PUT(' PRIOR['||idx||'] = -');
      END IF;

     IF i_nt.EXISTS(i_nt.NEXT(idx)) THEN
      DBMS_OUTPUT.PUT_LINE(' NEXT['||idx||'] = '|| LPAD(i_nt(i_nt.NEXT(idx)),2,' '));
     ELSE
      DBMS_OUTPUT.PUT_LINE(' NEXT['||idx||'] = -');
     END IF;

    END IF;
   END LOOP;
END nt_op;

BEGIN
DBMS_OUTPUT.PUT(CHR(10));
nt_op(nt_list);

nt_list.DELETE(8);
DBMS_OUTPUT.PUT_LINE(CHR(10)||'After nt_list.DELETE(8)');
nt_op(nt_list);
END;
/

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 17, 2013