Home » SQL & PL/SQL » SQL & PL/SQL » How to overcome ORA-22160: element at index [1] does not exist
How to overcome ORA-22160: element at index [1] does not exist [message #227220] Tue, 27 March 2007 10:08 Go to next message
madhu_dubasi
Messages: 12
Registered: November 2006
Junior Member
hi friends..

am trying to update using bulk processing.am facing this ORA-22160: element at index [1] does not exist..colud somebody help me how to over come this .I know what it means


ORA-22160: element at index [string] does not exist
Cause: Collection element at the given index does not exist.
Action: Specify the index of an element which exists.


i just want to know how to avoid this.




1 create or replace procedure dept_new
2 is
3 type dept_type is table of dept.deptno%type;
4 type tab_type is table of dept%rowtype;
5 --variable declaration
6 dept_t dept_type;
7 tab_t tab_type;
8 --cursor for updating
9 cursor c is select d.* from dept d,dept1 d1 where d.deptno=d1.deptno;
10 --cursor for inserting
11 cursor c1 is select d.* from dept d,dept1 d1 where d.deptno=d1.deptno(+) and d1.deptno is NULL;
12 BEGIN
13 begin
14 --open insert cursor
15 open c1;
16 loop
17 fetch c1 bulk collect into tab_t;
18 if tab_t.count>0 then
19 forall i in tab_t.first .. tab_t.last
20 insert into dept1 values tab_t(i);
21 end if;
22 exit when c1%notfound;
23 end loop;
24 close c1;
25 end;
26 begin
27 --Open Cursor
28 open c;
29 loop
30 fetch c bulk collect into tab_t;
31 if tab_t.count>0 then
32 forall i in tab_t.first .. tab_t.last
33 update dept1 set ROW=tab_t(i) where deptno=dept_t(i);
34 end if;
35 exit when c%NOTFOUND;
36 end loop;
37 close c;
38 end;
39 --EXCEPTION WHEN OTHERS THEN
40 --commit;
41* END;
SQL> /

Procedure created.

SQL> exec dept_new
BEGIN dept_new; END;

*
ERROR at line 1:
ORA-22160: element at index [1] does not exist
ORA-06512: at "PROJECT.DEPT_NEW", line 32
ORA-06512: at line 1



my dept and dept1 tables are both of same structure.

thanks regards..
madhu
Re: How to overcome ORA-22160: element at index [1] does not exist [message #227233 is a reply to message #227220] Tue, 27 March 2007 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Line 33 you use dept_t(i) but you never fill it.

Next time the {'''} button (just at the left of "Size" field) to post your code, it will be easier to read it.

Regards
Michel
Re: How to overcome ORA-22160: element at index [1] does not exist [message #227330 is a reply to message #227233] Wed, 28 March 2007 01:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
39 --EXCEPTION WHEN OTHERS THEN
40 --commit;


You were not planning on uncommenting this, once you are done debugging, were you?
Re: How to overcome ORA-22160: element at index [1] does not exist [message #227352 is a reply to message #227233] Wed, 28 March 2007 01:40 Go to previous messageGo to next message
madhu_dubasi
Messages: 12
Registered: November 2006
Junior Member
Thank you Michel
My problem got solved
regards madhu

[Updated on: Wed, 28 March 2007 01:40]

Report message to a moderator

Re: How to overcome ORA-22160: element at index [1] does not exist [message #280417 is a reply to message #227352] Tue, 13 November 2007 08:28 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems this is a bit old post.
But I have suggestion.
Quote:

16 loop
17 fetch c1 bulk collect into tab_t;
I feel better to limit the bulk collect. It helps if you have huge data.
Fine! you haven't used limit. I didn't see the significance of loop here.

By
Vamsi
Re: How to overcome ORA-22160: element at index [1] does not exist [message #545324 is a reply to message #227220] Tue, 28 February 2012 06:20 Go to previous messageGo to next message
trupra1.1.11
Messages: 1
Registered: February 2012
Location: Mumbai
Junior Member
Hi
madhu_dubasi

Please try in this way..............

create or replace procedure dept_new
is
type dept_type is table of dept.deptno%type;
type tab_type is table of dept%rowtype;

dept_t dept_type;
tab_t tab_type;

cursor c is select d.* from dept d,dept1 d1 where d.deptno=d1.deptno;

cursor c1 is select d.* from dept d,dept1 d1 where d.deptno=d1.deptno(+) and d1.deptno is NULL;

BEGIN
begin
open c1;
loop
fetch c1 bulk collect into tab_t;
exit when c1%notfound;
end loop;

if tab_t.count>0 then
forall i in tab_t.first .. tab_t.last
insert into dept1 values tab_t(i);
end if;

close c1;
end;


begin
open c;
loop
fetch c bulk collect into tab_t;
exit when c%NOTFOUND;
end loop;

if tab_t.count>0 then
forall i in tab_t.first .. tab_t.last
update dept1 set ROW=tab_t(i) where deptno=dept_t(i);
end if;

lose c;
end ;

end;

If dis code is working just reply me....
All the best....
I hope it's working...
Re: How to overcome ORA-22160: element at index [1] does not exist [message #545325 is a reply to message #545324] Tue, 28 February 2012 06:30 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Welcome to orafaq.
The op was nearly 5 years ago. I doubt it is still a problem.
Please go and read the forum guidelines on how to post code.
Re: How to overcome ORA-22160: element at index [1] does not exist [message #545326 is a reply to message #545324] Tue, 28 February 2012 06:32 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
@trupra1.1.11 - Please read and follow How to use [code] tags and make your code easier to read?
This thread is from 2007, I'd be very worried if madhu hadn't fixed it by now.
Previous Topic: Database tables column to row
Next Topic: Oracle SQL Simple Table Verification
Goto Forum:
  


Current Time: Fri Dec 09 08:08:30 CST 2016

Total time taken to generate the page: 0.17616 seconds