sql - I want to continue looping even when no-data-found and use index by..getting 'no data found error -

i using sqldeveloper. have use index program.

department table has id's 10,20,50,60,80,100.

currently program prints dept_names id's 10 , 20 , quits.

declare type dept_table_indexby table of   departments.department_name%type   index pls_integer; dept_table_arr dept_table_indexby; v_department_id  departments.department_id%type := 10; begin    in 1..10 loop   begin     select department_name dept_table_arr(i)     departments     department_id = v_department_id;     v_department_id := v_department_id + 10;   exception     when no_data_found     --null; /* tried option, still control exits loop */     dbms_output.put_line('in loop : ' || dept_table_arr(i));   end;   end loop;    in dept_table_arr.first..dept_table_arr.last loop   dbms_output.put_line('department name: outside loop ' || dept_table_arr(i));   end loop;  end; 

im not sure error is.

my guess because trying asign null dept_table_arr(i) when id=30

im not familiar oracle sintaxis, here suggestion:

1 - easier couldnt test myself.

select coalesce(department_name , '') dept_table_arr(i) departments department_id = v_department_id; 

the coalesce function takes 2 or more compatible arguments , returns first argument not null.

---

2 - longer know work

select count(department_name) int_department departments department_id = v_department_id;  if int_department > 0     select department_name dept_table_arr(i)     departments     department_id = v_department_id; else     dept_table_arr(i) := ""; end if;  v_department_id := v_department_id + 10;