-
I have a procedure to delete duplicate rows in a table. I want to schedule it to run every night using dbms_jobs. If I run the procedure manually, I can see 'numberofrows deleted'. But how do I code the dbms_jobs.submit to show me 'the number of rows deleted'? Thank you very much in advance.
"job_submit.sql"
set serveroutput on
declare
job_no number;
job_name VARCHAR2(32) := '&1;';
begin
dbms_job.submit(job_no,job_name,sysdate);
commit;
dbms_output.put_line(job_no);
end;
/
set serveroutput off
"pl_del_dup"
set serveroutput on
create or replace procedure pl_del_dup
is
begin
delete from employee a
where a.rowid>any(select b.rowid from employee b
where a.id=b.id
and a.name=b.name);
dbms_output.put_line(sql%rowcount ||' rows deleted');
end;
/
-
Try to have a output parameter for P_DEL_DUP procedure
which returns number of rows deleted and modify DBMS_JOB call as below
dbms_job.submit(job_no,'Declare num_rows_deleted number; begin P_DEL_DUP(num_rows_deleted ); end;', sysdate);
-
You can just write your output to the log file.
