How to extract, delete, commit with the cursor

Question

I am trying to remove many rows from a table. I want to try to set the lines that I want to delete in the cursor, and then continue to select, delete, fix on each line of the cursor until it becomes empty.

In the code below, we are fetchingstrings and put them in TYPE.

How can I change the code below to remove the TYPE from the picture and just do it fetch,delete,commiton the cursor itself.

    OPEN bulk_delete_dup;
    LOOP
        FETCH bulk_delete_dup BULK COLLECT INTO arr_inc_del LIMIT c_rows;

        FORALL i IN arr_inc_del.FIRST .. arr_inc_del.LAST
              DELETE FROM UIV_RESPONSE_INCOME 
              WHERE ROWID = arr_inc_del(i);

        COMMIT;
        arr_inc_del.DELETE;
        EXIT WHEN bulk_delete_dup%NOTFOUND;
    END LOOP;
    arr_inc_del.DELETE;
    CLOSE bulk_delete_dup;

Answer 1

? . , , , , DELETE, ..

DELETE FROM uiv_response_income uri
 WHERE EXISTS( 
    SELECT 1
      FROM (<<bulk_delete_dup query>>) bdd
     WHERE bdd.rowid = uri.rowid
  )

, , .

BULK COLLECT ( ), WHERE CURRENT OF DELETE

SQL> create table foo
  2  as
  3  select level col1
  4    from dual
  5  connect by level < 10000;

Table created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is select * from foo for update;
  3    l_rowtype c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into l_rowtype;
  8      exit when c1%notfound;
  9      delete from foo where current of c1;
 10    end loop;
 11* end;
SQL> /

PL/SQL procedure successfully completed.

, , , ( FOR UPDATE), . , , FOR UPDATE, ORA-01002:

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is select * from foo for update;
  3    l_rowtype c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into l_rowtype;
  8      exit when c1%notfound;
  9      delete from foo where current of c1;
 10      commit;
 11    end loop;
 12* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 7

, WHERE CURRENT OF, , . , - , , , , , ORA-01555: . SQL BULK COLLECT.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is select * from foo;
  3    l_rowtype c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into l_rowtype;
  8      exit when c1%notfound;
  9      delete from foo where col1 = l_rowtype.col1;
 10      commit;
 11    end loop;
 12* end;
SQL> /

PL/SQL procedure successfully completed.

, , , , . DELETE , , , , . , , .

Answer 2

. , " 8 " (8 , ?), db, , OLTP ( 1 , 2 ..) db ( 50% 1 ).

OLTP. ( "data factory" ) , , , CTAS, , , /, objs.

, "8 ", , , , , , , , , ( , , 1000 , rowid).

, , rowids, , , ( ), , , () .

- :

declare

-- assuming index on someCol
cursor sel_cur is
select rowid as row_id
from someTable
where someCol = 'BLAH';

v_ctr pls_integer := 0;
begin
for rec in sel_cur
loop
  v_ctr := v_ctr + 1;
  -- watch out for snapshot too old...
  delete from someTable
  where rowid = rec.row_id;
  if (mod(v_ctr, 1000) = 0) then
    commit;
  end if;
end loop;
commit;

end;