Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2001
    Location
    florida
    Posts
    6

    Unanswered: Generate an Email???

    I would like to write a trigger that would generate an email through an MS exchange server... for example

    Joe updates record, upon update, the trigger runs a program that sends an email through msexchange server to an email address of a person who needs to know about the change.

    Thanks
    C.L.

  2. #2
    Join Date
    Dec 2001
    Posts
    4
    I modified my existing code, and I didn't review the syntax too closely, so try it and repost any errors if it doesn't work.

    CREATE OR REPLACE TRIGGER send_me_email
    AFTER INSERT ON your_table
    REFERENCING new AS new old AS old
    FOR EACH ROW
    WHEN (this_column IS NOT NULL)
    DECLARE
    this_column NUMBER(6);
    err_msg VARCHAR2(1200);
    v_connection UTL_SMTP.CONNECTION;
    v_reply UTL_SMTP.REPLY;
    mesg VARCHAR2(999);
    crlf VARCHAR2(2) := CHR(13)||CHR(10);
    mailserver VARCHAR2(40);
    v_error_type VARCHAR2(60);
    v_email_address VARCHAR2(40);
    subject VARCHAR2(60) := 'whatever you want here'; /* this is actually derived
    from a procedure that I call, but what the hey. */
    BEGIN
    FOR n IN (SELECT mailserver,email_address FROM contact_table) LOOP /* gets the email etc from my contact table */
    v_connection := UTL_SMTP.OPEN_CONNECTION(n.mailserver,25);
    v_reply := UTL_SMTP.HELO(v_connection,n.mailserver);
    v_reply := UTL_SMTP.MAIL(v_connection,n.email_address);
    v_reply := UTL_SMTP.RCPT(v_connection,n.email_address);
    mesg :=
    'Date: '||TO_DATE(sysdate,'DD/MON/RRRR:HH24:MIS')||crlf||
    'From: '||sender||crlf||
    'Subject: '||subject||crlf||
    'To: '||n.email_address||crlf;
    v_reply := UTL_SMTP.DATA(v_connection,mesg);
    v_reply := UTL_SMTP.QUIT(v_connection);
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    err_msg := sqlerrm; /* traps errors and puts in the oracle errror message */
    v_errorlogname := 'trigger send_me_email failed to send correctly.';
    v_errorlogtype := 'Error';
    INSERT INTO errorlog (errorlogid,errorlogtype,errorlogname,errorlogtext ,errorlogtimestamp)
    VALUES (errorlog_errorlogid_seq.nextval,v_errorlogtype,v_ errorlogname,err_msg,sysdate);
    END send_me_email;
    /
    show errors

  3. #3
    Join Date
    Jan 2002
    Location
    italy
    Posts
    39
    If you are on 8i you can load in oracle's JVM JavaMail from sun's site

    Then you can write a java stored procedure to do the work and a pl/sql wrapper to invoke.

    The advantage of this solution is that you can rely on an application (JavaMail) well known and tested.

    If you need any example code let me know...

    regards,
    astropp
    astropp

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Originally posted by astropp
    If you are on 8i you can load in oracle's JVM JavaMail from sun's site

    Then you can write a java stored procedure to do the work and a pl/sql wrapper to invoke.

    The advantage of this solution is that you can rely on an application (JavaMail) well known and tested.

    If you need any example code let me know...
    Hi astropp

    Useful info. I'd like to see some example code please. Also if you have an example of a java stored procedure to copy a file, I'd be eternally grateful. The moving of a file is easy, but there seems to be no easy way to copy a file in java.

  5. #5
    Join Date
    Jan 2002
    Location
    italy
    Posts
    39
    To enable Oracle 8i datatabase to send mail i did the following

    1) download from
    http://java.sun.com/products/javamail
    the package JavaMail and JavaBeans(tm) Activation Framework
    You need to extract from the archive two jars: activation.jar and mail.jar

    Then you can load into the server using loadjava utility:

    loadjava -user user/password -resolve -synonym activation.jar
    loadjava -user user/password -resolve -synonym mail.jar

    If using Oracle8iR2 (8.1.6) you will receive a bytecode verification error: the classes will load and work, however I'm not sure if __everithing__ will work...

    2) When classes have been loaded you need to give the right permissions to work properly:
    -- grant permissions for Java 2 security standard
    connect sys/passwd
    -- Access to system properties
    exec dbms_java.grant_permission('SCOTT',
    'java.util.PropertyPermission',
    '*',
    'read,write');
    -- Access to sockets
    exec dbms_java.grant_permission('SCOTT',
    'java.net.SocketPermission',
    '*',
    'connect,resolve');
    -- Access to files
    exec dbms_java.grant_permission('SCOTT',
    'java.io.FilePermission',
    '*',
    'read,write');
    commit;

    3) At this point we can create in the database a Java class named SendMail with a member function Send(), and a pl/sql package named SendMailJPkg: (I attached the relevant code on the file mail.zip)

    Start the script sendmail.sql
    This will create a Java stored procedure to interface JavaMail

    Then start the script sendmailjpkg.sql
    This will create the pl/sql package sendmailjpkg

    (I attached the scripts because they were too long for this reply)


    4) At the end we can test the whole:

    var ErrorMessage VARCHAR2(4000);
    var ErrorStatus NUMBER;
    set serveroutput on
    exec dbms_java.set_output(5000);

    BEGIN
    :ErrorStatus := SendMailJPkg.SendMail(
    SMTPServerName => 'mail.smtp.host',
    Sender => 'sender@mydomain.com',
    Recipient => 'recipient@otherdomain.com',
    CcRecipient => '',
    BccRecipient => '',
    Subject => 'This is the subject: Oracle sends mail',
    Body => 'This is the body: Hello, this is a test' ||
    SendMailJPkg.EOL || 'spannig 2 lines',
    ErrorMessage => :ErrorMessage,
    Attachments => SendMailJPkg.ATTACHMENTS_LIST(
    '/tmp/attachment.txt',
    '/tmp/attachment.tar.gz'
    )
    );
    END;
    /

    print ErrorMessage
    print ErrorStatus
    Attached Files Attached Files
    astropp

  6. #6
    Join Date
    Jan 2002
    Location
    italy
    Posts
    39
    I forgot the code to copy files from Java...
    here it is...


    create or replace and compile java source named "FileCopy" as
    import java.io.*;

    public class FileCopy {
    public static void copy(String source_name, String dest_name)
    throws IOException {

    File source_file = new File(source_name);
    File dest_file = new File(dest_name);
    FileInputStream source = null;
    FileOutputStream dest = null;
    byte[] buffer;
    int bytes_read;

    try {

    if (!source_file.exists() || !source_file.isFile()) {
    throw new FileCopyException("No such source file: " + source_name);
    }

    if (!source_file.canRead()) {
    throw new FileCopyException("Source file not readable: " + source_name);
    }

    source = new FileInputStream(source_file);
    dest = new FileOutputStream(dest_file);
    buffer = new byte[1024];

    while (true) {
    bytes_read = source.read(buffer);
    if (bytes_read == -1) {
    break;
    }
    dest.write(buffer,0,bytes_read);
    }

    } finally {

    if (source != null) {
    source.close();
    }
    if (dest != null) {
    dest.close();
    }

    }

    }

    // public static void main(String[] args) {
    // try {
    // copy(args[0], args[1]);
    // } catch (IOException e) {
    // System.err.println(e.getMessage());
    // }
    // }

    }

    class FileCopyException extends IOException {
    public FileCopyException(String msg) { super(msg); }
    }
    /

    show error java source "FileCopy"

    create or replace procedure filecopy (source_file varchar2,
    dest_file varchar2) is
    language java
    name 'FileCopy.copy(java.lang.String, java.lang.String)';
    /


    you also need to give permission on the directory where you want to work:

    from sys
    :

    exec dbms_java.grant_permission('SCOTT','java.io.FilePe rmission','/tmp/*','read, write');


    then form SQL*Plus you can execute:


    exec filecopy('/tmp/prova.txt','/tmp/prova2.txt')
    astropp

  7. #7
    Join Date
    Nov 2003
    Posts
    2

    generate email thru form6i and oracle 8i

    I want to send mail thru form6i and database isoracle 8i
    could you help me how to do it.
    Regards
    Meenakshi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •