How to Send Email From Oracle Database 12C | Easy Guideline

Using the UTL_MAIL package can be an ideal choice if you want to send emails from Oracle Database 12c. This package provides an interface to send email messages directly from your database environment. Hence, there’s no need for third-party email services. 

Also, the setup process is not that hard. You only have to configure the SMTP server settings and run some SQL code. Here’s a step-by-step guide on how to send an email from your Oracle Database 12c.

How to Send an Email in Oracle?

The below process demonstrates how to send email from PL SQL procedure in Oracle database using UTL_MAIL.

Configure SMTP Server Settings

Before you can send emails from your Oracle Database 12c, you need to configure the SMTP server settings from within the database. The process includes setting the server hostname, port, and authentication details. Here’s an example.

BEGIN
   UTL_MAIL.SET_SMTP_HOST('smtp.example.com');
   UTL_MAIL.SET_SMTP_PORT(25); -- Use the appropriate port number
   UTL_MAIL.SET_AUTH('your_username', 'your_password');
END;
/

Send an Email

After configuring the SMTP server settings, use the SEND procedure of the UTL_MAIL package to send an email.

DECLARE
   v_to       VARCHAR2(100) := '[email protected]';
   v_subject  VARCHAR2(100) := 'Test Email';
   v_message  VARCHAR2(4000) := 'This is a test email sent from Oracle Database 12c.';
BEGIN
   UTL_MAIL.SEND(sender => '[email protected]',
                 recipients => v_to,
                 subject => v_subject,
                 message => v_message);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Email sent successfully.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      ROLLBACK;
END;
/

Send Emails With Attachments

The UTL_MAIL package also lets you attach files to an email with a procedure called ATTACH_BINARY_DATA.

DECLARE
   v_to       VARCHAR2(100) := '[email protected]';
   v_subject  VARCHAR2(100) := 'Email with Attachment';
   v_message  VARCHAR2(4000) := 'Please find the attached file.';
   v_filename VARCHAR2(100) := 'attachment.txt';
   v_content  RAW(32767);
BEGIN
   -- Read the content of the file into v_content
   SELECT UTL_RAW.CAST_TO_RAW(BFILENAME('DIRECTORY_NAME', v_filename))
   INTO v_content
   FROM DUAL;
   UTL_MAIL.SEND_ATTACH(sender => '[email protected]',
                        recipients => v_to,
                        subject => v_subject,
                        message => v_message,
                        mime_type => 'text/plain',
                        filename => v_filename,
                        data => v_content);
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Email with attachment sent successfully.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      ROLLBACK;
END;
/

FAQs – Frequently Asked Questions and Answers

What is UTL_MAIL in Oracle?

UTL_MAIL is a package in Oracle that lets you send email messages with essential email features like attachments. You can even add CC and BCC to your email and send it to multiple receivers at the same time.

What is the difference between UTL_SMTP and UTL_MAIL?

While UTL_SMTP is quite intricate and somewhat outdated, the Oracle UTL_MAIL is the newer, improved option for sending emails.

What is the difference between SMTP and webmail?

While SMTP is an email protocol for transferring emails between servers, webmail is a web interface for end users to manage their email accounts. Generally, SMTP is only accessible to server administrators, but webmail is accessible by end users through web browsers.

Conclusion

Sending emails directly from your Oracle Database 12c can have security and performance implications. Therefore, always try configuring proper access controls, use safe authentication, and optimize your sending process. These are the best practices for sending emails directly from Oracle while skipping external tools.