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

Thread: Send E-mail Notifications

  1. #1
    Join Date
    Jan 2001
    Posts
    61
    I am looking for some scripts or some way to get the e-mail when the datafile is about to reach to its max. size .Please share your script or way of doing that.

    Thanks,

    Ravi

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    You can create a JOB in OEM.

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Other way is to write your own procedure using the UTL_SMTP and schedule a job to execute it.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    That's the way I do it too: UTL_SMTP + a scheduled job.


  5. #5
    Join Date
    Jan 2001
    Posts
    61
    sambavan and Julian...Could you please write some details,examples etc..

    Thanks



  6. #6
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192

    Send Email with Attachment in Java Way

    Here you go and enjoy with this script. I is working fine.

    - SendMail with attachment, the java way;
    CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SendMail" AS
    import java.util.*;
    import java.io.*;
    import javax.mail.*;
    import javax.mail.internet.*;
    import javax.activation.*;
    public class SendMail {
    // Sender, Recipient, CCRecipient, and BccRecipient are comma-
    // separated lists of addresses;
    // Body can span multiple CR/LF-separated lines;
    // Attachments is a ///-separated list of file names;
    public static int Send(String SMTPServer,
    String Sender,
    String Recipient,
    String CcRecipient,
    String BccRecipient,
    String Subject,
    String Body,
    String ErrorMessage[],
    String Attachments) {

    // Error status;
    int ErrorStatus = 0;

    // create some properties and get the default Session;
    Properties props = System.getProperties();
    props.put("mail.smtp.host", SMTPServer);
    Session session = Session.getDefaultInstance(props, null);

    try {
    // create a message;
    MimeMessage msg = new MimeMessage(session);

    // extracts the senders and adds them to the message;
    // Sender is a comma-separated list of e-mail addresses as
    // per RFC822;
    {
    InternetAddress[] TheAddresses =
    InternetAddress.parse(Sender);
    msg.addFrom(TheAddresses);
    }

    // extract the recipients and assign them to the message;
    // Recipient is a comma-separated list of e-mail addresses
    // as per RFC822;
    {
    InternetAddress[] TheAddresses =
    InternetAddress.parse(Recipient);
    msg.addRecipients(Message.RecipientType.TO,
    TheAddresses);
    }

    // extract the Cc-recipients and assign them to the
    // message;
    // CcRecipient is a comma-separated list of e-mail
    // addresses as per RFC822;
    if (null != CcRecipient) {
    InternetAddress[] TheAddresses =
    InternetAddress.parse(CcRecipient);
    msg.addRecipients(Message.RecipientType.CC,
    TheAddresses);
    }

    // extract the Bcc-recipients and assign them to the
    // message;
    // BccRecipient is a comma-separated list of e-mail
    // addresses as per RFC822;
    if (null != BccRecipient) {
    InternetAddress[] TheAddresses =
    InternetAddress.parse(BccRecipient);
    msg.addRecipients(Message.RecipientType.BCC,
    TheAddresses);
    }

    // subject field;
    msg.setSubject(Subject);

    // create the Multipart to be added the parts to;
    Multipart mp = new MimeMultipart();

    // create and fill the first message part;
    {
    MimeBodyPart mbp = new MimeBodyPart();
    mbp.setText(Body);

    // attach the part to the multipart;
    mp.addBodyPart(mbp);
    }

    // attach the files to the message;
    if (null != Attachments) {
    int StartIndex = 0, PosIndex = 0;
    while (-1 != (PosIndex = Attachments.indexOf("///",
    StartIndex))) {
    // create and fill other message parts;
    MimeBodyPart mbp = new MimeBodyPart();
    FileDataSource fds =
    new FileDataSource(Attachments.substring(StartIndex,
    PosIndex));
    mbp.setDataHandler(new DataHandler(fds));
    mbp.setFileName(fds.getName());
    mp.addBodyPart(mbp);
    PosIndex += 3;
    StartIndex = PosIndex;
    }
    // last, or only, attachment file;
    if (StartIndex < Attachments.length()) {
    MimeBodyPart mbp = new MimeBodyPart();
    FileDataSource fds =
    new FileDataSource(Attachments.substring(StartIndex));
    mbp.setDataHandler(new DataHandler(fds));
    mbp.setFileName(fds.getName());
    mp.addBodyPart(mbp);
    }
    }

    // add the Multipart to the message;
    msg.setContent(mp);

    // set the Date: header;
    msg.setSentDate(new Date());

    // send the message;
    Transport.send(msg);
    } catch (MessagingException MsgException) {
    ErrorMessage[0] = MsgException.toString();
    Exception TheException = null;
    if ((TheException = MsgException.getNextException()) !=
    null)
    ErrorMessage[0] = ErrorMessage[0] + "\n" +
    TheException.toString();
    ErrorStatus = 1;
    }
    return ErrorStatus;
    }
    }
    /
    show errors java source "SendMail"

    CREATE OR REPLACE PACKAGE SendMailJPkg AS
    -- EOL is used to separate text line in the message body;
    EOL CONSTANT STRING(2) := CHR(13) || CHR(10);

    TYPE ATTACHMENTS_LIST IS
    TABLE OF VARCHAR2(3000);

    -- high-level interface with collections;
    FUNCTION SendMail(SMTPServerName IN STRING,
    Sender IN STRING,
    Recipient IN STRING,
    CcRecipient IN STRING DEFAULT '',
    BccRecipient IN STRING DEFAULT '',
    Subject IN STRING DEFAULT '',
    Body IN STRING DEFAULT '',
    ErrorMessage OUT STRING,
    Attachments IN ATTACHMENTS_LIST DEFAULT NULL)
    RETURN NUMBER;
    END SendMailJPkg;
    /
    show errors

    CREATE OR REPLACE PACKAGE BODY SendMailJPkg AS

    PROCEDURE ParseAttachment(Attachments IN ATTACHMENTS_LIST,
    AttachmentList OUT VARCHAR2) IS
    AttachmentSeparator CONSTANT VARCHAR2(12) := '///';
    BEGIN
    -- boolean short-circuit is used here;
    IF Attachments IS NOT NULL AND Attachments.COUNT > 0 THEN
    AttachmentList := Attachments(Attachments.FIRST);
    -- scan the collection, skip first element since it has been
    -- already processed;
    -- accommodate for sparse collections;
    FOR I IN Attachments.NEXT(Attachments.FIRST) ..
    Attachments.LAST LOOP --------ERROR DEV.SENDMAILJPKG", line 14
    AttachmentList := AttachmentList || AttachmentSeparator ||
    Attachments(I);
    END LOOP;
    ELSE
    AttachmentList := '';
    END IF;
    END ParseAttachment;

    -- forward declaration;
    FUNCTION JSendMail(SMTPServerName IN STRING,
    Sender IN STRING,
    Recipient IN STRING,
    CcRecipient IN STRING,
    BccRecipient IN STRING,
    Subject IN STRING,
    Body IN STRING,
    ErrorMessage OUT STRING,
    Attachments IN STRING) RETURN NUMBER;

    -- high-level interface with collections;
    FUNCTION SendMail(SMTPServerName IN STRING,
    Sender IN STRING,
    Recipient IN STRING,
    CcRecipient IN STRING,
    BccRecipient IN STRING,
    Subject IN STRING,
    Body IN STRING,
    ErrorMessage OUT STRING,
    Attachments IN ATTACHMENTS_LIST) RETURN NUMBER IS
    AttachmentList VARCHAR2(2000) := ''; ----- DEV.SENDMAILJPKG", line 44
    AttachmentTypeList VARCHAR2(2000) := '';
    BEGIN
    ParseAttachment(Attachments,
    AttachmentList);
    RETURN JSendMail(SMTPServerName,
    Sender,
    Recipient,
    CcRecipient,
    BccRecipient,
    Subject,
    Body,
    ErrorMessage,
    AttachmentList);
    END SendMail;

    -- JSendMail's body is the java function SendMail.Send();
    -- thus, no PL/SQL implementation is needed;
    FUNCTION JSendMail(SMTPServerName IN STRING,
    Sender IN STRING,
    Recipient IN STRING,
    CcRecipient IN STRING,
    BccRecipient IN STRING,
    Subject IN STRING,
    Body IN STRING,
    ErrorMessage OUT STRING,
    Attachments IN STRING) RETURN NUMBER IS
    LANGUAGE JAVA
    NAME 'SendMail.Send(java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String,
    java.lang.String[],
    java.lang.String) return int';
    END SendMailJPkg;
    /
    show errors

    --var ErrorMessage VARCHAR2(4000);
    --var ErrorStatus NUMBER;

    -- enable SQL*PLUS output;
    --SET SERVEROUTPUT ON
    -- redirect java output into SQL*PLUS buffer;
    --exec dbms_java.set_output(5000);
    --BEGIN
    -- :ErrorStatus := SendMailJPkg.SendMail(
    -- SMTPServerName => 'gmsmtp03.oraclecorp.com',
    -- Sender => 'cesare.cervini@oracle.com',
    -- Recipient => 'ccervini@ch.oracle.com',
    -- CcRecipient => '',
    -- BccRecipient => '',
    -- Subject => 'This is the subject line: Test JavaMail',
    -- Body => 'This is the body: Hello, this is a test' ||
    -- SendMailJPkg.EOL || 'that spans 2 lines',
    -- ErrorMessage => :ErrorMessage,
    -- Attachments => SendMailJPkg.ATTACHMENTS_LIST(
    -- '/export/home/osupport/on.lst',
    -- '/export/home/osupport/sqlnet.log.Z'
    -- )
    -- );
    --END;
    --/
    --print


    CREATE OR REPLACE PROCEDURE sp_java_send_mail AS

    ErrorMessage VARCHAR2(4000);
    ErrorStatus NUMBER;

    BEGIN
    ErrorStatus := SendMailJPkg.SendMail(
    SMTPServerName => '34.13.14.199',
    Sender => 'cdev@yahoo.com',
    Recipient => 'cdev@hotmail.com',
    CcRecipient => '',
    BccRecipient => '',
    Subject => 'This is the subject line: Test JavaMail',
    Body => 'This is the body: Hello, this is a test' ||
    SendMailJPkg.EOL || 'that spans 2 lines',
    ErrorMessage => ErrorMessage,
    Attachments => SendMailJPkg.ATTACHMENTS_LIST(
    '/export/home/cait/java-mail/mess.out'
    )
    );
    END;
    /

    Good Luck
    Chan
    OCP7.3/8.0/8i/9i
    Sun Certified Sys. Admin

  7. #7
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    Hi All,

    Chandra, I got some error running the Java Source, how do I load the missing classes?

    Thanks,
    skid

    ========================================
    Errors for JAVA SOURCE SendMail:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    0/0 SendMail:27: Class Session not found.
    0/0 SendMail:27: Class Session not found.
    0/0 SendMail:27: Undefined variable or class name: Session
    0/0 SendMail:31: Class MimeMessage not found.
    0/0 SendMail:31: Class MimeMessage not found.
    0/0 SendMail:37: Class InternetAddress not found.
    0/0 SendMail:38: Class InternetAddress not found.
    0/0 SendMail:38: Undefined variable or class name: InternetAddress
    0/0 SendMail:46: Class InternetAddress not found.
    0/0 SendMail:47: Class InternetAddress not found.
    0/0 SendMail:47: Undefined variable or class name: InternetAddress

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    0/0 SendMail:57: Class InternetAddress not found.
    0/0 SendMail:58: Class InternetAddress not found.
    0/0 SendMail:58: Undefined variable or class name: InternetAddress
    0/0 SendMail:68: Class InternetAddress not found.
    0/0 SendMail:69: Class InternetAddress not found.
    0/0 SendMail:69: Undefined variable or class name: InternetAddress
    0/0 SendMail:75: Variable msg may not have been initialized.
    0/0 SendMail:78: Class Multipart not found.
    0/0 SendMail:78: Class MimeMultipart not found.
    0/0 SendMail:82: Class MimeBodyPart not found.
    0/0 SendMail:82: Class MimeBodyPart not found.

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    0/0 SendMail:95: Class MimeBodyPart not found.
    0/0 SendMail:95: Class MimeBodyPart not found.
    0/0 SendMail:96: Class FileDataSource not found.
    0/0 SendMail:97: Class FileDataSource not found.
    0/0 SendMail:107: Class MimeBodyPart not found.
    0/0 SendMail:107: Class MimeBodyPart not found.
    0/0 SendMail:108: Class FileDataSource not found.
    0/0 SendMail:109: Class FileDataSource not found.
    0/0 SendMail:117: Variable msg may not have been initialized.
    0/0 SendMail:123: Undefined variable or class name: Transport
    0/0 SendMail:124: Class MessagingException not found.

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    0/0 Info: 33 errors

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by r_sonakiya
    sambavan and Julian...Could you please write some details,examples etc..

    Thanks

    How often do you need to check for how much space you have left?

    This will start a job every morning at 5AM and will send you email:

    Code:
    variable jobno number; 
    begin 
    dbms_job.submit(:jobno,'sp_r_sonakiya;',trunc(sysdate) + 5/24,
    'trunc(sysdate+1) + 5/24'); 
    COMMIT; 
    end; 
    /
    where sp_r_sonakiya is a stored procedure that checks bytes/maxbytes from dba_data_files and sends you email if, say, space left is < 10%.

    Do you know how to write that procedure and moreover, do you have a procedure that sends email?

    Make a serach in the forum with keyword send_email and you will hit the right code.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

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