CODEWITHSUNDEEP
oracle-databaseplsql
Raj Pannala
Raj Pannala

Reputation: 67

how to get row which uses bulk insert and forall to update base table

I have a temp table(around 760k rows ) with no primary key. i am trying to insert rows from this temp table to my main table using bulk collect and forall(also used save exceptions to catch the rejected rows) , i have successfully done this. But i need to keep track of rows which are getting rejected and which are moved succesfully(what i want to do is update status column of temp table as 'E' for error and M for succeeful migration) here is my procedure:

code snippet

desc temp_table:
col1 varchar2(30);
col2 varchar2(30);
col3 number;
col4 number;
status varchar2(1);

create or replace procedure mov_to_main_table
as
loop_count number default 0;
error_row_no number default 0;
sql_stmt varchar2(500);
cursor c_data is
   select * from temp_table,a
    where temp_table.col1=a.col;

TYPE t_bulk_collect_tab IS TABLE OF c_data%ROWTYPE;

l_tab t_bulk_collect_tab;
l_inserted t_bulk_collect_tab; 

BEGIN

OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 1000;

EXIT WHEN l_tab.count = 0;
BEGIN

FORALL i IN 1..l_tab.count  save exceptions
insert into main_table(col1,col2,col3)
values(l_tab(i).col1,l_tab(i).col2,l_tab(i),col3);

EXCEPTION
when others then
bulk_error_count := sql%bulk_exceptions.count;
--dbms_output.put_line('number of error rows :'||bulk_error_count );
for i in 1..bulk_error_count
loop
error_row_no := to_number(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)+100*loop_count;
sql_stmt := 'update  temp_table set status=''E'' where rowid in (select rowid from     temp_table where rownum <=:1 minus select rowid from temp_table where rownum<:2)';
execute immediate sql_stmt using error_row_no,error_row_no;

end loop;


end;

In above code i am updating the row which went into a exception part as 'E' but for the row which is inserting succesfully iam unable to update the status , primarily i need to catch that rownum or rowid to update which iam unable to get from for all how to update the succefully moved row? please help me out thanks in advance..

Upvotes: 1

Views: 2822

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

You approach is quite clever but there are a few things that won't work properly:

  1. First, you don't need dynamic SQL here, drop that execute immediate.
  2. You can still access the l_tab nested table in the exception block, so it's easy to pick the relevant id or rowid.
  3. Last, this is not how paging queries are used in Oracle, see examples on SO.

My advice would be to fetch the rowid at the same time as the rest of the data:

CURSOR c_data IS
   SELECT temp_table.*, a.*, temp_table.rowid rid 
     FROM temp_table, a 
    WHERE temp_table.col1 = a.col;

Then in the exception block you could find and update the offending row:

EXCEPTION
   WHEN OTHERS THEN
      bulk_error_count := SQL%bulk_exceptions.count;
      --dbms_output.put_line('number of error rows :'||bulk_error_count );
      FOR i IN 1 .. bulk_error_count LOOP
         UPDATE temp_table
            SET status = 'E'
          WHERE rowid = l_tab(SQL%BULK_EXCEPTIONS(i).error_index).rid);  
      END LOOP;
END;

Upvotes: 2

Related Questions