Insert data with SELECT into Index by Table PL/SQL

Question

Well I have a INDEX BY TABLE which looks like this:

TYPE dept_table_type IS TABLE OF departments.department_name%TYPE INDEX BY PLS_INTEGER;
my_dept_table dept_table_type;

And with this Loop I want to fill it:

FOR l IN 1..10
  LOOP
  deptno := deptno + 10;
  SELECT department_id, department_name INTO my_dept_table FROM departments WHERE department_id=deptno;
  END LOOP;

Is there a possible way to do that because with my solution it's not working...

Thanks in advice

Answer 1

You can either read each department into the appropriate element of the collection:

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
    INDEX BY PLS_INTEGER;

  my_dept_table dept_table_type;
  DEPTNO        NUMBER;
BEGIN
  FOR l IN 1..10
  LOOP
    deptno := l + 10;

    SELECT department_name
      INTO my_dept_table(l)
      FROM departments
      WHERE department_id = deptno;
  END LOOP;
  
  FOR I IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('MY_DEPT_TABLE(' || I || ') = ''' || MY_DEPT_TABLE(I) || '''');
  END LOOP;
END;

works fine.

But better yet, you can BULK COLLECT the data into your collection, which saves a bunch of code:

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
    INDEX BY PLS_INTEGER;

  my_dept_table dept_table_type;
  DEPTNO        NUMBER;
BEGIN
  SELECT DEPARTMENT_NAME
    BULK COLLECT INTO MY_DEPT_TABLE
    FROM DEPARTMENTS;

  FOR I IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('MY_DEPT_TABLE(' || I || ') = ''' || MY_DEPT_TABLE(I) || '''');
  END LOOP;
END;

db<>fiddle here

EDIT

OP has indicated that he wants the department number to be used as the index to the collection. In that case I'd use a cursor and add each element separately:

DECLARE
  TYPE dept_table_type IS TABLE OF departments.department_name%TYPE
    INDEX BY PLS_INTEGER;

  my_dept_table dept_table_type;
BEGIN
  FOR aRow IN (SELECT DEPARTMENT_ID, DEPARTMENT_NAME
                 FROM DEPARTMENTS
                 ORDER BY DEPARTMENT_ID)
  LOOP
    MY_DEPT_TABLE(aRow.DEPARTMENT_ID) := aRow.DEPARTMENT_NAME;
  END LOOP;
  
  FOR I IN MY_DEPT_TABLE.FIRST..MY_DEPT_TABLE.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('MY_DEPT_TABLE(' || I || ') = ''' || MY_DEPT_TABLE(I) || '''');
  END LOOP;
END;

db<>fiddle here