4

Is this possible with oracle's scheduler. I just want to track where it currently is executing, when the job is running and get feedback. 

dbms_scheduler.create_job(
    job_name => 'hello_oracle_scheduler',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_OUTPUT.PUT_LINE('' ''); DBMS_OUTPUT.PUT_LINE(''Hello world of scheduler. Time to execute scheduled jobs!!!''); END;',
    number_of_arguments => 0
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
help
  • 297
  • 2
  • 5
  • 14

2 Answers2

7

You better use a table and insert/updates on it to track your JOBs. DMBS_OUTPUT package makes sense in the weird cases where you have a console.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • Table, is that how its normally/usually done? Never thought to go to that lenght. So even within sqlplus this will not be possible? – help Jun 06 '11 at 21:12
  • 1
    You might want to write to the logging table via a stored proc with anonymous transaction pragma to capture results even if the job itself fails and rolls back. – Shannon Severance Jun 06 '11 at 21:13
  • 1
    @help: You an make the call to dbms_scheduler.create_job from SQL Plus, but the job is run on the server without SQL Plus, seperately from the SQL Plus session that created the job. – Shannon Severance Jun 06 '11 at 21:14
3

I would recommend using Pablo / Shannon's approach of a table insert through a proc with pragma autonomous_transaction option. However, another option would be to use UTL_MAIL (or UTL_SMTP if on 9i or less) to send an email to yourself if this is just a quick and dirty need.

Craig
  • 5,740
  • 21
  • 30
  • Puzzled with utl_mail/utl_smtp. What if I dont know the host – help Jun 07 '11 at 15:41
  • If you don't know your mail server, that will make sending mail rather difficult. :) I imagine you could look it up from your email client setup, but that definitely doesn't gurantee that the db server can talk to that mail server. The table insert will probably be your best option. – Craig Jun 07 '11 at 16:13