PL/SQL Nested Table TRIM

Oracle PL/SQL Nested Table TRIM

The example below shows the use of TRIM to delete an element in a PL/SQL nested table. The TRIM without specifying any number of elements to be delete results in the last element of a collection to be deleted. If a nested_table.TRIM(3) is specified, it deletes last three elements. To avoid SUBSCRIPT_BEYOND_COUNT exception, the value of TRIM(n) should be less than or equal to the total number of elements in a PL/SQL nested table. Typically this should be programatically done by computing the number of elements using nested_table.COUNT and the value of n in TRIM(n) should be computed to be less than the COUNT.

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
     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 LOOP;
END nt_op;

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

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

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: November 17, 2013

73988