Sending Email With Attachment

sending email message from Oracle database ( note that we can send mail form the Forms by using javamail ) is very simple , specially starting form the 10 g release UTL_Mail package that make the issue very simple , but have limitation in the size of the attachment , so we can customize the old demo_mail package that supplied with oracle to send mail with attachment by changing some code in Attach_base64 procedure to be like the following :

PROCEDURE ATTACH_BASE64
(P_CONN IN OUT NOCOPY UTL_SMTP.connection
,P_DATA IN BLOB
,P_MIME_TYPE IN VARCHAR2 := 'application/octet'
,P_INLINE IN BOOLEAN := TRUE
,P_FILENAME IN VARCHAR2 := NULL
,P_LAST IN BOOLEAN := FALSE
)
IS
i PLS_INTEGER;
len PLS_INTEGER;
BEGIN
attach_demo_mail.BEGIN_ATTACHMENT(p_conn, p_mime_type, p_inline, p_filename, 'base64');
-- Split the Base64-encoded attachment into multiple lines
i := 1;
len := DBMS_LOB.getLength(p_data);
WHILE (i < len) LOOP
IF(i + MAX_BASE64_LINE_WIDTH < len)THEN
UTL_SMTP.Write_Raw_Data (p_conn
, UTL_ENCODE.Base64_Encode(
DBMS_LOB.Substr(p_data, MAX_BASE64_LINE_WIDTH, i)
)
);
ELSE
UTL_SMTP.Write_Raw_Data (p_conn
, UTL_ENCODE.Base64_Encode(
DBMS_LOB.Substr(p_data, (len - i), i)
)
);
END IF;
UTL_SMTP.Write_Data(p_conn, UTL_TCP.CRLF);
i := i + MAX_BASE64_LINE_WIDTH;
END LOOP;
END_ATTACHMENT(p_conn, p_last);
END;
------------------------------------------------------------------------
After that you can make your private pkg the use the supplied package to sned your email with different type of attachment ( you can customize the code below according to your requirement)

Pre - requisities -- create your own table to load the blob files to it
CREATE TABLE GN_BLOB
(
BLOB_RECID NUMBER,
TEMP_FILE BLOB
)

CREATE OR REPLACE PACKAGE TMS.mail_Attach_pkg AS
/******************************************************************************
NAME: mail_Attach_pkg
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 10/08/2009 1. Created this package.
******************************************************************************/

procedure p_load_file_to_db(p_file_name varchar2,p_recid out number);
PROCEDURE p_MAIL_WITH_ATTACHMENT(p_attach_type number,p_file_name varchar2,p_sender varchar2,p_receipients varchar2 ,p_subject varchar2,p_message varchar2);

END mail_Attach_pkg;
/

CREATE OR REPLACE PACKAGE BODY TMS.mail_attach_pkg AS
/******************************************************************************
NAME: mail_attach_pkg
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 10/08/2009 1. Created this package body.
******************************************************************************/
procedure p_load_file_to_db(p_file_name varchar2,p_recid out number)is
LOBD BLOB;
FILS BFILE;
AMT INT;
v_Recid number(16);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

-- get the record id for the blob

select seq_GN_blob.nextval into v_Recid from dual;

-- load html file to database

INSERT INTO GN_blob(temp_file,blob_recid) -- this your table that you will save the blob file to use it after that
VALUES(EMPTY_BLOB(),v_Recid)
RETURNING Temp_FILE INTO LOBD;

FILS:=BFILENAME('TEST_DIR', p_file_name);
DBMS_LOB.FILEOPEN(FILS, DBMS_LOB.FILE_READONLY);
AMT:= DBMS_LOB.GETLENGTH(FILS);
DBMS_LOB.LOADFROMFILE(LOBD, FILS, AMT); -- LOAD THE FILE directy to the data base
DBMS_LOB.FILECLOSE(FILS);
COMMIT;

p_Recid:=v_recid ;

end ;
--****************************************************************************************************************
PROCEDURE p_MAIL_WITH_ATTACHMENT(p_attach_type number ,p_file_name varchar2,p_sender varchar2,p_receipients varchar2 ,p_subject varchar2,p_message varchar2) is
t_conn UTL_SMTP.Connection;
t_blob BLOB;
-- cCRLF ; --VarChar2(2) := CHR(13) CHR(10);

v_Recid number(16);
BEGIN

--- load the file to the database
P_LOAD_FILE_TO_DB(p_file_name,v_recid);

-- load BLOB
DBMS_LOB.CREATETEMPORARY(t_blob, FALSE);
SELECT gn_blob.TEMP_FILE INTO t_blob FROM GN_blob WHERE blob_recid = v_recid;

-- start mail
t_conn := Attach_DEMO_MAIL.Begin_Mail(p_sender,
p_receipients,
p_subject,
attach_DEMO_MAIL.MULTIPART_MIME_TYPE);

-- some inline html text first line
attach_DEMO_MAIL.Attach_Text(t_conn, 'Dear sirs 'UTL_TCP.CRLF
' Good Day kindly find our attached report haythem mousa 'UTL_TCP.CRLF, 'text/plain');

--- second line
---attach_DEMO_MAIL.Attach_Text(t_conn, 'second line 'UTL_TCP.CRLF
---- ' 22222t 'UTL_TCP.CRLF, 'text/plain');

-- attachment
-- 'application/pdf' for PDF files
-- DEMO_MAIL.Attach_Base64(t_conn, t_blob, 'application/x-zip-compressed', TRUE, 'file.zip');
if p_attach_type=1 then
Attach_DEMO_MAIL.Attach_Base64(t_conn, t_blob, 'text/txt', TRUE, 'test.txt');
elsif p_attach_type=2 then
Attach_DEMO_MAIL.Attach_Base64(t_conn, t_blob, 'image/jpg', TRUE, 'sample.jpg');
elsif p_attach_type=3 then
Attach_DEMO_MAIL.Attach_Base64(t_conn, t_blob, 'application/pdf', TRUE, 'sample.pdf');
end if ;

-- some html attachment
---html attachment
--c_DEMO_MAIL.Attach_Text(t_conn, 'This is a HTML report.'
--cCRLF' good morning ', 'text/plain', FALSE,null);

-- end connection
attach_DEMO_MAIL.End_Mail(t_conn);

--DBMS_LOB.FREETEMPORARY(t_blob);
COMMIT;
END;

END mail_attach_pkg;
/

you can customize the code above to add,edit, delete features