Today's example is a practical oldie on working with binary files in PLSQL. I recently had a requirement to attach a file to an email out of Concurrent Manager but the file could be one of several types. I frequently generated flat files as attachments but had never attached images to an email. Additionally, my previous examples would work with files that were stored in database tables as BLOBS. This article will explain the following two items:
1. create a temporary LOB from a BFILE
2. attach LOB to an email as a raw datatype
First, create a database directory that will contain the BLOB you want to attach to an email. In this example we will use a .jpg of my dog, Layla:
Next, I will FTP my file to the database tier to /usr/tmp:
The setup of utl_smtp is not covered in this topic - the author assumes your DBA configured this correctly and it is available for use. Below is the complete script to build and send the image as an attachment via utl_smtp. We'll discuss each part of it below after we run it and see the results:
set serveroutput on size 1000000
declare
--
lv_oracle_directory varchar2(240) := '/usr/tmp';
lv_file_name varchar2(240) := 'layla.jpg';
lv_smtp_host varchar2(240) := '192.168.X.X';
lv_list varchar2(240) := 'oracle-hack@hackity.com';
--
m_mail_conn utl_smtp.connection;
m_crlf varchar2(2) := utl_tcp.crlf; -- carriage return linefeed
m_boundary varchar2(60) := '6a_j9dh3sk2lao9vdse830';
m_first_boundary varchar2(60) := '--'|| m_boundary || m_crlf;
m_last_boundary varchar2(60) := '--'|| m_boundary || '--' || m_crlf;
--
lb_lob blob;
ln_amount number;
ln_position number;
lr_raw raw(2000);
--
l_bfile_source_location bfile;
li_size integer;
i pls_integer;
li_length pls_integer;
--
ln_max_base64_line_width constant pls_integer := 76 / 4 * 3;
--
begin
--
m_mail_conn := utl_smtp.open_connection(lv_smtp_host, 25);
--
utl_smtp.helo(m_mail_conn, lv_smtp_host);
utl_smtp.mail(m_mail_conn, 'Oracle_eBiz@hackity.com'); -- needs to look and feel like an email addy with '@' and '.'
utl_smtp.rcpt(m_mail_conn,lv_list);
utl_smtp.open_data(m_mail_conn);
--
-- MIME header
--
utl_smtp.write_data(m_mail_conn,'MIME-Version: 1.0' || utl_tcp.crlf);
utl_smtp.write_data(m_mail_conn, 'Content-Type: multipart/mixed; boundary="'||m_boundary||'"'||m_crlf);
utl_smtp.write_data(m_mail_conn, 'Subject: '||'Test attachment'||m_crlf||m_crlf);
--
-- email body text
--
utl_smtp.write_data(m_mail_conn, m_first_boundary);
utl_smtp.write_data(m_mail_conn, 'Content-Type: '||'text/plain'||m_crlf);
utl_smtp.write_data(m_mail_conn, ' charset=US-ASCII'||m_crlf);
utl_smtp.write_data(m_mail_conn,'Hello, world...see the attached file.'||m_crlf);
--
-- first attachment MIME header creation
--
utl_smtp.write_data(m_mail_conn, m_first_boundary);
utl_smtp.write_data(m_mail_conn, 'Content-Type: "image/jpeg"; name="'||lv_file_name||'"'||m_crlf);
utl_smtp.write_data(m_mail_conn, 'Content-Transfer-Encoding: base64'||m_crlf);
utl_smtp.write_data(m_mail_conn, 'Content-Disposition: attachment; filename="'||lv_file_name||'"'||m_crlf);
--
-- create a temporary LOB (versus storing in a table)
--
dbms_lob.createtemporary(lb_lob, true, dbms_lob.session);
l_bfile_source_location := bfilename('GVS_DOG', 'layla.jpg');
li_size := dbms_lob.getlength(l_bfile_source_location);
--
dbms_output.put_line('li_size: '||li_size);
dbms_lob.open(l_bfile_source_location);
--
dbms_lob.loadfromfile(dest_lob => lb_lob -- IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
,src_lob => l_bfile_source_location -- IN BFILE,
,amount => li_size); -- IN INTEGER,
--
i := 1;
li_length := dbms_lob.getlength(lb_lob);
dbms_output.put_line('length li_length: '||li_length);
--
while (i < li_length) loop
if(i + ln_max_base64_line_width < li_length)then
utl_smtp.write_raw_data (m_mail_conn
,utl_encode.base64_encode(dbms_lob.substr(lb_lob, ln_max_base64_line_width, i)));
else
utl_smtp.write_raw_data(m_mail_conn
,utl_encode.base64_encode(dbms_lob.substr(lb_lob, (li_length - i), i)));
end if;
utl_smtp.write_data(m_mail_conn, utl_tcp.crlf);
i := i + ln_max_base64_line_width;
end loop;
--
utl_smtp.write_data(m_mail_conn, m_crlf);
utl_smtp.write_data(m_mail_conn, m_last_boundary);
utl_smtp.write_data(m_mail_conn, m_crlf);
utl_smtp.close_data(m_mail_conn);
utl_smtp.quit(m_mail_conn);
--
dbms_lob.close(l_bfile_source_location);
dbms_lob.freetemporary(lb_lob);
commit;
--
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
rollback;
end;
/
Save this off as utl_smtp and run it from SQL*Plus:
A few seconds later there is a package:
Now that we know this works, let's take a brief look at some of the script:
- lv_smtp_host - this needs to be defined by your mail people. I have sometimes seen network aliases created so you can use mail.hostname.com in case the IP address changes
- The recipient is hard-coded as one value here and must be a valid email address format or you'll potentially see the error Error: ORA-29279: SMTP permanent error: 501 5.1.3 Bad recipient address syntax
- m_boundary - this arbitrary value is used throughout the mail message you build to separate each section. The value should not exist anywhere in the mail message. The initial boundary starts with two dashes and the last boundary of the email ends with two dashes.
- I copied this formula from several examples I found on the webernets. The first time I tried to attach the file by breaking it up into 1000 byte chunks it came through garbled. Not understanding this very well I then went to RFC 2045 (Multipurpose Internet Mail Extensions) for an explanation that states the file must be transferred in base64 encoding and also must be chunked into pieces no larger than 76 characters not including the carriage-return line feed.
- This address also nees to be in a valid format or you'll see error Error: ORA-29279: SMTP permanent error: 501 5.1.7 Bad sender address syntax
- Because this message is plain text as well as base64 you use this particular content-type This is also where you specifiy what the boundary string will be but do not use any dashes in front of it.
- The body of the email is created using a plain-text content-type and an ASCII character set. This is also where you specify the first boundary and it includes the "--"
- The same boundary is used but repeated here so separate the body from the attachment. base64 is specified as the encoding as well as the attachment and filename.
- This line creates a temporary lob. Previously I would pull LOBs out of tables using a SQL statement or procedure. In this example we want to grab it from the filesystem for use as an attachment. This line creates an empty lob ready to accept the file
- This will assign the file to the empty lob
- write the image attachment in 76 byte chunks
