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 );
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := 'This is a test mail!' || crlf ||
'Subject: Just testing';
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)
mailhost VARCHAR2(30) := 'mail.toolsyndicate.com';
PROCEDURE send_header( name IN VARCHAR2, header IN VARCHAR2) AS
utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
c := utl_smtp.open_connection(mailhost,25);
-- 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', ',email@example.com');
-- send_header('Cc', ',firstname.lastname@example.org');
utl_smtp.write_data(c, utl_tcp.CRLF || message);
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
'Failed to send mail due to the following error: ' || sqlerrm);
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
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! :D
Btw, does anyone know how to use a loop to select a range of email address to be sent from the database please? Thanks!