I'm trying to use PL/SQL utl_smtp. When I run the procedure, I get a message that says that it was successfully...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
completed. But, when I check to see if I've received the message (I sent it to myself using a different e-mail address), I haven't received it. Can you tell me what's wrong? The following is my code.
CREATE OR REPLACE PROCEDURE send_mail ( sender IN VARCHAR2, recipient IN VARCHAR2, subject IN VARCHAR2, message IN VARCHAR2) IS mailhost VARCHAR2(30) := 'send.columbia.edu'; mail_conn utl_smtp.connection; crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 ); mesg VARCHAR2( 1000 ); BEGIN mail_conn := utl_smtp.open_connection(mailhost, 25); mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf || 'From: <'||sender||'>' || crlf || 'Subject: '||subject || crlf || 'To: '||recipient || crlf || '' || crlf || message; utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn, 'firstname.lastname@example.org'); utl_smtp.rcpt(mail_conn, 'email@example.com'); utl_smtp.data(mail_conn, 'mesg'); utl_smtp.quit(mail_conn); END;
I wrote a very similar procedure that works. Here is my code (you can customize it to your needs):
CREATE OR REPLACE PROCEDURE send_mail (sender IN VARCHAR2, recipient IN VARCHAR2, message IN VARCHAR2, nStatus OUT NUMBER) IS mailhost VARCHAR2(30) := 'email.hai.iec.co.il'; -- host mail addr mail_conn utl_smtp.connection; BEGIN nStatus := 0; mail_conn := utl_smtp.open_connection(mailhost, 25); utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn, sender); utl_smtp.rcpt(mail_conn, recipient); utl_smtp.data(mail_conn, message); utl_smtp.quit(mail_conn); EXCEPTION WHEN OTHERS THEN nStatus := SQLCODE; END send_mail; /
For More Information
- Dozens more answers to tough Oracle questions from Eli Leiba are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.