-
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!
-
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
-
Thanks! It works! How about selecting the a group of recipients to send from tha database?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|