DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sending / Receiving e-mails

Hybrid View

  1. #1
    Join Date
    Nov 2001
    Posts
    39

    Sending / Receiving e-mails

    (1) Without creating an instance of Outlook, is there a way of sending and receiving e-mails from Oracle program ?

    (2) Can I send formatted emails from Oracle (HTML format) ?
    Thanks, Deepa

  2. #2
    Join Date
    Jun 2001
    Posts
    20
    yup, I think it requires 8i with Jserver enabled. We do it all the time.

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  4. #4
    Join Date
    Jun 2001
    Posts
    20
    yepyep, but that's plain text, looks like. We wanted to trigger html emails based on work requests getting entered.

    here's one fer html mime type:

    CREATE OR REPLACE PROCEDURE send_email
    (p_sender IN VARCHAR2,
    p_recipient IN VARCHAR2,
    p_message IN VARCHAR2)
    as
    l_mailhost VARCHAR2(255) := 'mail.yourserver.com';
    l_mail_conn utl_smtp.connection;
    BEGIN
    l_mail_conn := utl_smtp.open_connection('mail.yourserver.com', 25);
    utl_smtp.helo(l_mail_conn, l_mailhost);
    utl_smtp.mail(l_mail_conn, p_sender);
    utl_smtp.rcpt(l_mail_conn, p_recipient);
    utl_smtp.open_data(l_mail_conn );
    utl_smtp.write_data(l_mail_conn,'MIME-Version: 1.0' ||CHR(13)|| CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)|| p_message);
    utl_smtp.close_data(l_mail_conn );
    utl_smtp.quit(l_mail_conn);
    end;
    /

    this procedure is a little different from the one posted above, in that you can feed it the sender email, recipient email, and message from a trigger. Comes in very handy when you want to actually have the email containing data from the new record.

    this particular procedure is fed by the below trigger:

    CREATE OR REPLACE TRIGGER hux_email_customer
    before insert on worequest
    for each row
    WHEN (new.req_num is not null and
    new.email is not null and
    new.name is not null and
    new.building is not null and
    new.room is not null and
    new.description is not null and
    new.problem is not null and
    new.phone is not null)
    declare to_mail varchar2(64);
    name varchar2(80);
    newbuilding varchar2(40);
    newdescription varchar2(500);
    msgtext varchar2(11048);
    reqid varchar2(10);
    crlf varchar2(2) := chr(13)||chr(10); -- EOL CHARACTERS
    subjectline varchar2(60) := 'Subject: We have received your request, ';
    compiledsubject varchar2(500);
    begin
    newbuilding := :new.building;
    newdescription := :new.description;
    compiledsubject := subjectline||:new.name||crlf;
    to_mail := :new.email;
    name := :new.name;
    reqid := :new.req_num;
    msgtext := compiledsubject||'

    Facilities Management Online Work Request Received



    Dear '||name||',

    We have received your request, and your number is: '||reqid||'.


    Here is a summary of the information you submitted:


  5. '||newbuilding||'
  6. '||newdescription||'

    If you have any questions at all please feel free to call us at x9999 or simply respond to this email.


    Regards,

    Facilities Management Customer Service';
    send_mail2('fromaddy@yourserver.com',to_mail,msgtext);
    end;






    it ain't beautiful, but it works like a mug

    edit - oops, the forum parsed the html within that trigger. If ya need it, lemme know I'll email it to ya.


  7. #5
    Join Date
    Jun 2001
    Posts
    20
    btw - ignore this part due to poor table design.

    WHEN (new.req_num is not null and
    new.email is not null and
    new.name is not null and
    new.building is not null and
    new.room is not null and
    new.description is not null and
    new.problem is not null and
    new.phone is not null)

  8. #6
    Join Date
    Jun 2004
    Posts
    125

    Can you guys help me with this!!!

    Hello guys,

    I am trying to create a form that will use a procedure to send an email when a record is added. Below is my procedure and call to the procedure with the error. I looked up the error ORA-29531, it says this.

    ORA-29531 no method string in class string

    Cause: An attempt was made to execute a non-existent method in a Java class.

    Action: Adjust the call or create the specified method.

    What do I need to get the class file loaded and where to find the class file. Any help will be appreciated.

    P.S. I am running 8i server and Forms 6i on Windows.




    create or replace PROCEDURE send_mail (p_sender IN VARCHAR2,
    p_recipient IN VARCHAR2,
    p_message IN VARCHAR2)
    as
    l_mailhost VARCHAR2(255) := 'bb2.carlylevanlines.com';
    l_mail_conn utl_smtp.connection;
    BEGIN
    l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
    utl_smtp.helo(l_mail_conn, l_mailhost);
    utl_smtp.mail(l_mail_conn, p_sender);
    utl_smtp.rcpt(l_mail_conn, p_recipient);
    utl_smtp.open_data(l_mail_conn );
    utl_smtp.write_data(l_mail_conn, p_message);
    utl_smtp.close_data(l_mail_conn );
    utl_smtp.quit(l_mail_conn);
    end;

    SQL> begin
    2 send_mail( 'sender@test.com',
    3 'receiver@test.com',
    4 'Hello Email' );
    5 end;
    6 /
    begin
    *
    ERROR at line 1:
    ORA-29531: no method get_crlf in class oracle/plsql/net/TCPConnection
    ORA-06512: at "SYS.UTL_TCP", line 678
    ORA-06512: at "SYS.UTL_TCP", line 671
    ORA-06512: at "SYS.UTL_SMTP", line 99
    ORA-06512: at "SYS.UTL_SMTP", line 121
    ORA-06512: at "ORACLE.SEND_MAIL", line 8
    ORA-06512: at line 2

  9. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Have you installed the Jserver? http://www.dbasupport.com/forums/sho...&threadid=4167
    There was an article by Julian* that gave instructions for windows - can't find it for the moment.

    Note also that patches 8.1.7.4.15 and .16 for Windows have a bug which affects UTL_SMTP & UTL_TCP (I got ORA-29532). The fix is to use orajox8.dll from (say) 8.1.7.4.13. You might want to check with support.

    * corrected afterwards
    Last edited by DaPi; 12-22-2004 at 07:06 AM.

  10. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Tim,

    I think your link should be http://www.oracle-base.com/Articles/...romPLSQL9i.php
    (.asp => .php)

    The one you posted gives me a 404.

  11. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Appologies to Julian . . . he wrote the installation instructions for Windows:

    http://www.dbasupport.com/forums/sho...threadid=24763

  12. #10
    Join Date
    Jun 2004
    Posts
    125
    Thanks guys. I really appreciate the replies. I will try it.

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