-
Calling Oracle mail utl_smtp.data in package- email BODY EMPTY
I'm trying to use the package code written by jkoopmann to monitor my alertlogs and page me (by sending email) on a Windows environment. http://www.dbasupport.com/oracle/ora9i/alert_log2.shtml I've modified it a lil bit. Trust me, it can be a pain monitering alertlogs on a Windows machine! Not without 3rd party tools anyway..I don't wanna use OEM to monitor the alertlogs since I don't wish to get notified on ORA-12500 errors, and there is no way to 'filter' Oracle alerts in OEM without altering the underlying TCL code.
Back to the issue at hand. I'm not very good at code and could use some help here. In the above mentioned package, I'm calling Oracle's utl_smtp package to send me an email with the relevent ORA- error message (which is stored in a buffer area - varchar2 field). While I get the subject, the body appears to be empty everytime! What am I doing wrong here? A dbms_output.putline(buffer2) does return the text..but my email body however is empty! If I pass a variable in a separate pl/sql code, it seems to work fine..but just not in a package body!
I'm pasting the entire code..and have highlighted the problematic piece towards the bottom. Please see refer to the section between the highlighted lines. Why is the 'buffer2' variable not getting passed as the email body?
Many thanks for your time.
- Anand
CREATE OR REPLACE PACKAGE
that_darn_alert_log
AS
PROCEDURE bdump_dir_setup;
PROCEDURE build_possible_alert_log_names;
PROCEDURE check_alertlog_name;
PROCEDURE read_alertlog;
END that_darn_alert_log;
/
CREATE OR REPLACE PACKAGE BODY
that_darn_alert_log
AS
bdump_dir VARCHAR2(500);
alertlist VARCHAR2(4000);
sid VARCHAR2(100);
comma VARCHAR2(1);
alertfile VARCHAR2(255);
itexists NUMBER;
fileat BFILE;
pointa INTEGER;
pointb INTEGER;
v_length INTEGER;
r_char10 RAW(4);
char10 VARCHAR2(1) := CHR(10);
startdate VARCHAR2(100);
r_startdate RAW(100);
buffer VARCHAR2(800);
buffer2 VARCHAR2(800);
buffer3 varchar2(100);
l_maicon utl_smtp.connection;
PROCEDURE bdump_dir_setup IS
BEGIN
EXECUTE IMMEDIATE
'SELECT value '||
' FROM v$parameter '||
' WHERE name = ''background_dump_dest'''
INTO bdump_dir;
EXECUTE IMMEDIATE
'CREATE DIRECTORY BDUMP_DIR '||
' AS '''||bdump_dir||'''';
END bdump_dir_setup;
PROCEDURE check_alertlog_name IS
BEGIN
pointa := 1;
LOOP
pointb := INSTR(alertlist,
',',
pointa,1);
v_length := pointb - pointa;
alertfile:= SUBSTR(alertlist,
pointa,
v_length);
pointa := pointb + 1;
itexists := DBMS_LOB.FILEEXISTS(
BFILENAME('BDUMP_DIR',alertfile));
IF itexists = 1 THEN
pointb := 0;
EXIT;
END IF;
END LOOP;
END check_alertlog_name;
PROCEDURE build_possible_alert_log_names IS
BEGIN
alertlist := alertlist||
comma||
'alert_'||
lower(sid)||
'.log';
comma := ',';
alertlist := alertlist||
comma||
upper(sid)||
'ALRT.LOG';
END build_possible_alert_log_names;
PROCEDURE read_alertlog IS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
alertlist := NULL;
comma := NULL;
EXECUTE IMMEDIATE
'SELECT value '||
' FROM v$parameter '||
' WHERE name = ''db_name'''
INTO sid;
build_possible_alert_log_names;
EXECUTE IMMEDIATE
'SELECT value '||
' FROM v$parameter '||
' WHERE name = ''instance_name'''
INTO sid;
build_possible_alert_log_names;
EXECUTE IMMEDIATE
'SELECT substr(global_name,1,'||
' instr(global_name,''.'',-1)-1) '||
' FROM global_name'
INTO sid;
build_possible_alert_log_names;
r_char10 := UTL_RAW.CAST_TO_RAW(char10);
check_alertlog_name;
fileat := BFILENAME('BDUMP_DIR',alertfile);
DBMS_LOB.FILEOPEN(fileat,DBMS_LOB.FILE_READONLY);
startdate := TO_CHAR(SYSDATE,'Dy Mon DD');
r_startdate := UTL_RAW.CAST_TO_RAW(startdate);
pointa := DBMS_LOB.INSTR(fileat,r_startdate,1,1);
IF pointa != 0 THEN
LOOP
pointb := DBMS_LOB.INSTR(fileat,r_char10,pointa,1);
EXIT WHEN pointb = 0;
v_length := pointb - pointa;
buffer := DBMS_LOB.SUBSTR(fileat,v_length,pointa);
buffer2 := utl_raw.cast_to_varchar2(buffer);
--- Modified for 255 char limit – Anand - works well!
---- DBMS_OUTPUT.PUT_LINE(buffer2);
--if length(buffer2) > 255 then
--DBMS_OUTPUT.PUT_LINE(substr(buffer2,1,254));
--DBMS_OUTPUT.PUT_LINE(substr(buffer2,255,LENGTH(buffer2)));
--Else
--DBMS_OUTPUT.PUT_LINE(buffer2);
--END IF;
If SUBSTR(buffer2, 1,4) ='ORA-' and SUBSTR(buffer2, 1,9) != 'ORA-12500'
Then
DBMS_OUTPUT.PUT_LINE(substr(buffer2,1,254));
l_maicon :=utl_smtp.open_connection('155.11.11.111’);
utl_smtp.helo(l_maicon,'hostname');
utl_smtp.mail(l_maicon,'axr2@hotmail.com');
utl_smtp.rcpt(l_maicon,'axr2@hotmail.com');
utl_smtp.data(l_maicon,'From: oracle-ARTEST@apollo.com' || utl_tcp.crlf||
'To: axr2@hotmail.com' || utl_tcp.crlf ||
'Subject: Alertlog errors' || utl_tcp.crlf || buffer2);
utl_smtp.quit(l_maicon);
END IF;
pointa := pointb + 1;
END LOOP;
END IF;
DBMS_LOB.FILECLOSE(fileat);
END read_alertlog;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
END that_darn_alert_log;
/
Last edited by Axr2; 04-20-2004 at 03:09 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|