How to generate a email from a stored procedure using a mail server or outlook in Oracle

Hi,

I need a code to generate a email from a stored procedure in Oracle using Outlook or a mail server. If is possible we prefer Outlook
LVL 1
joyacv2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
as long as your can communicate via SMTP protocol it doesn't matter what type of email server you have.


Simple example...

DECLARE
    v_connection   UTL_SMTP.connection;
BEGIN
    v_connection := UTL_SMTP.open_connection('your.email.server.com');
    UTL_SMTP.helo(v_connection, 'your.domain.com');
    UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');
    UTL_SMTP.rcpt(v_connection, 'your_recipient@your.domain.com');
    UTL_SMTP.open_data(v_connection);

    UTL_SMTP.write_data (v_connection,
                         'From: test from address' || UTL_TCP.crlf
                        );
    UTL_SMTP.write_data (v_connection, 'To: test to address' || UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection,
                         'Subject: test subject' || UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
    UTL_SMTP.write_data (v_connection, 'test body' || UTL_TCP.crlf);
    UTL_SMTP.close_data (v_connection);
    UTL_SMTP.quit (v_connection);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;


You might also want to review extended authentication in this article

http://www.experts-exchange.com/Database/Oracle/A_5915-Extending-Oracle%27s-Email-functionality-with-PL-SQL-Authentication.html

or attachments with
http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html

you might be able to use utl_mail  but it is very limited, not installed by default and not extensible.  So I don't bother.  Using utl_smtp is pretty easy, and as shown in the article you can build your own wrapper functions to encapsulate more complex functionality with a simple api if needed
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
note, Outlook is simply an email client.  It's not really involved in the sending of the email.  You can certainly send email from Oracle to someone using Outlook though.  I have outlook myself and receive messages from oracle databases using code much like the example above and in the articles.
0
sdstuberCommented:
Also note in 11g or higher, you have to configure network access via ACLs

You'll use the DBMS_NETWORK_ACL_ADMIN package to do that.

You can find instructions for using that package, including examples here...

http://www.experts-exchange.com/Database/Oracle/A_8429-How-to-use-Access-Control-Lists-in-Oracle.html

and part 2 here

http://www.experts-exchange.com/Database/Oracle/A_9074-How-to-use-Network-Access-Control-Lists-in-Oracle.html
0
joyacv2Author Commented:
Hi,

Do you know some additional code to use Outlook to send the message, so any user can use his personal email to send the email in the machine?
0
sdstuberCommented:
Change these lines...

    UTL_SMTP.mail(v_connection, 'your_return_address@your.domain.com');


UTL_SMTP.write_data (v_connection,
                         'From: test from address' || UTL_TCP.crlf
                        );


and your email will be addressed from whomever you want.

Note the "From: ....." line is really just a comment.
 The utl_smtp.mail procedure is what really determines the from.  It's simply convention that most email systems put the address in the "From" line.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.