Getting BULK In-BIND error during bulk update. [message #422453] |
Wed, 16 September 2009 10:26  |
gauravsunil
Messages: 68 Registered: August 2007 Location: bhubaneswar
|
Member |
|
|
Hi Experts,
I want to update a column with default value zero to a static history table employees, having 23 million records using bulk collect.
Please find the script for the same:
SQL> DECLARE
TYPE type1 IS RECORD ( emp_id employees.emp_id%TYPE);
TYPE emp_id_type IS TABLE OF type1 INDEX BY BINARY_INTEGER;
emp_id_arr emp_id_type;
CURSOR hist_correction IS
SELECT emp_id FROM employees;
BEGIN
OPEN hist_correction;
LOOP
EXIT WHEN hist_correction%notfound;
FETCH hist_correction BULK COLLECT INTO emp_id_arr LIMIT 100000;
FORALL i IN emp_id_arr.FIRST..emp_id_arr.LAST
update employees set emp_id = '0';
END LOOP;
END;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 /
I am getting the below error after execution:
update employees set emp_id = '0';
*
ERROR at line 13:
ORA-06550: line 13, column 4:
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
Please help me to resolve the problem.
Thanks and Regards,
Sunil
|
|
|
Re: Getting BULK In-BIND error during bulk update. [message #422455 is a reply to message #422453] |
Wed, 16 September 2009 10:46   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The error is pretty self explanitory - your update statement makes no reference to the Forall array.
Additionally, you can't (afaik) use a table of Records like you are with bulk Bind / Forall:create table test_067 (col_1 number);
insert into test_067 select level from dual connect by level <= 1000;
DECLARE
TYPE col_1_type IS TABLE OF test_067.col_1%type INDEX BY BINARY_INTEGER;
col_1_arr col_1_type;
CURSOR hist_correction IS
SELECT col_1 FROM test_067;
BEGIN
OPEN hist_correction;
LOOP
EXIT WHEN hist_correction%notfound;
FETCH hist_correction BULK COLLECT INTO col_1_arr LIMIT 10;
FORALL i IN col_1_arr.FIRST..col_1_arr.LAST
update test_067 set col_1 = '0'
where col_1 = col_1_arr(i);
END LOOP;
END;
/
select * from test_067;
|
|
|
|