to handle eroor ORA-22160
904890Dec 21 2011 — edited Dec 22 2011Hi,
I have written following procedure and getting following error, Please can anyone guide me how to overcome this issue?
* how can i post my script in a formatted shape, which tags do i have to use
* getting following error
6 rows inserted.
14 rows inserted.
6 rows inserted.
ORA-22160: element at index [16] does not exist
create or replace procedure pr_bulk1 is
type t_idx is table of emp.idx%type
index by pls_integer;
type t_source is table of emp.source%type
index by pls_integer;
type t_score is table of emp.score%type
index by pls_integer;
TYPE guide_aat IS TABLE OF pls_integer
INDEX BY pls_integer;
v_idx t_idx;
v_source t_source;
v_score t_score;
v_score_updated guide_aat;
v_score_n_updated guide_aat;
cursor c is
select idx,
source, score
from rel08_nz_poi
where instr(source,'ABC)>0;
begin
open c;
loop
fetch c bulk collect into
v_idx, v_source,
v_score limit 20;
for i in 1..v_idx.count
loop
if v_score(i) is not null and v_score(i) >90 then
v_score(i):=v_score(i)*2;
v_score_updated(i):=i;
else
v_score_n_updated(i):=i;
end if;
end loop;
forall indx in 1..v_score_updated.count
insert into t_update (
idx, source,
score
) values
(v_idx(indx),
v_source(indx),
v_score(indx)
);
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
);
forall indx in 1..v_score_n_updated.count
insert into t_n_update (
idx, source,
score
) values
(v_idx(indx),
v_source(indx),
v_score(indx)
);
DBMS_OUTPUT.PUT_LINE(
TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
);
exit when c%notfound;
end loop;
--dbms_output.put_line('total records fetched='||c%rowcount);
exception when no_data_found then
dbms_output.put_line('no data found');
when others then
dbms_output.put_line(sqlerrm);
end;
/