DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer nested_typ; BEGIN answer := nt1 MULTISET UNION nt4; answer := nt1 MULTISET UNION nt3; answer := nt1 MULTISET UNION DISTINCT nt3; answer := nt2 MULTISET INTERSECT nt3; answer := nt2 MULTISET INTERSECT DISTINCT nt3; answer := SET(nt3); answer := nt3 MULTISET EXCEPT nt2; answer := nt3 MULTISET EXCEPT DISTINCT nt2; END; 

 nt1 MULTISET UNION nt4: 1 2 3 1 2 4 nt1 MULTISET UNION nt3: 1 2 3 2 3 1 3 nt1 MULTISET UNION DISTINCT nt3: 1 2 3 nt2 MULTISET INTERSECT nt3: 3 2 1 nt2 MULTISET INTERSECT DISTINCT nt3: 3 2 1 SET(nt3): 2 3 1 nt3 MULTISET EXCEPT nt2: 3 nt3 MULTISET EXCEPT DISTINCT nt2: empty set 

 DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1, 2, 3); nt2 nested_typ := nested_typ(3, 2, 1); nt3 nested_typ := nested_typ(2, 3, 1, 3); nt4 nested_typ := nested_typ(); BEGIN IF nt1 = nt2 THEN DBMS_OUTPUT.PUT_LINE('nt1 = nt2'); END IF; IF (nt1 IN (nt2, nt3, nt4)) THEN DBMS_OUTPUT.PUT_LINE('nt1 IN (nt2,nt3,nt4)'); END IF; IF (nt1 SUBMULTISET OF nt3) THEN DBMS_OUTPUT.PUT_LINE('nt1 SUBMULTISET OF nt3'); END IF; IF (3 MEMBER OF nt3) THEN DBMS_OUTPUT.PUT_LINE('3 MEMBER OF nt3'); END IF; IF (nt3 IS NOT A SET) THEN DBMS_OUTPUT.PUT_LINE('nt3 IS NOT A SET'); END IF; IF (nt4 IS EMPTY) THEN DBMS_OUTPUT.PUT_LINE('nt4 IS EMPTY'); END IF; END; 

 nt1 = nt2 nt1 IN (nt2,nt3,nt4) nt1 SUBMULTISET OF nt3 3 MEMBER OF nt3 nt3 IS NOT A SET nt4 IS EMPTY 

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(11, 22, 33, 44, 55, 66); BEGIN nt.TRIM; -- Trim last element nt.DELETE(4); -- Delete fourth element nt.TRIM(2); -- Trim last two elements END; 

 beginning: 11 22 33 44 55 66 after TRIM: 11 22 33 44 55 after DELETE(4): 11 22 33 55 after TRIM(2): 11 22 33 

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(11, 22, 33); BEGIN nt.EXTEND(2, 1); -- Append two copies of first element nt.DELETE(5); -- Delete fifth element nt.EXTEND; -- Append one null element END; 

 beginning: 11 22 33 after EXTEND(2,1): 11 22 33 11 11 after DELETE(5): 11 22 33 11 after EXTEND: 11 22 33 11 

 DECLARE TYPE NumList IS TABLE OF INTEGER; n NumList := NumList(1, 3, 5, 7); BEGIN n.DELETE(2); -- Delete second element FOR i IN 1 .. 6 LOOP IF n.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE('n('||i||') = ' || n(i)); ELSE DBMS_OUTPUT.PUT_LINE('n('||i||') does not exist'); END IF; END LOOP; END; 

 DECLARE TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10); aa_str aa_type_str; BEGIN aa_str('Z') := 26; aa_str('A') := 1; aa_str('K') := 11; aa_str('R') := 18; DBMS_OUTPUT.PUT_LINE('Before deletions:'); DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST); aa_str.DELETE('A'); aa_str.DELETE('Z'); DBMS_OUTPUT.PUT_LINE('After deletions:'); DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST); END; 

 Before deletions: FIRST = A LAST = Z After deletions: FIRST = K LAST = R 

 DECLARE TYPE NumList IS VARRAY(10) OF INTEGER; n NumList := NumList(1, 3, 5, 7); BEGIN DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); n.EXTEND(3); DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); n.TRIM(5); DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); END; 

 n.COUNT = 4, n.LAST = 4 n.COUNT = 7, n.LAST = 7 n.COUNT = 2, n.LAST = 2 

 DECLARE TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER; aa aa_type; -- associative array TYPE va_type IS VARRAY(4) OF INTEGER; va va_type := va_type(2, 4); -- varray TYPE nt_type IS TABLE OF INTEGER; nt nt_type := nt_type(1, 3, 5); -- nested table BEGIN aa(1) := 3; aa(2) := 6; aa(3) := 9; aa(4) := 12; DBMS_OUTPUT.PUT_LINE('aa.COUNT = ' || aa.count); DBMS_OUTPUT.PUT_LINE('aa.LIMIT = ' || aa.limit); DBMS_OUTPUT.PUT_LINE('va.COUNT = ' || va.count); DBMS_OUTPUT.PUT_LINE('va.LIMIT = ' || va.limit); DBMS_OUTPUT.PUT_LINE('nt.COUNT = ' || nt.count); DBMS_OUTPUT.PUT_LINE('nt.LIMIT = ' || nt.limit); END; 

 aa.COUNT = 4 aa.LIMIT = va.COUNT = 2 va.LIMIT = 4 nt.COUNT = 3 nt.LIMIT = 

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(18, NULL, 36, 45, 54, 63); BEGIN nt.DELETE(4); DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.'); FOR i IN 1 .. 7 LOOP DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = '); print(nt.PRIOR(i)); DBMS_OUTPUT.PUT('nt.NEXT(' || i || ') = '); print(nt.NEXT(i)); END LOOP; END; 

 nt(4) was deleted. nt.PRIOR(1) = nt.NEXT(1) = 2 nt.PRIOR(2) = 1 nt.NEXT(2) = 3 nt.PRIOR(3) = 2 nt.NEXT(3) = 5 nt.PRIOR(4) = 3 nt.NEXT(4) = 5 nt.PRIOR(5) = 3 nt.NEXT(5) = 6 nt.PRIOR(6) = 5 nt.NEXT(6) = nt.PRIOR(7) = 6 nt.NEXT(7) = 

 DECLARE TYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; CURSOR c1 IS SELECT employee_id,last_name FROM employees WHERE salary > 10000 ORDER BY last_name; enums NumTab; names NameTab; BEGIN SELECT employee_id, last_name BULK COLLECT INTO enums, names FROM employees ORDER BY employee_id; OPEN c1; LOOP FETCH c1 BULK COLLECT INTO enums, names LIMIT 10; EXIT WHEN names.COUNT = 0; do_something(); END LOOP; CLOSE c1; DELETE FROM emp_temp WHERE department_id = 30 RETURNING employee_id, last_name BULK COLLECT INTO enums, names; END; 

 DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids enum_t; TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids dept_t; BEGIN FORALL j IN depts.FIRST .. depts.LAST DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; END; 

 DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; BEGIN nums(1) := 1; -- raises COLLECTION_IS_NULL nums := NumList(1, 2); nums(NULL) := 3; -- raises VALUE_ERROR nums(0) := 3; -- raises SUBSCRIPT_BEYOND_COUNT nums(3) := 3; --raises SUBSCRIPT_OUTSIDE_LIMIT nums.Delete(1); IF nums(1) = 1 THEN ... -- raises NO_DATA_FOUND END; 

 CREATE PACKAGE foobar type number_idx_tbl is table of number indexed by binary_integer; store1_table number_idx_tbl; -- PL/SQL indexed table store2_table number_idx_tbl; -- PL/SQL indexed table store3_table number_idx_tbl; -- PL/SQL indexed table ... END; -- end of foobar DECLARE ... empty_table number_idx_tbl; -- uninitialized ("empty") version BEGIN FOR i in 1..1000000 loop store1_table(i) := i; -- load data END LOOP; ... store1_table := empty_table; -- "truncate" the indexed table ... - dbms_session.free_unused_user_memory; -- give memory back to system store1_table(1) := 100; -- index tables still declared; store2_table(2) := 200; -- but truncated. ... END;