Let’s take a look on how to send mail in Oracle DBMS using the UTL_SMTP package in a step-by-step basis:
- Declare some variables and store some key information (ie. SMTP server address and port, the From email address and the To email address):
- Connecting to SMTP server and inform addresses informations
- Write the mail data in the connection – including subject and message body.
- Close the connection with the SMTP server
Now, let’s take a look at the full code sample:
Hi everyone
Can anyone help with a doc on how to send a query result by email from oracle dB!
Thanks
Hi Farik,
You can send data on the message body or as attachment, but the best way depends on the number of columns/rows and on their datatypes.
If your query returns just a few rows and columns of simple (i.e. strings, numbers or dates) data, you can choose to send the results in the message body. To do that, you may concat the values in each row and write them into the mail body. You should use a loop to write all the lines into the message.
string_line varchar2(2000) default null;col_sep varchar2(1) default null;
begin
col_sep := '; ';
row_sep := ' - ';
for results in ( select col1, col2 from table inner join ... where ... ) loop
string_line := results.col1 || string_sep || results.col2;
utl_smtp.write_raw_data(ServerConnection, utl_raw.cast_to_raw(utl_tcp.crlf||string_line));
end loop;
end;
But the above solution may not be the most appropriate for most situations, where a query returns several lines and/or columns, or even for queries that returns binary data (e.g. representing binary files, like PDF files, image files, …). In such situations, you should send the results as an attachment.
Thank you for your quick reply to my request.
In fact I got 2 cases to sort out : – the 1st one is a notification email fetching data from my Database.
-the second case is a to set a trigger and send and email in case of insert or update a new row.
Thanks in advance for your help and support
Hi Farik,
First of all, you may create a “generic” stored procedure that sends a email making just a few changes to the code in this post. Such procedure would have at least 3 parameters: the email address of the message’s recipient, the message’s subject and the message’s body. Such parameters whould replace the corresponding variables we used in this post’s code. Then, your insert/update trigger may call this procedure to send the mail. The notification email you mention in your first use case may be sent from your client application, but if you want to use Oracle to send such message, then just call the “generic” procedure you created.
Alright thanks u for the response… I’ll try it.
Very useful