DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Calling Oracle mail utl_smtp.data in package- email BODY EMPTY

Threaded View

  1. #1
    Join Date
    Oct 2002
    Posts
    807

    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
  •  


Click Here to Expand Forum to Full Width