Search This Blog

Total Pageviews

Tuesday 13 November 2018

Oracle delete row via plsql (rowid )

Oracle delete row via plsql (rowid )  .... 



Create table !!!


create table cust
(cust_id number
,last_name varchar2(30)
,first_name varchar2(30)
);


insert into cust (cust_id, last_name, first_name) values(1, 'ANUJ','SINGH');

Insert 

insert into cust
select level + 1
,dbms_random.string('U',dbms_random.value(3,15)) rand_last_name
,dbms_random.string('U',dbms_random.value(3,15)) rand_first_name
from dual
connect by level < 100000;



SQL>
insert into cust
select level + 1
,dbms_random.string('U',dbms_random.value(3,15)) rand_last_name
,dbms_random.string('U',dbms_random.value(3,15)) rand_first_name
from dual
connect by level < 10000000;

99999 rows created.

SQL> commit;

Commit complete.




set serveroutput on
declare

v_count number ;
  x NUMBER := 0;
  v_CUST CUST%ROWTYPE;

begin

for i in (select rowid, c.* from CUST C) loop

begin

dbms_output.put_line(v_count||'  '||'Total No of Row Count ON Cust');

delete from CUST where rowid=i.rowid ;
      x := x + 1;              -- Commits after every X number of records
      IF x > 1000 THEN
      dbms_output.put_line(x||'  '||'Deleted Row from Cust');
COMMIT;
         x := 0;
      END IF;
exception
when no_data_found
then
dbms_output.put_line('exception');
end;
end loop;
end;


*****************************************************************

SQL> set serveroutput on
SQL> declare
  2    x NUMBER := 0;
  v_CUST CUST%ROWTYPE;
  3    4  begin
  5  for i in (select rowid, c.* from CUST C) loop
  6  begin
delete from CUST where rowid=i.rowid ;
  7    8        x := x + 1;              -- Commits after every X number of records
      IF x > 1000 THEN
  9   10        dbms_output.put_line(x||'  '||'Deleted Row from Cust');
 11
 12  COMMIT;
 13           x := 0;
 14        END IF;
 15  exception
when no_data_found
 16   17  then
 18  dbms_output.put_line('exception');
 19  end;
end loop;
 20   21  end;
 22  /



1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust
1001  Deleted Row from Cust

PL/SQL procedure successfully completed.

Oracle DBA

anuj blog Archive