SQL & PL/SQL

Dear All

OS -- HP UX --

directory created as dir_mmail path /tmp/sayeed
grants given to public for read write

email procedure ---
CREATE OR REPLACE procedure DBA_HO.Emailattach1 is
/*LOB operation related varriables */
v_src_loc BFILE := BFILENAME('DIR_MMAIL', 'imran.jpeg');
l_buffer RAW(54);
l_amount BINARY_INTEGER := 54;
l_pos INTEGER := 1;
l_blob BLOB := EMPTY_BLOB;
l_blob_len INTEGER;
v_amount INTEGER;
v_connection_handle UTL_SMTP.CONNECTION;
v_from_email_address VARCHAR2(80) := 'itmakropak@makropak.com';
v_to_email_address VARCHAR2(80) := 'sayeed@makropak.com';
v_smtp_host VARCHAR2(80) := 'mailhost.makropak.com';
v_subject VARCHAR2(80) := 'Makro Mail Test Mail';
l_message VARCHAR2(200) := 'This is test mail using UTL_SMTP';

/* This send_header procedure is written in the documentation */
PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(v_connection_handle,
pi_name || ': ' || pi_header || UTL_TCP.CRLF);
END;
BEGIN
/*Preparing the LOB from file for attachment. */
DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
l_blob_len := DBMS_LOB.getlength(l_blob);

/*UTL_SMTP related coding. */
v_connection_handle := UTL_SMTP.OPEN_CONNECTION(v_smtp_host, 25);
UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
UTL_SMTP.OPEN_DATA(v_connection_handle);
send_header('From', '"Sender"');
send_header('To', '"Recipient"');
send_header('Subject', v_subject);

--MIME header.
UTL_SMTP.WRITE_DATA(v_connection_handle,
'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' boundary= "' || 'DIR_MMAIL.SECBOUND' || '"' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);


-- Mail Body
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'DIR_MMAIL.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: text/plain;' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' charset=US-ASCII' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);


-- Mail Attachment
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || 'DIR_MMAIL.SECBOUND' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Type: application/octet-stream' ||
UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
' filename="' || 'imran.jpeg' || '"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
/* Writing the BLOL in chunks */
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
UTL_SMTP.write_raw_data(v_connection_handle,
UTL_ENCODE.BASE64_ENCODE(l_buffer));
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
l_buffer := NULL;
l_pos := l_pos + l_amount;
END LOOP;
UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);


-- Close Email
UTL_SMTP.WRITE_DATA(v_connection_handle,
'--' || ' DIR_MMAIL.SECBOUND' || '--' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(v_connection_handle,
UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);


UTL_SMTP.CLOSE_DATA(v_connection_handle);
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
DBMS_LOB.FILECLOSE(v_src_loc);


EXCEPTION
WHEN OTHERS THEN
UTL_SMTP.QUIT(v_connection_handle);
DBMS_LOB.FREETEMPORARY(l_blob);
DBMS_LOB.FILECLOSE(v_src_loc);
RAISE;
END;
/

Procedure created.

SQL> execute emailattach1;

PL/SQL procedure successfully completed.

Please let me know how i can find where is problem or why i am not getting email. If i use this procedure simpl (no attachment) then i get email.

Regards

Sayeed