In my previous article I have given the details about multiple types of triggers with its real life industry examples. In this article I would like to give information about another important topic which has been used to send the mails in PL/SQL program. There are so many times requirements to send the mail after updates,inserts or deletion of records. These kind of all settings will be handled by oracle in built package UTL_MAIL package which will provide the way to send the mail through oracle engine. The mail is always sending using SMTP protocol – Simple Mail Transfer Protocol.
What you will see in this article?
1.What is UTL_MAIL package and its multiple types?
2.How to send mail using oracle engine and SMTP protocol?
What is UTL_MAIL package and its multiple types?
The UTL_MAIL package is the utility which is used to manage the sending mail using oracle and SMTP protocol. The UTL_MAIL package is very important package to manage the mails. There are so many requirements in real world which needs to use this package.
Initial Steps to use UTL_MAIL package :
Step 1 : Install the UTL_MAIL package
The first step is to install UTL_MAIL package. Just remember that the following is path of SQL files. DBA need to install this package using following path.
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
Step 2 : SMTP_OUT_SERVER parameter set up
The second step is to set up the SMTP_OUT_SERVER parameter. The DBA team needs to set this parameter and add the SMTP details to this parameter.
Step 3 : DBA needs to add the privileges’
We require to add and give the specified privileges to the User to execute this package.
Step 4 : Add the ACL record
The DBA need to add Record in ACL (Access Control List) using package called DBMS_NETWORK_ACL_ADMIN
Step 5 : Understanding different procedures of UTL_MAIL package
Now with using above steps user can use the UTL_MAIL package. There are more procedures of UTL_MAIL package. Here we will see with example how to use UTL_mail package with multiple procedures.
These are above three different procedures for sending the mail in PL/SQL. How to use that we will see with example in next section.
How to send mail using oracle engine and SMTP protocol?
In this section we will see the detailed steps and example of sending mail using oracle engine. I am taking example on my system and will try to show you stepwise how to add this feature. Most of the time this kind of feature is useful to fulfill business needs. If you are using ERP system and creating the account. After creating the account user will get the mail. This is most simple example for sending the mail
Real life example :
Step 1 : In order to send the mail we require to install UTL_MAIL package
Connect to Sysdba
Sys as sysdba
execute the following SQL which is on pluggable databases (I am taking path on my system)
@C:\app\Amit\product\12.2.0\dbhome_1\rdbms\admin\utlmail.sql
@C:\app\Amit\product\12.2.0\dbhome_1\rdbms\admin\prvtmail.plb
Step 2 : Give the grants to package
GRANT execute ON utl_mail TO public;
Step 3 : Add record to ACL
The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer
The network Access Control List (ACL).
Step 4 : Set smtp_out_server parameter
alter system set smtp_out_server = ‘mail.xxx.ae.ac:33’;
Step 5 : Procedure creation and use the package
Syntax :
BEGIN
UTL_MAIL.send(sender => ‘complexsql@gmail.com’,
recipients => ‘complexsql@hotmail.com’,
subject => ‘This is Test mail’,
message => ‘Hello World’,
mime_type => ‘text; charset=us-ascii’);
END;
Using the above steps you can send the mail .
Scenario 2 : If you want to send image attached mail Kindly refer following syntax.
Example :
—Create function to get the image
Create
function get_image (P_DIR VARCHAR2,P_FILE_NAME VARCHAR2)
RETURN RAW
IS
IMAGE raw(32767);
f bfile :=bfilename(P_DIR,P_FILE_NAME);
–BFILENAME returns a BFILE locator that is associated
–with a physical LOB binary file on the server file system
BEGIN
–This procedure opens a BFILE for read-only access.
dbms_lob.fileopen(f,dbms_lob.file_readonly);
IMAGE:= dbms_lob.substr(f);
dbms_lob.close(f);
return IMAGE;
END;
–Send mail
BEGIN
utl_mail.send_attach_raw(sender => ‘complexsql@gmail.com’,
recipients => ‘complexsql@hotmail.com’,
subject => ‘Test Mail with Attachment of image’,
message => ‘kindly find the attachment’,
mime_type => ‘text; charset=us-ascii’,
attachment=> get_image(‘AMIT’,’Amit.jpg’), —calling the function
att_inline=>true,
att_mime_type=>’image/jpg’,
att_filename=>’Amit.jpg’
);
END;
With using the same process you can send attachment. If you want more details on it kindly comment in comments section. If you like this article or if you have any issues with the same kindly comment in comments section.