CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX SENDING THE EMAIL
msg_to varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX RECIEVING THE EMAIL
msg_subject varchar2 := 'Output file TEST1', ----- EMAIL SUBJECT
msg_text varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1 varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
c utl_tcp.connection;
rc integer;
crlf VARCHAR2(2):= CHR(13)||CHR(10);
mesg VARCHAR2( 32767 );
BEGIN
c := utl_tcp.open_connection('196.35.140.18', 25); ----- OPEN SMTP PORT CONNECTION
rc := utl_tcp.write_line(c, 'HELO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
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||' <'||msg_from||'>');
rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
rc := utl_tcp.write_line(c, 'To: '||msg_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, ''); ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 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="Test.txt"');
rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, v_output1);
rc := utl_tcp.write_line(c, '-------SECBOUND--');
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_application_error(-20000, SQLERRM);
END;
/
msg_from varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX SENDING THE EMAIL
msg_to varchar2 := 'EMAILADDRESS@DOMAIN.COM', ----- MAIL BOX RECIEVING THE EMAIL
msg_subject varchar2 := 'Output file TEST1', ----- EMAIL SUBJECT
msg_text varchar2 := 'THIS IS THE TEXT OF THE EMAIL MESSAGE.',
v_output1 varchar2 := 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT SHOULD BE IN A TEXT FILE ATTACHED TO THE EMAIL.')
IS
c utl_tcp.connection;
rc integer;
crlf VARCHAR2(2):= CHR(13)||CHR(10);
mesg VARCHAR2( 32767 );
BEGIN
c := utl_tcp.open_connection('196.35.140.18', 25); ----- OPEN SMTP PORT CONNECTION
rc := utl_tcp.write_line(c, 'HELO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'EHLO 196.35.140.18'); ----- PERFORMS HANDSHAKING WITH SMTP SERVER, INCLUDING EXTRA INFORMATION
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||' <'||msg_from||'>');
rc := utl_tcp.write_line(c, 'MIME-Version: 1.0');
rc := utl_tcp.write_line(c, 'To: '||msg_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, ''); ----- INSERTS A BLANK LINE. PART OF THE MIME FORMAT AND NONE OF THEM SHOULD BE REMOVED.
rc := utl_tcp.write_line(c, '-------SECBOUND');
rc := utl_tcp.write_line(c, 'Content-Type: text/plain'); ----- 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="Test.txt"');
rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
rc := utl_tcp.write_line(c, ' filename="Test.txt"'); ----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, v_output1);
rc := utl_tcp.write_line(c, '-------SECBOUND--');
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_application_error(-20000, SQLERRM);
END;
/
No comments:
Post a Comment