1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

when objects go invalid send an email?

Discussion in 'SQL PL/SQL' started by ashwanth, May 26, 2014.

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    How can I send an email when objects gets invalid?

    I have couple of stored procedures and a package whenever this stored procedure and package goes invalid , i need to send an email.how can i achieve this.can u pls give me an idea how this can be done
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Ashwanth,

    One way of doing it is outlined below:

    1) Write a procedure to list invalid stored procedures and packages using:

    Code (SQL):
    SELECT object_name
    FROM user_objects
    WHERE STATUS = 'INVALID';
    Build the list of such objects and email same to designated persons using UTL_MAIL or UTL_SMTP (you may google around for examples or refer to this article http://www.club-oracle.com/forums/send-two-e-mails-from-oracle-database-t646/

    2) Schedule above job using DBMS_SCHEDULER - see http://www.oracle-base.com/articles/10g/scheduler-10g.php

    Hope this helps.
     
  3. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    thanks for the idea,now i almost completed for sending email .now i need to format the email

    this is how it looks when i get the email

    object_type object_name status last_ddl_time
    -------------------------------------------------------------------

    PROCEDURE SP_LOAD_S1_SHPMT_FILE INVALID 19-MAY-14

    SYNONYM SP_LOAD_GDF_FILE INVALID 17-MAY-14

    SYNONYM SP_LOAD_S1_SHPMT_FILE INVALID 17-MAY-14

    PACKAGE BODY OI_LOAD_PKG INVALID 16-MAY-14

    the code to format the email

    UTL_SMTP.open_data (mail_conn);
    UTL_SMTP.write_data (mail_conn, 'From: ' || sender || crlf);
    UTL_SMTP.write_data (mail_conn, 'To: ' || recievers || crlf);
    UTL_SMTP.write_data (mail_conn,
    'Subject: INVALID OBJECTS ' || crlf);
    mesg :=
    ''
    || crlf
    || 'owner'
    || ' '
    || ' object_type '
    || ' '
    || ' object_name'
    || ' status'
    || ' last_ddl_time'
    || crlf
    || '-------------------'
    || ' -------------------'
    || '------------------------------------------------'
    || crlf;

    UTL_SMTP.write_data (mail_conn, mesg || crlf);


    FOR c1rec IN c1
    LOOP
    mesg :=
    RPAD (c1rec.owner, 10)
    || ' '
    || lPAD (c1rec.object_type, 20)
    || ' '
    || lPAD (c1rec.object_name, 35)
    || lPAD (c1rec.status, 15)
    || lPAD (c1rec.last_ddl_time, 45)
    || crlf;

    UTL_SMTP.write_data (mail_conn, mesg || crlf);
    END LOOP;


    UTL_SMTP.write_data (mail_conn, crlf);
    UTL_SMTP.close_data (mail_conn);

    UTL_SMTP.quit (mail_conn);


    how can i format this for a better output
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    1) Use same formatting for header & detail lines
    2) Use RPAD instead of LPAD so as to left align the fields (just like standard SQL*Plus output)

    You can try below code (extract the parts you need for your script):

    Code (SQL):
    DECLARE
      mesg VARCHAR2(2000);
      CURSOR C1
      IS
        SELECT 'SCOTT' owner,
          'PROCEDURE' object_type,
          'SP_LOAD_S1_SHPMT_FILE' object_name,
          'INVALID' STATUS,
          '19-MAY-14' last_ddl_time
        FROM DUAL
      UNION
      SELECT 'SCOTT' owner,
        'SYNONYM ' object_type,
        'SP_LOAD_GDF_FILE' object_name,
        'INVALID' STATUS,
        '17-MAY-14' last_ddl_time
      FROM DUAL
      UNION
      SELECT 'SCOTT' owner,
        'SYNONYM ' object_type,
        'SP_LOAD_S1_SHPMT_FILE' object_name,
        'INVALID' STATUS,
        '17-MAY-14' last_ddl_time
      FROM DUAL
      UNION
      SELECT 'SCOTT' owner,
        'PACKAGE BODY' object_type,
        'OI_LOAD_PKG' object_name,
        'INVALID' STATUS,
        '16-MAY-14' last_ddl_time
      FROM DUAL;
    BEGIN
      mesg := RPAD ('owner', 10) || ' ' || RPAD ('object_type', 20) || ' ' || RPAD ('object_name', 35) || ' ' || RPAD ('status', 15) || ' ' || RPAD ('last_ddl_time', 15) ;
      DBMS_OUTPUT.PUT_LINE(mesg);
      mesg := '----------' || ' ' || '--------------------' || ' ' || '-----------------------------------' || ' ' || '---------------' || ' ' || '---------------' ;
      DBMS_OUTPUT.PUT_LINE(mesg);
      FOR c1rec IN c1
      LOOP
        mesg := RPAD (c1rec.owner, 10) || ' ' || RPAD (c1rec.object_type, 20) || ' ' || RPAD (c1rec.object_name, 35) || ' ' || RPAD (c1rec.STATUS, 15) || ' ' || RPAD (c1rec.last_ddl_time, 15)
        --|| crlf
        ;
        DBMS_OUTPUT.PUT_LINE(mesg);
      END LOOP;
    END;
    /
     
  5. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    hi rajen,thanks for your help and support,the format is not perfect.how about using html formatting
     
  6. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    can u tell me what is wrong with this code


    CREATE OR REPLACE PROCEDURE sp_send_invalid_list
    AS
    v_message VARCHAR2(32000);
    conn UTL_SMTP.CONNECTION;
    v_sender_acct varchar2(100):='xxx;
    mailhost VARCHAR2 (100) := 'xxx';
    v_count number;
    CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
    v_hostname varchar2(100):=' ';
    v_displayhost varchar2(100);
    BEGIN
    v_message:='<center><h3><i><font color=#000099>List of Invalid objects </font></i></h3></center><br>'||utl_tcp.CRLF;
    v_message:=v_message||'<table style="border: solid 0px #cccccc" cellspacing="0" cellpadding="0"><tr BGCOLOR=#000099>';
    v_message:=v_message||'<td><b><font color=white>Owner</font></td>';
    v_message:=v_message||'<td><b><font color=white>object_type</font></td>';
    v_message:=v_message||'<td><b><font color=white>object_name</font></td>';
    v_message:=v_message||'<td><b><font color=white>status </font></td>';
    v_message:=v_message||'<td><b><font color=white>last_ddl_time</font></td></tr>'||utl_tcp.CRLF;
    FOR I IN ( SELECT owner,
    object_type,
    object_name,
    status,
    last_ddl_time
    FROM dba_objects
    WHERE object_name IN ('xxxx',
    'xxxxE',
    'xxxx',
    'xxxxx')
    AND status != 'VALID'
    ORDER BY owner)
    LOOP
    v_message:=v_message||'<tr><td>'||i.owner||'</td><td>'||i. object_type||'</td></tr>'||utl_tcp.CRLF;
    END LOOP;

    v_message:=v_message||'</table></body></html>'||utl_tcp.CRLF;
    conn:= utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(conn,mailhost);
    utl_smtp.mail(conn,v_sender_acct);
    utl_smtp.rcpt(conn,'loganath.al@pg.com');
    utl_smtp.open_data(conn);
    utl_smtp.write_data(conn,'content-type: text/html;');
    utl_smtp.write_data(conn,'MIME-Version: 1.0'||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'To: '||'xxxxxx'||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'Cc:'||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'From: '||v_sender_acct||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'Subject: List of Invalid objects '||utl_tcp.CRLF);
    utl_smtp.write_data(conn,'<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="TEXT/HTML; ">'||
    utl_tcp.CRLF||'<content="MSHTML 6.00.2800.1276" name=GENERATOR>'||utl_tcp.CRLF||'<HTML><BODY>');
    utl_smtp.write_data(conn,v_message);
    utl_smtp.close_data(conn);
    utl_smtp.quit(conn);
    END;
    /

    i get email,but the body is empty in outlook

    when i check in webmail it works,do u know why this doesnt show the output in outlook
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Sorry ... won't be of much help on this one. At first sight, can't see anything wrong but I don't have the necessary accesses and resources to check on my side and play around with the settings/formatting.

    However, I did hear some times back that Outlook 2007 had some issues on displaying HTML: certain tags are ignored or not recognized.

    PS.: I suppose you've already verified your settings in Outlook (email options: mails in text only).
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I highly recommend pulling the UTL_SMTP portion of sp_send_invalid_list out to a separate procedure that accepts arguments for the elements to be emailed (sender, recipient, subject, body). Sending email is a common requirement and a functional email procedure is something that you will use over and over again.

    Once you create your new procedure (i.e. send_email), troubleshoot that procedure separately from sp_send_invalid_list. Diagnosing the problem should be much simpler with the reduced amount of code and a completely controllable set of data being sent to the procedure, i.e.:

    Code (Text):
    BEGIN
      send_email(p_to      => 'myname@mycomoany.com',
                 p_from    => 'myname@mycomoany.com',
                 p_subject => 'Test Email',
                 p_body    => 'Test Body');
    END;
    Once the email procedure is working, incorporating a call to it into sp_send_invalid_list should be a snap. It will also make future email requirements much simpler as well.
     
  9. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    thanks all for the inputs,it works .
     
  10. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    can i get an overview on sending email notifications on expiring tickets(incidents).we have a ticketing tool with no.of incidents and its target date.when the target date is about to expire from 2 days before it should notification(can handle email notifications now).These info are not stored in db i guess.not sure about the working of tool
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Ashwanth,

    I don't what you mean by
    .
    Which info you mean ?

    If the ticket info is not stored in the database then how can you trigger the notification ?