How do I find the number of rows processed inside a pl/sql block? I want to commit the transaction only if the number of rows deleted = rows inserted into another table.
Thanks in advance.
Printable View
How do I find the number of rows processed inside a pl/sql block? I want to commit the transaction only if the number of rows deleted = rows inserted into another table.
Thanks in advance.
Hi,
This is Rohit ,OCP from India.
Inside a pl/sql u can find out the number of rows processed by rowcount attribute of cursor.
eg.
declare
x number;
begin
insert into emp_dup
select * from emp;
select count(*) into x from emp_dup;
if sql%rowcount=x then
delete from emp;
end if;
end;
sql%rowcount attribute of cursor tells u how many rows have been processed.
If any doubts please be free to write to me at
rohitsn@hotmail.com
Hi,
This is Rohit ,OCP from India.Please dont refer to the earlier pl/qsl given ,it will not solve ur problem.Use the below given program which will give u the desired result.
Inside a pl/sql u can find out the number of rows processed
by rowcount attribute of cursor.
declare
new_rows number;
old_rows number;
begin
select count(*) into old_rows from a;
insert into b
select * from a;
new_rows:=sql%rowcount;
if new_rows=old_rows then
delete from a;
commit;
else
dbms_output.put_line('Error in inserting rows');
end if;
end;
/
If any doubts please be free to write to me at
rohitsn@hotmail.com