I need to direct my dbms_output in a mail

Question

As a part of my daily operations i need to run a procedure, get the output in a notepad and copy some part in mail and send it to business... If I can shoot a mail from d/b, then it would save some good time. Is it really possible? If yes can anyone share some sample codes. many Thanks in advance!

Answer 1

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.

Answer 2

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

Answer 3

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 ^^

Answer 4

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.