CODEWITHSUNDEEP
oracle-databaseemailstored-proceduresoracle11gsmtp
Ingila Ejaz
Ingila Ejaz

Reputation: 233

Email .txt file as an attachment through Oracle Stored Procedure

I have a .txt file in which I have stored my data Semicolon-separated. The file is stored at backend in a Clob.

File:

ABCD;HEAD; 1.0;00204;18.05.2015;00000000;00000000000.00;18.05.2015

I need to send this text file as an attachment via Stored Procedure in Oracle.

Problem:

I get this encoded file attached in the Email:

4546554C3B484541443B20202020202020312E303B30303634323B31382E30352E323031353B30303030303030313B30303030303030303032302E30303B31382E30352E323031350A4546554C3B444154413B20202020202020312E303B4546554C3B303030543130303030303335303B313B31382E30352E323031353B303030303032302C3030302E30303B504B523B3132363435363135313231313B303235303533313030310D0A

Procedure:

CREATE OR REPLACE PROCEDURE SENDMAIL_ATT
(
MSG_FROM     VARCHAR2,
MSG_TO       VARCHAR2,
MSG_SUBJECT  VARCHAR2,
MSG_TEXT     VARCHAR2,
MSG_ATT      CLOB,
ATT_FILENAME VARCHAR2
) IS

V_MAILHOST VARCHAR2(50) := 'mail_server';
V_PORT     NUMBER(2) := 25;
V_HELO     VARCHAR2(50) := 'localhost';

C    UTL_TCP.CONNECTION;
RC   INTEGER;
CRLF VARCHAR2(2) := CHR(13) || CHR(10);
MESG VARCHAR2(32767);

V_BUFFER_SIZE CONSTANT PLS_INTEGER := 4000;
V_BUFFER_NEXT PLS_INTEGER := 4000;
V_BODY_BUFFER VARCHAR2(8000 CHAR);
BEGIN

C := UTL_TCP.OPEN_CONNECTION(V_MAILHOST, V_PORT); -- open the SMTP port
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'HELO ' || V_HELO);
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'MAIL FROM: ' || MSG_FROM); ----- MAIL BOX SENDING THE EMAIL
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'RCPT TO: ' || MSG_TO); ----- MAIL BOX RECIEVING   THE EMAIL
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'DATA'); ----- EMAIL MESSAGE BODY START
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C,'Date: ' ||
 TO_CHAR(SYSDATE, 'dd Mon yy hh24:mi:ss'));
  RC := UTL_TCP.WRITE_LINE(C, 'From: ' || MSG_FROM);
