SQL & PL/SQL

Doesn't NVL work with Assosiative arrays? When i tried to UPDATE empdtls table with the values stored
in v_ename array, i got no_data_found error because
the query, select ename bulk collect into v_ename.....returns only 5 rows and v_ename(6) was null. So i tried NVL and COALESCE for v_ename(6). I couldn't succeed.

Any workarounds?

create table empdtls
 (ename1 varchar2(30),
 ename2 varchar2(30),
 ename3 varchar2(30),
 ename4 varchar2(30),
 ename5 varchar2(30),
 ename6 varchar2(30),
 location varchar2(30));
 
 SQL> insert into empdtls(location) values ('NY');
 
 1 row created.
 
 SQL> commit;


declare
 type ename_arraytyp is table of varchar2(100) index by binary_integer;
 v_ename ename_arraytyp;
begin
 select ename bulk collect into v_ename from emp where sal>2900;
dbms_output.put_line(v_ename(1));
dbms_output.put_line(v_ename(2));
dbms_output.put_line(v_ename(3));
update empdtls
           set ename1=v_ename(1),
               ename2=v_ename(2),
               ename3=v_ename(3),
               ename4=v_ename(4),
               ename5=v_ename(5),
               ename6=NVL(v_ename(6),'No Name')
           where location='NY';
commit;
end;
/

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

Message was edited by:
J.Kiechle