Using utl_smtp to send to multple recipients
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Using utl_smtp to send to multple recipients

Hybrid View

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Post

    Hi,

    The following is my code to send email through smtp to one email address.

    create or replace procedure send_email as
    mailhost VARCHAR2(30) := 'smtp.mooks.com';
    crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
    mesg VARCHAR2(1000);
    mail_conn utl_smtp.connection;
    BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, '');
    utl_smtp.rcpt(mail_conn, '');
    mesg := 'This is a test mail!' || crlf ||
    'Subject: Just testing';
    utl_smtp.data(mail_conn, mesg);
    utl_smtp.quit(mail_conn);
    END;


    1. May I now how to use in-coperate a cursor to select the email address that I wish to send to using the following procedures please?

    2. Besides a cursor, any way to test that the code above can send to 2 identified recipients?

    3. How to specify the "TO: " column in the email? I do not want it to be inside the message like:
    mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd-Mon-yy hh24:mi:ss' ) || cr ||
    'From: <'||sender||'>' || cr ||
    'Subject: '||subj || cr ||
    'To: '||recipient || cr ||
    '' || cr || msg;

    The output of the above will be inside the message, instead of our normal message to specify To: , FRom:, Subject: columns.

    Any help please?? Thanks!

  2. #2
    Join Date
    Mar 2001
    Posts
    314
    How about something like:

    CREATE OR REPLACE PROCEDURE send_mail (
    sender IN VARCHAR2,
    recipient IN VARCHAR2,
    subj IN VARCHAR2,
    message IN VARCHAR2)
    IS
    mailhost VARCHAR2(30) := 'mail.toolsyndicate.com';
    c utl_smtp.connection;
    PROCEDURE send_header( name IN VARCHAR2, header IN VARCHAR2) AS
    BEGIN
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
    END;
    BEGIN
    c := utl_smtp.open_connection(mailhost,25);
    utl_smtp.helo(c, mailhost);
    utl_smtp.mail(c, sender);
    utl_smtp.rcpt(c, recipient);
    utl_smtp.open_data(c);
    send_header('From', sender);
    send_header('To', recipient);
    -- If you need to send mail to more than one receipient, uncomment the
    -- following line(s) as appropriate. Please don't forget the ","
    -- in the "To" line before the next receipient's email id. You can't
    -- use a comma separated list in the receipient parameter.
    -- For variable number of "To"'s and "Cc"'s have multiple calls to
    -- "send_header" function inside a cursor for loop.
    -- Similar comments apply for "Cc" too.
    -- send_header('To', ',amar@britneyspearsmail.com');
    -- send_header('Cc', ',amar@christinaaguileramail.com');
    send_header('Subject', subj);
    utl_smtp.write_data(c, utl_tcp.CRLF || message);
    utl_smtp.close_data(c);
    utl_smtp.quit(c);
    EXCEPTION
    WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
    utl_smtp.quit(c);
    raise_application_error(-20000,
    'Failed to send mail due to the following error: ' || sqlerrm);
    END;


    -amar

  3. #3
    Join Date
    Jan 2000
    Posts
    387

    Thumbs up

    Thanks! It works! How about selecting the a group of recipients to send from tha database?

  4. #4
    Join Date
    Mar 2001
    Posts
    54
    How to get the smtp service!
    I tried with the above script.I am getting an error like 'service not available'.
    I don't know much about this one.But still i would like know how to send mails from oracle.COuld u please help me out


    Regards
    MJeyaseelan

  5. #5
    Join Date
    Jan 2000
    Posts
    387

    Wink

    Hi,

    You need to have Oracle 816 and above in order to use the SMTP service. If you are running Oracle 816 and above, then you will just have to install the java components. Have Fun!

    Btw, does anyone know how to use a loop to select a range of email address to be sent from the database please? Thanks!


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