Close a nested table type
SQL> SQL> create table department 2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 / Table created. SQL> SQL> INSERT INTO department VALUES (10, 'ACCOUNTING', 'NEW YORK'); 1 row created. SQL> INSERT INTO department VALUES (20, 'RESEARCH', 'DALLAS'); 1 row created. SQL> INSERT INTO department VALUES (30, 'SALES', 'CHICAGO'); 1 row created. SQL> INSERT INTO department VALUES (40, 'OPERATIONS', 'BOSTON'); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE 2 CURSOR all_depts IS 3 SELECT * 4 FROM department 5 ORDER BY dept_name; 6 7 TYPE dept_table IS TABLE OF department%ROWTYPE; 8 9 depts dept_table; 10 depts_max PLS_INTEGER; 11 inx1 PLS_INTEGER; 12 BEGIN 13 depts_max := 0; 14 15 depts := dept_table (); 16 17 FOR dept IN all_depts LOOP 18 depts_max := depts_max + 1; 19 depts.extend; 20 depts(depts_max).dept_id := dept.dept_id; 21 depts(depts_max).dept_name := dept.dept_name; 22 depts(depts_max).no_of_emps := dept.no_of_emps; 23 END LOOP; 24 25 depts.extend(5,1); 26 27 FOR inx1 IN 1..depts_max+5 LOOP 28 DBMS_OUTPUT.PUT_LINE (depts(inx1).dept_id ||' ' || depts(inx1).dept_name); 29 END LOOP; 30 END; 31 / 10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING 10 ACCOUNTING PL/SQL procedure successfully completed. SQL> SQL> drop table department; Table dropped. SQL> SQL> SQL> --