How to get sql%rowcount print out in DBMS_JOBS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to get sql%rowcount print out in DBMS_JOBS

  1. #1
    Join Date
    Dec 2000
    Posts
    37
    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;
    /

  2. #2
    Join Date
    Sep 2000
    Posts
    77
    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);
    Thanks

  3. #3
    Join Date
    May 2002
    Posts
    37
    You can just write your output to the log file.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width