sending mail

[mylena]

Hello.
It's my first message on this forum.
I am using Oracle 9i with Developer 6i.
How can i make that by pressing a button to send an e-mail to a certain person with a predefined text merged with some changing variables (like name, cost)?
Thanks.

 

[HimanB]

Hi,
Following procedure will help you to send text as well attachements.
This process makes use of SAMTP to send mails.

/* ** ****************************************************************************************** ** *
** DESCRIPTION OF SCRIPT : THIS PROCESS IS FOR SENDING MAIL WITH THE ATTACHMENT * ** ****************************************************************************************** ** */

CREATE OR REPLACE PROCEDURE MAIL_FILE( SUBJECT IN VARCHAR2,
MESSAGE IN VARCHAR2,
MAX_SIZE IN NUMBER DEFAULT 9999999999,
FILENAME1 IN VARCHAR2 DEFAULT NULL,
FILENAME2 IN VARCHAR2 DEFAULT NULL,
FILENAME3 IN VARCHAR2 DEFAULT NULL,
RETURN_DESC OUT VARCHAR2) IS

L_SMTP_SERVER VARCHAR2(20); /** TO STORE THE IP ADDRESS OF THE SMTP SERVER **/
L_SMTP_SERVER_PORT NUMBER; /** TO STORE THE SMTP PORT OF THE SMTP SERVER **/
L_DIRECTORY_NAME VARCHAR2(200); /** TO STORE THE PATH / DIRECTORY NAME OF THE FILE **/
L_FILE_NAME VARCHAR2(100); /** TO STORE THE FILENAME **/
L_LINE VARCHAR2(1000); /** TO STORE THE CONTENTS OF THE LINE READ FROM THE FILE **/
CRLF VARCHAR2(2):= CHR(13) || CHR(10);
L_MESG VARCHAR2(32767); /** TO STORE THE MESSAGE **/
CONN UTL_SMTP.CONNECTION; /** SMTP CONNECTION VARIABLE **/
L_MSG_TO VARCHAR2(2000); /** TO STORE THE LIST OF RECIPEINTS **/
L_SENDER_NAME VARCHAR2(200); /** TO STORE THE NAME OF THE SENDER **/

TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;

FILE_ARRAY VARCHAR2_TABLE; /** AN ARRAY TO STORE THE FILE NAMES **/
I BINARY_INTEGER; /** ARRAY INDEX **/

L_FILE_HANDLE UTL_FILE.FILE_TYPE; /** FILE POINTER **/
L_SLASH_POS NUMBER; /** TO STORE THE POSITION OF \ IN THE FILE NAME **/

L_MESG_LEN NUMBER; /** TO STORE THE LENGHT OF THE MESSAGE **/

ABORT_PROGRAM EXCEPTION; /** USER DEFINED EXCEPTION **/

MESG_LENGTH_EXCEEDED BOOLEAN := FALSE; /** BOOLEAN VARIABLE TO TRAP IF THE MESSAGE LENGHT IS EXCEEDING **/

RETURN_DESC1 VARCHAR2(2000); /** VARIABLE TO STORE THE ERROR MESSAGE. TO BE RETURNED TO THE CALLING PROGRAM **/

/*** CURSOR TO SELECT THE RECIPEINTS AND MARK A COPY TO THE SENDER AS WELL ***/

CURSOR RECIPIENT_CUR IS
SELECT VAL
FROM MISC
WHERE KEY1 = 'EMAIL'
AND (KEY2 = 'RECIPIENT EMAIL'
OR KEY2 = 'SENDER EMAIL');


/***
** THIS PROCEDURE FETCHES THE VALUES FOR MISCELLANEOUS PARAMETERS
***/

PROCEDURE FETCH_MISC IS

BEGIN
RETURN_DESC1 := '11 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = SMTP SERVER. ';
L_SMTP_SERVER := PACKAGE.GET_PVAL('SMTP SERVER');

RETURN_DESC1 := '22 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = SMTP PORT. ';
L_SMTP_SERVER_PORT := PACKAGE.GET_PVAL('SMTP PORT');

RETURN_DESC1 := '33 - E: PARAMETER NOT MAINTAINED IN MISC FOR AM_KEY1 = TICKET_EMAIL AND KEY2 =SENDER EMAIL. ';
L_SENDER_NAME := PACKAGE.GET_PVAL('TICKET_EMAIL','SENDER EMAIL');


EXCEPTION
WHEN OTHERS THEN
RAISE ABORT_PROGRAM;

END FETCH_MISC;


/**** MAIN PROGRAM STARTS HERE ****/

BEGIN
/*** FETCHING MISCELLANEOUS PARAMETERS ***/

FETCH_MISC;

/*** ASSIGNING FILE NAMES TO ARRAY ***/

FILE_ARRAY(1) := FILENAME1;
FILE_ARRAY(2) := FILENAME2;
FILE_ARRAY(3) := FILENAME3;

RETURN_DESC1 := '10 - E: THERE WAS AN ERROR IN OPENING CONNECTION. ';
CONN:= UTL_SMTP.OPEN_CONNECTION( L_SMTP_SERVER, L_SMTP_SERVER_PORT ); /** OPEN CONNECTION ON THE SERVER **/

