SQL & PL/SQL

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