Steps
1. Create directory
2. Create table in database for exportlog through external table
3. Create procedure for sent to email or check err msg In exportlog table
4. Create sql script which daily import data from exportlog file to exportlog table.
5. Schedule SQL script to run after export command.
SQL> conn system/oracle
SQL> create directory log as 'c:\log';
SQL> grant read,write on directory LOG to username;
SQL> -- sql script
Create sql script which create exportlog table and import exportlog file to database
DROP TABLE EXPORTLOG;
CREATE TABLE exportlog (msg varchar2(200))
organization external (
type oracle_loader
default directory log
access parameters (
records delimited by newline
)
location ('exportlog.log')
)
reject limit 1000;
exec pro_exportlog;
SQL>-- Create procedure which check error msg in exportlog table and sent email
Procedure: PRO_EXPORTLOG
CREATE OR REPLACE PROCEDURE "SCOTT"."PRO_EXPORTLOG"
AS
SendorAddress Varchar2(30) := 'xxx.xxxx.local';
ReceiverAddress varchar2(30) := 'm.taj@fakhruddin.ae';
EmailServer varchar2(30) := 'xxx.xxxxxx.local';
Port number := 25;
conn UTL_SMTP.CONNECTION;
v_result varchar2(2000);
BEGIN
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress );
utl_smtp.open_data (conn);
utl_smtp.write_data(conn, chr(13));
utl_smtp.write_data(conn, 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )||chr(13));
utl_smtp.write_data(conn, 'From:'||SendorAddress||chr(13));
utl_smtp.write_data(conn, 'To: '||ReceiverAddress ||chr(13));
utl_smtp.write_data(conn, 'Subject: Mail Through ORACLE Database!' ||chr(13));
utl_smtp.write_data(conn, chr(13));
FOR rec IN (select msg from exportlog
where msg LIKE 'EXP-%'
OR msg LIKE '%ORA-%'
OR msg LIKE '%warning%')
LOOP
v_result := rec.msg;
utl_smtp.write_data( conn,v_result||chr(13) );
END LOOP ;
UTL_SMTP.close_data(conn);
UTL_SMTP.quit(conn);
END;
----------------------------------------------------
4. Create bat file and schedule this sql script
I tested above all and the result:
Date: 05 Feb 08 11:26:24
From:xxx.xxxxx.local
To: m.taj@xxxxxxxx.xx
Subject: Mail Through ORACLE Database!
Export terminated successfully without warnings.
----------------------------------------------------
Date: 05 Feb 08 11:23:47
From:xxxx.xxxxxx.local
To: m.taj@xxxxx.xx
Subject: Mail Through ORACLE Database!
EXP-00011: FGC.YYYYY does not exist
Export terminated successfully with warnings.
Any query or comment are welcome.
