CODEWITHSUNDEEP
oracle-databaseemailplsql
Arvind
Arvind

Reputation:

I need to direct my dbms_output in a mail

As a part of my daily operations I need to:

If I could send mail from database, then it would save some good time. Is it possible? If yes, can anyone share some sample codes?

Upvotes: 0

Views: 882

Answers (4)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

If you're on Oracle 10g or later you can also use UTL_MAIL (which is basically a wrapper on top of UTL_SMTP).

UTL_MAIL.SEND (
   sender     => 'me@host'
   recipients => 'you@host',
   subject    => 'test email',
   message    => 'Hello!');

But note - from the docs:

UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement and the security exposure this involves. In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions.

You must both install UTL_MAIL and define the SMTP_OUT_SERVER.

To install UTL_MAIL:

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file.

Upvotes: 1

user119635
user119635

Reputation:

This wikiarticle should help you: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

It is for Oracle 8 but it should work also with 10 or higher.

Edit: Dam to slow ^^

Upvotes: 0

azazel77
azazel77

Reputation:

Oracle provides some packages which you can use to send mail Following link talks about it

link texthttp://www.orafaq.com/wiki/Send_mail_from_PL/SQL

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Use UTL_SMTP to directly create mails from your plsql procedure. (example code) Caveat: the database server must be able to access the mail server; in some organisations, firewall rules prevent that.

Upvotes: 0

Related Questions