Finding the job number in an exception handler

Question

I have some Oracle jobs running. In each job I have set up a PL/SQL exception handler that sends an email to developers if there is an error encountered in the code the job runs. Is there a way for me to know in the exception handler the job number so I can include that in the email?

Something like:

BEGIN
   Run_This_Plsql();
EXCEPTION WHEN OTHERS DO
   DECLARE
      job_number VARCHAR2(64);
   BEGIN
      job_number := --This would be the job number of the currently running job
      Send_email(job_number, subject, recipient, from);
   END;
END;

Answer 1

While searching, I found this post in OraFAQs forum highlighting BG_JOB_ID parameter of sys_context function - give it a try.

BEGIN
   Run_This_Plsql();
EXCEPTION WHEN OTHERS DO
   DECLARE
      job_number VARCHAR2(64);
   BEGIN
      job_number := sys_context('userenv', 'BG_JOB_ID');
      Send_email(job_number, subject, recipient, from);
   END;
END;

Answer 2

To build on your suggested solution.

I would store the oracle Job number in a table and read this number when the process starts you can then create logging, status, email detail tables that all relate to the job number.

here's some pseudocode:

BEGIN    
    SELECT oracle_job_no INTO v_job_no FROM Myjobs WHERE jobName='Daily doodah';

    job_start(v_job_no); -- log job start

    Run_This_Plsql(v_job_no); 

    job_end(v_job_no); -- log job end

EXCEPTION WHEN OTHERS DO    
DECLARE       
    BEGIN              
        Job_fail(v_job_no); -- log job failure, send email, etc
    END; 
END;

or just use an off the shelf scheduling system that does all of this and more e.g autosys

Answer 3

If you are talking about dbms_job then you can simply use job parameter. Let's say you have following procedure

procedure do_the_job(p_job_no in number) is
begin
   ...
exception
  when others then 
    send_email(p_job_no, ...);
end;

then you can pass job number to the procedure like this:

declare
  l_job_no number;
begin
  dbms_job.submit(job => l_job_no, what => 'do_the_job(job);');
end;
/

Answer 4

If you want session info (session id for example, or any other session info), then you might try (choose columns you wish to report/log):

select * from v$session where sid=(select sys_context('USERENV','SID') from dual);

Hope that helps