UTL_SMTP.HELO( CONN, L_SMTP_SERVER ); /** DO THE INITIAL HAND SHAKE **/

UTL_SMTP.MAIL( CONN, L_SENDER_NAME );

RETURN_DESC1 := '20 - E: THERE WAS AN ERROR IN CREATING RECEIPIENTS. ';

FOR L_RECIPIENT_CUR_REC IN RECIPIENT_CUR /** LOOP FOR MULTIPLE RECEIPEINTS **/
LOOP

L_MSG_TO := L_RECIPIENT_CUR_REC.AM_PAR_VAL;
UTL_SMTP.RCPT( CONN, L_MSG_TO );

END LOOP;


UTL_SMTP.OPEN_DATA ( CONN );

/*** GENERATE THE MIME HEADER ***/

RETURN_DESC1 := '30 - E: THERE WAS AN ERROR IN GENERATING MIME HEADER. ';

L_MESG:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CRLF ||
'From: ' || L_SENDER_NAME || CRLF ||
'Subject: ' || SUBJECT || CRLF ||
'To: ' || L_MSG_TO || CRLF ||
'Mime-Version: 1.0' || CRLF ||
'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || CRLF ||
'' || CRLF ||
'This is a Mime message, which your current mail reader may not' || CRLF ||
'understand. Parts of the message will appear as text. If the remainder' || CRLF ||
'appears as random characters in the message body, instead of as' || CRLF ||
'attachments, then you''ll have to extract these parts and decode them' || CRLF ||
'manually.' || CRLF ||
'' || CRLF ||
'--DMW.Boundary.605592468' || CRLF ||
'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' || CRLF ||
'Content-Disposition: inline; filename="message.txt"' || CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF ||
'' || CRLF ||
MESSAGE || CRLF || CRLF || CRLF ;

L_MESG_LEN := LENGTH(L_MESG);

IF L_MESG_LEN > MAX_SIZE THEN

MESG_LENGTH_EXCEEDED := TRUE;

END IF;

RETURN_DESC1 := '40 - E: THERE WAS AN ERROR IN WRITING MESSAGE TO CONNECTION. ';

UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

/*** START ATTACHING THE FILES ***/

FOR I IN 1..3 LOOP

EXIT WHEN MESG_LENGTH_EXCEEDED;

IF FILE_ARRAY(I) IS NOT NULL THEN

BEGIN

L_SLASH_POS := INSTR(FILE_ARRAY(I), '/', -1 );

IF L_SLASH_POS = 0 THEN

L_SLASH_POS := INSTR(FILE_ARRAY(I), '\', -1 );

END IF;

L_DIRECTORY_NAME := SUBSTR(FILE_ARRAY(I), 1, L_SLASH_POS - 1 );

L_FILE_NAME := SUBSTR(FILE_ARRAY(I), L_SLASH_POS + 1 );

RETURN_DESC1 := '50 - E: THERE WAS AN ERROR IN OPENING FILE. ';

L_FILE_HANDLE := UTL_FILE.FOPEN(L_DIRECTORY_NAME, L_FILE_NAME, 'R' );

L_MESG := CRLF || '--DMW.Boundary.605592468' || CRLF ||
'Content-Type: application/octet-stream; name="' || L_FILE_NAME || '"' || CRLF ||
'Content-Disposition: attachment; filename="' || L_FILE_NAME || '"' || CRLF ||
'Content-Transfer-Encoding: 7bit' || CRLF || CRLF ;

L_MESG_LEN := L_MESG_LEN + LENGTH(L_MESG);

UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

LOOP

RETURN_DESC1 := '60 - E: THERE WAS AN ERROR IN READING FILE. ';

UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE);

IF L_MESG_LEN + LENGTH(L_LINE) > MAX_SIZE THEN

L_MESG := '*** truncated ***' || CRLF;

UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

MESG_LENGTH_EXCEEDED := TRUE;

EXIT;

END IF;

L_MESG := L_LINE || CRLF;

UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

L_MESG_LEN := L_MESG_LEN + LENGTH(L_MESG);

END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;

WHEN UTL_FILE.INVALID_PATH THEN
RAISE ABORT_PROGRAM;

WHEN OTHERS THEN
RAISE ABORT_PROGRAM;

END;

L_MESG := CRLF;

UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

UTL_FILE.FCLOSE(L_FILE_HANDLE);

END IF;

END LOOP;

RETURN_DESC1 := '70 - E: THERE WAS AN ERROR IN CLOSING MIME BOUNDARY. ';

L_MESG := CRLF || '--DMW.Boundary.605592468--' || CRLF;

UTL_SMTP.WRITE_DATA ( CONN, L_MESG );

UTL_SMTP.CLOSE_DATA( CONN );

UTL_SMTP.QUIT( CONN );

EXCEPTION
WHEN ABORT_PROGRAM THEN
RETURN_DESC := RETURN_DESC1;

WHEN OTHERS THEN
RETURN_DESC := RETURN_DESC1;

END;
/