FORALL更新仅更新集合中的最后一条记录，并给出错误ORA-22160：索引[1]处的元素不存在。我有一个场景，我必须更新数百万条记录。当我执行下面的代码时，更新只发生在第100条记录上，剩下的99条记录保持不变(我用100条记录进行了测试)。执行后，它给出ORA-22160：索引[1]处的元素不存在。我使用的是oracle9i数据库。这个问题需要建议。
Declare
CURSOR tk_iflow_cur
IS
SELECT DISTINCT top.rule_id,
top.rule_item_id,
msib.segment2
FROM iflow_rules top,
iflow_active_rules msib
WHERE top.rule_id = msib.rule_item_id
AND msib.organization_id = 5039 and rownum<=100
ORDER BY top.ora_inv_item_id; -- cursor fetches 100 row
---Variable declaration
TYPE l_iflow_id
IS
TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE l_iflow_org
IS
TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE l_iflow_inv_id
IS
TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE l_r12_inv_id
IS
TABLE OF NUMBER;
TYPE l_r12_item_key
IS
TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE l_r12_iflow
IS
TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
TYPE l_omar_item_id
IS
TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE l_omar_seg2
IS
TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
tk_iflow_id l_iflow_id;
tk_iflow_org l_iflow_org;
tk_iflow_inv_id l_iflow_inv_id;
t_omar_item_id l_omar_item_id;
t_omar_seg2 l_omar_seg2;
r12_inv_id l_r12_inv_id:=l_r12_inv_id();
r12_item_key l_r12_item_key;
r12_iflow l_r12_iflow;
user_excep EXCEPTION;
v_err_count NUMBER;
PRAGMA EXCEPTION_INIT (user_excep, -24381);
r12_item_id2 VARCHAR2(200);
BEGIN --
tk_iflow_id.DELETE;
tk_iflow_inv_id.DELETE;
OPEN tk_iflow_cur;
LOOP --Cursor Loop
tk_iflow_id.DELETE;
tk_iflow_inv_id.DELETE;
t_omar_seg2.DELETE;
FETCH tk_iflow_cur BULK COLLECT INTO tk_iflow_id, tk_iflow_inv_id, t_omar_seg2; ---100 records assigned to variables
FOR i IN 1..tk_iflow_id.COUNT -- to store the cursor value
LOOP --for Loop
BEGIN
--Comment : passing the cursor value to derive new records from different instance for updating the records
SELECT DISTINCT segment1,
inventory_item_id,
item_type
INTO r12_iflow(i),
r12_item_id2,
r12_item_key(i)
FROM iflow.ifl_active_rules@R12_db_link
WHERE segment1 =tk_iflow_id(i)
AND NVL(SEGMENT2,'NULL') = NVL(t_omar_seg2(i),'NULL')
AND organization_id =5063;
EXCEPTION
WHEN OTHERS THEN
r12_inv_id(i) := 0;
r12_item_key(i) := 0;
END;
r12_inv_id :=l_r12_inv_id();
r12_inv_id.EXTEND (i);
r12_inv_id (i) := r12_item_id2;
END LOOP; --end for loop
BEGIN
FORALL i IN r12_inv_id.FIRST..r12_inv_id.LAST SAVE EXCEPTIONS -- for all the derived records i'm updating the target table.100 records i'm updating
UPDATE iflow_active_rules
SET ora_org_id =5063,
ora_inv_item_id =r12_inv_id(i)
WHERE iflow_id =r12_iflow(i)
AND ora_inv_item_id = tk_iflow_inv_id(i);
EXCEPTION
WHEN user_excep THEN
v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. v_err_count
LOOP
DBMS_OUTPUT.put_line ( 'Error: ' || i || ' Array Index: ' || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX || ' Message: ' || SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE) );
END LOOP;
END;
BEGIN
FORALL i IN r12_inv_id.FIRST .. r12_inv_id.LAST SAVE EXCEPTIONS
UPDATE iflow_rules --- updating openup system iflow cost details
SET ora_org_id =5063,
ora_inv_item_id =r12_inv_id(i)
WHERE iflow_id =r12_iflow(i)
AND ora_inv_item_id = tk_iflow_inv_id(i) ;
EXCEPTION
WHEN user_excep THEN
v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. v_err_count
LOOP
DBMS_OUTPUT.put_line ( 'Error: ' || i || ' Array Index: ' || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX || ' Message: ' || SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE) );
END LOOP;
END;
EXIT
WHEN tk_iflow_id.COUNT=0;
COMMIT;
END LOOP; --End Cursor Loop
CLOSE tk_iflow_cur;
END;
之所以发生这种情况，是因为您在集合r12_inv_id的范围内使用r12_iflow集合，而该集合不可访问。FORALL语句与FOR LOOP语句不同。尽管它们在相同的逻辑上工作，但在变量的使用范围上有所不同。您可以在您的案例中创建一个RECORD并进行处理。请参阅下面的操作方法。PS未测试。
DECLARE
CURSOR tk_iflow_cur
IS
SELECT DISTINCT top.rule_id, top.rule_item_id, msib.segment2
FROM iflow_rules top, iflow_active_rules msib
WHERE top.rule_id = msib.rule_item_id
AND msib.organization_id = 5039
AND ROWNUM <= 100
ORDER BY top.ora_inv_item_id; -- cursor fetches 100 row
Type var_cur is table of tk_iflow_cur%rowtype index by pls_integer;
l_cur var_cur;
--Record of your variables.
TYPE var_rec is RECORD
(
l_r12_iflow VARCHAR2 (200),
l_r12_inv_id number,
l_r12_item_key VARCHAR2 (200)
);
TYPE rec is table of var_rec index by pls_integer;
--Variable declared to hold the result of your cursor.
l_rec rec;
user_excep EXCEPTION;
v_err_count NUMBER;
PRAGMA EXCEPTION_INIT (user_excep, -24381);
r12_item_id2 VARCHAR2 (200);
BEGIN
OPEN tk_iflow_cur;
LOOP --Cursor Loop
FETCH tk_iflow_cur BULK COLLECT INTO l_cur LIMIT 100; ---100 records assigned to variables
FOR i IN 1 .. l_cur.COUNT -- to store the cursor value
LOOP --for Loop
BEGIN
--Comment : passing the cursor value to derive new records from different instance for updating the records
SELECT DISTINCT segment1, inventory_item_id, item_type
INTO l_rec(i).l_r12_iflow ,l_rec(i).l_r12_inv_id ,l_rec(i).l_r12_item_key
FROM iflow.ifl_active_rules@R12_db_link
WHERE segment1 = l_cur(i).rule_id
AND NVL (SEGMENT2, 'NULL') = NVL (l_cur(i).segment2 , 'NULL')
AND organization_id = 5063;
EXCEPTION
WHEN OTHERS
THEN
l_rec(i).l_r12_iflow := 0;
l_rec(i).l_r12_inv_id:= 0;
l_rec(i).l_r12_item_key := '';
END;
END LOOP; --end for loop
BEGIN
FORALL i IN 1 .. l_rec.COUNT SAVE EXCEPTIONS -- for all the derived records i'm updating the target table.100 records i'm updating
UPDATE iflow_active_rules
SET ora_org_id = 5063,
ora_inv_item_id = l_rec(i).l_r12_inv_id
WHERE iflow_id = l_rec(i).l_r12_iflow
AND ora_inv_item_id = l_rec(i).l_r12_inv_id;
EXCEPTION
WHEN user_excep
THEN
v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. v_err_count
LOOP
DBMS_OUTPUT.put_line (
'Error: '
|| i
|| ' Array Index: '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| ' Message: '
|| SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP;
END;
BEGIN
FORALL i IN 1..l_rec.COUNT SAVE EXCEPTIONS
UPDATE iflow_rules --- updating openup system iflow cost details
SET ora_org_id = 5063,
ora_inv_item_id = l_rec(i).l_r12_inv_id
WHERE iflow_id = l_rec(i).l_r12_iflow
AND ora_inv_item_id = l_rec(i).l_r12_inv_id;
EXCEPTION
WHEN user_excep
THEN
v_err_count := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. v_err_count
LOOP
DBMS_OUTPUT.put_line (
'Error: '
|| i
|| ' Array Index: '
|| SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
|| ' Message: '
|| SQLERRM (SQL%BULK_EXCEPTIONS (i).ERROR_CODE));
END LOOP;
END;
EXIT WHEN tk_iflow_cur%NOTFOUND;
END LOOP; --End Cursor Loop
COMMIT;
CLOSE tk_iflow_cur;
END;