Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

to handle eroor ORA-22160

904890Dec 21 2011 — edited Dec 22 2011
Hi,

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;

/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 19 2012
Added on Dec 21 2011
4 comments
1,845 views