This content has been marked as final. Show 4 replies
Your answer is helpful in understanding the utl_mail. I am even able to send emails using utl_mail.
But my requirement is , i should be able to send an email with attachments by just passing the
file name and the oracle directory name. In my example these are 'test.csv' and 'dir_temp'
Any one used such procedure before?
But my requirement is , i should be able to send an email with attachments by just passing the file name and the oracle directory name.Here's another example:
Just change the pl/sql block to a storef procedure, and adjust as you wish.
One thing though:
Since UTL_MAIL is restricted to 32k, you might need the javamail api:
Try to ctrl+f useful examples over there.
This link might be of use as well:
user12225636 wrote:You do not need a directory object. You do not need a csv file an an attachment. Nor do you need to install a SMTP server.
I have created a Oracle directory "dir_temp"
create or replace directory dir_temp as 'c:\temp';
I am able to create a csv file in this directory as "test.scv" using utl_file and some select statements.
Now I need to send this csv file as an attachment in email.
we have smtp installed on our server.
UTL_MAIL in Oracle is a SMTP client. You do not need to install any SMTP software on the Oracle server to make the client part work. What you do need is access to your network/company's mail server. This means that mail server must accept client connections from your Oracle server. The firewalls in place must allow tcp traffic on port 25 through from the Oracle server to the mail server.
As for attachments. Attachments are not files. Attachments are part and parcel of the e-mail itself. An e-mail is a MIME (Multipurpose Internet Mail Extensions) body. It contains a header part that includes the subject, Mime type, date, recipient and sender data and so on.
After the header, there's a single black line, and then the message body. The body can be in several parts. Where a single part can be an attachment part.
UTL_MAIL creates a MIME body for you. It allows you to specify:
- the header (the subject, recipient, sender, etc)
- the text body
- a single attachment part
Note: modify CR as applicable to your platform (e.g. requiring carriage return and linefeed or only one of these).
SQL> declare 2 CR constant varchar2(2) := chr(10); 3 begin 4 SYS.UTL_MAIL.send_attach_varchar2( 5 sender => 'email@example.com', 6 recipients => 'firstname.lastname@example.org', 7 subject => 'Sample Mail from UTL_MAIL', 8 mime_type => 'text/plain', -- we're sending a plain text e-mail 9 message => 'This is the e-mail message part that the mail reader will display.', 10 att_mime_type => 'text/csv', -- we're adding a csv text attachment 11 att_filename => 'sample.csv', -- with this as the file name 12 -- and here is the attachment's contents/data: 13 attachment => 'col1,col2,col3'||CR||'col1,col2,col3'||CR||'col1,col2,col3' 14 ); 15 end; 16 / PL/SQL procedure successfully completed.
The following MIME body is created and delivered via SMTP:
As you can see, the CSV file is not a file - it is part of the MIME body and no physical file was required to be attached. That is a requirement for a mail reader like Outlook - that is not a requirement for using the SMTP protocol or constructing a MIME body.
From: email@example.com To: firstname.lastname@example.org Subject: =?ISO-8859-1?Q?Sample=20Mail=20from=20UTL=5FMAIL?= X-Priority: 3 Content-Type: multipart/mixed; boundary="------------4D8C24=_23F7E4A13B2357B3" Return-Path: email@example.com Date: 19 Nov 2009 07:45:15 +0200 Mime-Version: 1.0 This is a multi-part message in MIME format. --------------4D8C24=_23F7E4A13B2357B3 Content-Type: text/plain Content-Transfer-Encoding: 7bit This is the e-mail message part that the mail reader will display. --------------4D8C24=_23F7E4A13B2357B3 Content-Type: text/csv; name="sample.csv" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline; filename="sample.csv" col1,col2,col3 col1,col2,col3 col1,col2,col3 --------------4D8C24=_23F7E4A13B2357B3--