1 Reply Latest reply: Jun 2, 2011 9:46 AM by Billy~Verreynne RSS

    dbms_scheduler and email_notification

    mafaiz
      Hi,

      My task is to create scheduler using dbms_scheduler and send mail notification to the authorized user..

      The following process i have done to complete my task,but it not works..

      Step1.create logon table

      create table logon_xe(L_SYSDATE DATE,L_ORA_LOGIN_USER VARCHAR2(100),L_MACHINE VARCHAR2(100),L_PROGRAM VARCHAR2(100),L_OSUSER VARCHAR2(100));

      Step2.create trigger for the create table
      TRIGGER LOGON_XE AFTER LOGON ON XE
      BEGIN
      insert into logon_XE
      select sysdate, ora_login_user, machine, program, osuser
      FROM SYS.DUAL,
      SYS.V_$SESSION
      WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+)
      AND machine <> '<machine_name>';
      END;

      Step3.create scheduler program
      BEGIN
      -- PL/SQL Block.
      DBMS_SCHEDULER.create_program (
      program_name => 'logon_plsql_block_prog',
      program_type => 'PLSQL_BLOCK',
      program_action => 'BEGIN insert into logon_xe values(sysdate)); END;',
      enabled => TRUE,
      comments => 'Program to gather LOGON_XE statistics using a PL/SQL block.');
      END;

      Step4.create scheduler

      BEGIN
      DBMS_SCHEDULER.create_schedule (
      schedule_name => 'logon_xe_daily_schedule',
      start_date => SYSTIMESTAMP,
      repeat_interval => 'freq=daily;byday=TUE,WED,THU,FRI; byhour=16,17;',
      end_date => NULL,
      comments => 'Repeats daily, on the mentioned hour, for ever.');
      END;
      /
      Step5.create job

      DBMS_SCHEDULER.create_job (
      job_name => 'LOGON_XE_JOB',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN insert into logon_xe values(sysdate); END;',
      start_date => SYSTIMESTAMP,
      repeat_interval => 'freq=DAILY; byday=Tue,Wed,Thu,Fri; byhour=16,17;',
      end_date => NULL,
      enabled => TRUE,
      comments => 'Job defined entirely by the CREATE JOB procedure.');
      END;

      Step6.Run job

      BEGIN
      -- Run job synchronously.
      DBMS_SCHEDULER.run_job (job_name => 'LOGON_XE_JOB',
      use_current_session => FALSE);
      END;
      /

      AFTER CREATED SCHEDULER,I HAVE DECLARE PROCEDURE FOR E-MAIL NOTIFICATION.

      Step7.create procedure for mail notification



      CREATE OR REPLACE PROCEDURE send_mail (

      p_mail_host IN VARCHAR2,

      p_from IN VARCHAR2,

      p_to IN VARCHAR2,

      p_subject IN VARCHAR2,

      p_message IN VARCHAR2)

      AS

      l_mail_conn UTL_SMTP.connection;

      BEGIN

      l_mail_conn := UTL_SMTP.open_connection(p_mail_host, 25);

      UTL_SMTP.helo(l_mail_conn, p_mail_host);

      UTL_SMTP.mail(l_mail_conn, p_from);

      UTL_SMTP.rcpt(l_mail_conn, p_to);



      UTL_SMTP.open_data(l_mail_conn);



      UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));

      UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || Chr(13));

      UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || Chr(13));

      UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || Chr(13));

      UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

      UTL_SMTP.write_data(l_mail_conn, p_message || Chr(13));



      UTL_SMTP.close_data(l_mail_conn);

      UTL_SMTP.quit(l_mail_conn);

      END send_mail;

      /

      Step8.
      BEGIN

      send_mail(p_mail_host => 'host',

      p_from => 'user@mycompany.com',

      p_to => 'user1@mycompany.com',

      p_subject => 'Test SEND_MAIL Procedure',

      p_message => 'If you are reading this it worked!');

      END;

      /
      step 9:

      CREATE OR REPLACE PROCEDURE automated_email_alert AS

      l_mail_host VARCHAR2(50) := 'host';

      l_from VARCHAR2(50) := 'user@mycompany.com';

      l_to VARCHAR2(50) := 'user1@mycompany.com';

      BEGIN

      insert into logon_xe values(
      user,
      sys_context('USERENV','SESSIONID'),
      sys_context('USERENV','HOST'),
      null,
      null,
      null,
      sysdate,
      to_char(sysdate, 'hh24:mi:ss'),
      null,
      null,
      null
      );

      send_mail(p_mail_host => l_mail_host,

      p_from => l_from,

      p_to => l_to,

      p_subject => 'AUTOMATED_EMAIL_ALERT (MYSID): Success',

      p_message => 'AUTOMATED_EMAIL_ALERT (MYSID) completed successfully!');



      EXCEPTION

      WHEN OTHERS THEN

      send_mail(p_mail_host => l_mail_host,

      p_from => l_from,

      p_to => l_to,

      p_subject => 'AUTOMATED_EMAIL_ALERT (MYSID): Error',

      p_message => 'AUTOMATED_EMAIL_ALERT (MYSID) failed with the following error:' || SQLERRM);

      END automated_email_alert;

      /


      After successfuly executed all the above steps,still my task haven't completed.
      I have tried to implement this on oracle XE 10.2.0.2
      My scheduled jobs was not running on the mentioned time,also unable to receive mail ...
      Let me want to know what i have done a mistake.

      Advice me on this...

      Edited by: mafaiz on Jun 3, 2011 10:40 AM