| How to overcome ORA-22160: element at index [1] does not exist [message #227220] |
Tue, 27 March 2007 10:08  |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
