Oracle 10g Scheduler Job Email Notification

1) connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@apx1213 as sysdba
Enter password: ******
Connected.
SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @d:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

2) Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'mail.apexsoftcell.com' scope=spfile;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

3) Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to ldbo;

Grant succeeded.

) Create procedure for Email Notification

create or replace procedure PRC_EMAIL (pSubject IN VARCHAR2, pMessage IN VARCHAR2) is
BEGIN
utl_mail.send(sender => 'info@apexsoftcell.com', recipients => 'kshitij@apexsoftcell.com', subject => pSubject, message => pMessage);
END;
/

4) Create Scheduler Job

BEGIN
DBMS_SCHEDULER.drop_JOB (job_name => 'compile');
END;
/


BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'compile',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE lnResult VARCHAR2(150);
BEGIN UTL_RECOMP.recomp_serial(''LDBO''); lnResult:=''SUCCESS'';
PRC_EMAIL(''Compile Notification'',lnResult);
EXCEPTION WHEN OTHERS THEN lnResult:=SUBSTR(SQLERRM,1,150);
PRC_EMAIL(''Compile Notification'',lnResult);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Compile job');
END;
/

5) Execute Job Manually
exec DBMS_SCHEDULER.run_job ('compile');