Greens Technologys Whatsapp
Greens Technologys Whatsapp

UTL_MAIL : Email from PL/SQL in the Oracle Database

+91-89399 15577

UTL_MAIL : Email from PL/SQL in the Oracle Database

The UTL_MAIL package was introduced in Oracle 10g to provide a simple API to allow email to be sent from PL/SQL. In prior versions this was possible using the UTL_SMTP package (shown here), but this required knowledge of the SMTP protocol.

  • Setup
  • Send Emails
  • Send Emails with Attachments

Related articles.

Email From Oracle PL/SQL (UTL_SMTP)




Setup

The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;

-- Instance restart only necessary in 10gR1.
SHUTDOWN IMMEDIATE
STARTUP

Send Emails

With the configuration complete we can now send a mail using the SEND procedure. It accepts the following parameters.

  • SENDER : This should be a valid email address.
  • RECIPIENTS : A comma-separated list of email addresses.
  • CC : An optional comma-separated list of email addresses.
  • BCC : An optional comma-separated list of email addresses.
  • SUBJECT : The subject line for the email.
  • MESSAGE : The email body.
  • MIME_TYPE : Set to 'text/plain; charset=us-ascii' by default.
  • PRIORITY : (1-5) Set to 3 by default.
  • REPLYTO : Introduced in 11gR2. A valid email address.

Below is an example of the usage.

BEGIN
  UTL_MAIL.send(sender     => 'me@domain.com',
                recipients => 'person1@domain.com,person2@domain.com',
                cc         => 'person3@domain.com',
                bcc        => 'myboss@domain.com',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;
/

Send Emails with Attachments

The package also supports sending mails with RAW and VARCHAR2 attachments using the SEND_ATTACH_RAW and SEND_ATTACH_VARCHAR packages respectively. They work in a similar fashion to the SEND procedure, with a few extra parameters.

  • ATTACHMENT : The contents of the attachment. This should be VARCHAR2 or RAW depending on which procedure you call.
  • ATT_INLINE : Indicates if the attachment should be readable inline. Default FALSE.
  • ATT_MIME_TYPE : The default is 'text/plain; charset=us-ascii'.
  • ATT_FILENAME : The name for the attachment.

Below is an example of sending an email with a text attachment.

BEGIN
  UTL_MAIL.send_attach_varchar (
    sender       => 'me@domain.com',
    recipients   => 'person1@domain.com,person2@domain.com',
    cc           => 'person3@domain.com',
    bcc          => 'myboss@domain.com',
    subject      => 'UTL_MAIL Test',
    message      => 'If you get this message it worked!',
    attachment   => 'The is the contents of the attachment.',
    att_filename => 'my_attachment.txt'    
  );
END;
/

  • 100% Practical Training

  • Hands
    On
    Training

  • 100%
    Job
    Gurantee

UTL_MAIL : Email from PL/SQL in the Oracle Database

Our Reviews 5 Star Rating: Recommended - Best IT Training in Chennai

5  out of 5  based on 12263 ratings.

Google          5 Star Rating: Recommended - Best IT Training in Chennai

Facebook      5 Star Rating: Recommended - Best IT Training in Chennai

UrbanPro       5 Star Rating: Recommended - Best IT Training in Chennai

Sulekha         5 Star Rating: Recommended - Best IT Training in Chennai

Yet5              5 Star Rating: Recommended - Best IT Training in Chennai

Justdial          5 Star Rating: Recommended - Best IT Training in Chennai


Oracle SQL Training in Chennai

Oracle SQL Training in Chennai
best Oracle SQL Training center in chennai

"I thought I knew Oracle SQL until I took this course. My company sent me here against my will. It was definitely worth and I found out how many things I was doing wrong. Karthik is awesome. but i got a lot inspired by you. I will keep in touch and will always try to learn from you as much as I can. Thanks once again Karthik"


Greens Technologys Overall Reviews


Greens Technologys Overall Reviews

5 out of 5 based on 17,981 ratings. 17,981 user reviews.





Relevant Courses