How to use index by table collections in PL/SQL

Question

I am a new user to PL/SQL and recently created an account in Stack Overflow. I am trying to use one loop to retrieve data and store it in an index by table, and then another loop to display the data from the index by table, but it keeps giving me an error that the data is not found. Here is my code:

    SET SERVEROUTPUT ON
    DECLARE
      TYPE dept_table_type IS TABLE OF departments.department_name%TYPE INDEX BY PLS_INTEGER;
      my_dept_table dept_table_type;
      loop_count NUMBER := 10;
      deptno NUMBER := 10;
    BEGIN
      dbms_output.put_line('DEPARTMENT_ID                  DEPARTMENT_NAME');
      LOOP
        SELECT department_name INTO my_dept_table(loop_count)
        FROM departments
        WHERE department_id = deptno;

        deptno := deptno + 10;
        loop_count := loop_count + 10;

        EXIT WHEN loop_count > 100;
      END LOOP;

      FOR I in my_dept_table.FIRST..my_dept_table.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE(I||'                                   '||my_dept_table(I));
      END LOOP;
    END;
    /


    Result: Error report -
    ORA-01403: no data found
    ORA-06512: at line 18
    01403. 00000 -  "no data found"
    *Cause:    No data was found from the objects.
    *Action:   There was no data from the objects which may be due to end of fetch.

Can anyone please check the code. I am a new user to PLSQL and Thank you.

Answer 1

There are two suspicious places which can cause the NO_DATA_FOUND exception.

The first one is select statement which would fail if you try to fetch department name that belongs to department number which doesn't exist.

Based on Scott's DEPT table (as I don't have your tables): this is table's contents:

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

Your code, rewritten:

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     TYPE dept_table_type IS TABLE OF dept.dname%TYPE
  3        INDEX BY PLS_INTEGER;
  4
  5     my_dept_table  dept_table_type;
  6     l_deptno       NUMBER := 10;
  7  BEGIN
  8     LOOP
  9        DBMS_OUTPUT.put_line (l_deptno);
 10
 11        SELECT dname                             --> this fails
 12          INTO my_dept_table (l_deptno)
 13          FROM dept
 14         WHERE deptno = l_deptno;
 15
 16        l_deptno := l_deptno + 10;
 17        EXIT WHEN l_deptno > 100;
 18     END LOOP;
 19  END;
 20  /
10
20
30
40
50                            --> because department 50 doesn't exist
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 11

So, what to do? Handle the exception!

SQL> DECLARE
  2     TYPE dept_table_type IS TABLE OF dept.dname%TYPE
  3        INDEX BY PLS_INTEGER;
  4
  5     my_dept_table  dept_table_type;
  6     l_deptno       NUMBER := 10;
  7  BEGIN
  8     LOOP
  9        DBMS_OUTPUT.put_line (l_deptno);
 10
 11        BEGIN
 12           SELECT dname
 13             INTO my_dept_table (l_deptno)
 14             FROM dept
 15            WHERE deptno = l_deptno;
 16        EXCEPTION
 17           WHEN NO_DATA_FOUND
 18           THEN
 19              DBMS_OUTPUT.put_line (l_deptno || ' department does not exist');
 20        END;
 21
 22        l_deptno := l_deptno + 10;
 23        EXIT WHEN l_deptno > 100;
 24     END LOOP;
 25  END;
 26  /
10
20
30
40
50
50 department does not exist
60
60 department does not exist
70
70 department does not exist
80
80 department does not exist
90
90 department does not exist
100
100 department does not exist

PL/SQL procedure successfully completed.

SQL>

---

Another place (which is what your code suffers from) is when you try to display array item that doesn't exist.

SQL> DECLARE
  2     TYPE dept_table_type IS TABLE OF dept.dname%TYPE
  3        INDEX BY PLS_INTEGER;
  4
  5     my_dept_table  dept_table_type;
  6     l_deptno       NUMBER := 10;
  7  BEGIN
  8     LOOP
  9        BEGIN
 10           SELECT dname
 11             INTO my_dept_table (l_deptno)
 12             FROM dept
 13            WHERE deptno = l_deptno;
 14        EXCEPTION
 15           WHEN NO_DATA_FOUND
 16           THEN
 17              NULL;
 18        END;
 19
 20        l_deptno := l_deptno + 10;
 21        EXIT WHEN l_deptno > 100;
 22     END LOOP;
 23
 24     DBMS_OUTPUT.put_line ('Number of items: ' || my_dept_table.COUNT);
 25
 26     FOR I IN my_dept_table.FIRST .. my_dept_table.LAST
 27     LOOP
 28        DBMS_OUTPUT.put_line ('i = ' || i);
 29        DBMS_OUTPUT.put_line (my_dept_table (i));
 30     END LOOP;
 31  END;
 32  /
Number of items: 4             --> department numbers 10, 20, 30, 40
i = 10                         --> this is OK - department 10 = ACCOUNTING
ACCOUNTING
i = 11                         --> but, there's no department 11 and you got an error
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 29


SQL>

---

Suggestion: rewrite code entirely to be more efficient:

SQL> DECLARE
  2     TYPE dept_table_type IS TABLE OF dept.dname%TYPE
  3        INDEX BY PLS_INTEGER;
  4
  5     my_dept_table  dept_table_type;
  6  BEGIN
  7     SELECT dname
  8       BULK COLLECT INTO my_dept_table
  9       FROM dept;
 10
 11     FOR i IN my_dept_table.FIRST .. my_dept_table.LAST
 12     LOOP
 13        DBMS_OUTPUT.put_line (my_dept_table (i));
 14     END LOOP;
 15  END;
 16  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>