RC := UTL_TCP.WRITE_LINE(C, 'MIME-Version: 1.0');
RC := UTL_TCP.WRITE_LINE(C, 'To: ' || MSG_TO);
RC := UTL_TCP.WRITE_LINE(C, 'Subject: ' || MSG_SUBJECT);
RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
RC := UTL_TCP.WRITE_LINE(C, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
RC := UTL_TCP.WRITE_LINE(C); ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/html'); ----- 1ST BODY PART.    EMAIL TEXT MESSAGE
RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer-Encoding: 7bit');
RC := UTL_TCP.WRITE_LINE(C);
RC := UTL_TCP.WRITE_LINE(C, MSG_TEXT); ----- TEXT OF EMAIL MESSAGE
RC := UTL_TCP.WRITE_LINE(C);
RC := UTL_TCP.WRITE_LINE(C, '-------SECBOUND');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Type: text/plain;'); ----- 2ND BODY PART.
RC := UTL_TCP.WRITE_LINE(C, ' name="' || ATT_FILENAME || '"');
RC := UTL_TCP.WRITE_LINE(C, 'Content-Transfer_Encoding: 7bit'); 
RC := UTL_TCP.WRITE_LINE(C, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
RC := UTL_TCP.WRITE_LINE(C, ' filename="' || ATT_FILENAME || '"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
RC := UTL_TCP.WRITE_LINE(C);

--CLOB 
FOR I IN 0 .. FLOOR(DBMS_LOB.GETLENGTH(MSG_ATT) / V_BUFFER_SIZE)
LOOP
DBMS_LOB.READ(MSG_ATT,
V_BUFFER_NEXT,
I * V_BUFFER_SIZE + 1,
V_BODY_BUFFER);

RC := UTL_TCP.WRITE_TEXT(C, V_BODY_BUFFER);

END LOOP;

RC := UTL_TCP.WRITE_LINE(C);
RC := UTL_TCP.WRITE_LINE(C, '.'); ----- EMAIL MESSAGE BODY END
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
RC := UTL_TCP.WRITE_LINE(C, 'QUIT'); ----- ENDS EMAIL TRANSACTION
DBMS_OUTPUT.PUT_LINE(UTL_TCP.GET_LINE(C, TRUE));
UTL_TCP.CLOSE_CONNECTION(C); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
WHEN OTHERS THEN
RAISE;
END SENDMAIL_ATT;

Procedure Call:

declare
 -- Local variables here
l_clob   clob;
fileName varchar2(100);
begin

SELECT file_name, file_content
INTO fileName, l_clob
FROM my_table
WHERE ID = 141;

SENDMAIL_ATT(MSG_FROM     => '[email protected]',
           MSG_TO       => '[email protected]',
           MSG_SUBJECT  => 'Test Email',
           MSG_TEXT     => 'Some Subject',
           MSG_ATT      => l_clob,
           ATT_FILENAME => fileName);

end;

Upvotes: 0

Views: 4152

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

I use this package to send out mails. It works fine with attachments, try it out:

CREATE OR REPLACE PACKAGE BODY SENDMAIL_PKG AS

PRIORITY_HIGH           CONSTANT INTEGER := 1;
PRIORITY_NORMAL         CONSTANT INTEGER := 3;
PRIORITY_LOW            CONSTANT INTEGER := 5;

SMTP_PORT               CONSTANT INTEGER := 25;
SMTP_SERVER             CONSTANT VARCHAR2(50) := 'mailhost';
SMTP_DOMIAN             CONSTANT VARCHAR2(50) := SYS_CONTEXT('USERENV', 'DB_DOMAIN');
MIME_BOUNDARY           CONSTANT VARCHAR2(50) := '=====Boundary======';
MIME_MIXED              CONSTANT VARCHAR2(50) := 'multipart/mixed;';
MIME_TEXT               CONSTANT VARCHAR2(50) := 'text/plain;';

DEFAULT_FROM_NAME       CONSTANT VARCHAR2(50) := SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA');
DEFAULT_FROM_MAIL       CONSTANT VARCHAR2(50) := DEFAULT_FROM_NAME||'@'||SMTP_DOMIAN;



FUNCTION OpenConnection(ToEmail IN VARCHAR2, FromEmail IN VARCHAR2) RETURN UTL_SMTP.connection IS
    con UTL_SMTP.connection;
BEGIN
    -- setup mail header
    con := UTL_SMTP.OPEN_CONNECTION(SMTP_SERVER, SMTP_PORT);
    UTL_SMTP.helo(con, SMTP_DOMIAN);
    UTL_SMTP.Mail(con, LOWER(FromEmail));
    UTL_SMTP.rcpt(con, LOWER(ToEmail));
    UTL_SMTP.open_data(con);
    RETURN con;
END OpenConnection;


PROCEDURE PutHeader(con IN OUT UTL_SMTP.connection, 
    Subject IN VARCHAR2, ToEmail IN VARCHAR2, 
    FromEmail IN VARCHAR2, FromName IN VARCHAR2,
    Priority IN INTEGER) IS
BEGIN
    UTL_SMTP.write_data(con, 'From: "'||FromName||'" <'||LOWER(FromEmail)||'>'||UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'To: <'||LOWER(ToEmail)||'>'||UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Subject: '||Subject||UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'X-Priority: '||Priority||UTL_TCP.CRLF);
END PutHeader;


PROCEDURE CloseMail(con IN OUT UTL_SMTP.connection) IS
BEGIN
    -- finish mail
    UTL_SMTP.close_data(con);
    UTL_SMTP.quit(con);
END CloseMail;

-- Append default footer at the end of mail
PROCEDURE PutFooter(con IN OUT UTL_SMTP.connection) IS
    sender VARCHAR2(1000);
BEGIN
    -- put footer to end of mail
    UTL_SMTP.write_data(con, UTL_TCP.CRLF || UTL_TCP.CRLF);
    sender := SYS_CONTEXT('USERENV', 'DB_NAME')||'.'||SYS_CONTEXT('USERENV', 'DB_DOMAIN');
    UTL_SMTP.write_data(con, 'Message from '||sender||' sent at '||TO_CHAR(LOCALTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss'));   
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
END PutFooter;


-- Send Mail with CLOB attachment
PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN VARCHAR2, 
    ToEmail IN VARCHAR2,
    Attachment IN CLOB, FilenameAttachment IN VARCHAR2,
    FromEmail IN VARCHAR2 DEFAULT DEFAULT_FROM_MAIL,
    FromName IN VARCHAR2 DEFAULT DEFAULT_FROM_NAME,
    Priority IN INTEGER DEFAULT PRIORITY_NORMAL) IS

    con UTL_SMTP.connection;

    AttachmentLength PLS_INTEGER;
    amount BINARY_INTEGER := 30000;
    BUFFER VARCHAR2(32000);
    offset PLS_INTEGER := 1;

BEGIN

    con := OpenConnection(ToEmail, FromEmail);
    PutHeader(con, Subject, ToEmail, FromEmail, FromName, Priority);

    UTL_SMTP.write_data(con, 'Mime-Version: 1.0' || UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Content-Type: '||MIME_MIXED||' boundary="'||MIME_BOUNDARY||'"' || UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF); 
    UTL_SMTP.write_data(con, 'Content-type: text/plain' || UTL_TCP.CRLF); 

    -- Mail Body
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(Message));
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    PutFooter(con);

    -- Mail Attachment
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, '--'||MIME_BOUNDARY || UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Content-Type: '||MIME_TEXT||' name="'||FilenameAttachment||'"'|| UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, 'Content-Disposition: attachment; filename="'||FilenameAttachment||'"'|| UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);

    AttachmentLength := DBMS_LOB.GETLENGTH(Attachment);
    LOOP
        EXIT WHEN offset > AttachmentLength;
        DBMS_LOB.READ(Attachment, amount, offset, BUFFER);
        UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(BUFFER));
        offset := offset + amount;
    END LOOP;
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_data(con, '--'||MIME_BOUNDARY||'--' || UTL_TCP.CRLF);

    CloseMail(con);

END SendMail;



-- Send plain Mail without attachment
PROCEDURE SendMail(
    Subject IN VARCHAR2, 
    Message IN VARCHAR2, 
    ToEmail IN VARCHAR2,
    FromEmail IN VARCHAR2 DEFAULT DEFAULT_FROM_MAIL,
    FromName IN VARCHAR2 DEFAULT DEFAULT_FROM_NAME,
    Priority IN INTEGER DEFAULT PRIORITY_NORMAL) IS

    con UTL_SMTP.connection;

BEGIN

    con := OpenConnection(ToEmail, FromEmail);
    PutHeader(con, Subject, ToEmail, FromEmail, FromName, Priority);
    UTL_SMTP.write_data(con, 'Content-type: '||MIME_TEXT ||' charset='||GetCharset||UTL_TCP.CRLF );
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    UTL_SMTP.write_raw_data(con, UTL_RAW.cast_to_raw(Message));
    UTL_SMTP.write_data(con, UTL_TCP.CRLF);
    PutFooter(con);
    CloseMail(con);

END SendMail;



END SENDMAIL_PKG;

Upvotes: 2

Related Questions