create table dept1 as select * from dept where 1=2;

declare
vv varchar2(200):='select deptno,dname,loc from dept1';
type depp_tab is table of dept1%rowtype;
t_depp depp_tab;
begin
execute immediate vv bulk collect into t_depp;
for i in t_depp.first .. t_depp.last loop
insert into scott.dept values(t_depp(i).deptno,t_depp(i).dname,t_depp(i).loc);
end loop;
end;

ORA-06502: PL/SQL: numeric or value error

SQL> declare
  2  vv varchar2(200):='select deptno,dname,loc from dept1';
  3  type depp_tab is table of dept1%rowtype;
  4  t_depp depp_tab;
  5  begin
  6  execute immediate vv bulk collect into t_depp;
  7  for i in t_depp.first .. t_depp.last loop
  8  insert into scott.dept values(t_depp(i).deptno,t_depp(i).dname,t_depp(i).loc);
  9  end loop;
 10  end;
 11  
 12  
 13  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7

declare
vv varchar2(200):='select deptno,dname,loc from dept1';
type depp_tab is table of dept1%rowtype;
t_depp depp_tab;
begin
execute immediate vv bulk collect into t_depp;
if t_depp.count>0 then 
for i in t_depp.first .. t_depp.last loop
insert into scott.dept values(t_depp(i).deptno,t_depp(i).dname,t_depp(i).loc);
end loop;
end if;
end;