Home » SQL & PL/SQL » SQL & PL/SQL » Getting BULK In-BIND error during bulk update.
Getting BULK In-BIND error during bulk update. [message #422453] Wed, 16 September 2009 10:26 Go to next message
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 Go to previous messageGo to next message
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;
Re: Getting BULK In-BIND error during bulk update. [message #422457 is a reply to message #422453] Wed, 16 September 2009 10:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Additionally, if all you're trying to do is set a field to 0 for a range of records, then the quickest solution is
UPDATE empployees
SET   emp_id = '0';
Previous Topic: Send Attachment in Email using PL/SQL
Next Topic: Exception handling using update
Goto Forum:
  


Current Time: Fri Sep 30 07:19:09 CDT 2016

Total time taken to generate the page: 0.23620 seconds