CREATE OR REPLACE PROCEDURE CORP_EMAIL_DQ_ISSUES_generic 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) := 'FLATFILES_LOG_DIR'; /** 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 **/ MESSAGE VARCHAR2(400) := '***************************************************' || CRLF || '*** PLEASE CHECK ATTACHMENTS (if present) ***' || CRLF || '*** ***' || CRLF || '*** Any queries returning more than 100 results ***' || CRLF || '*** will be listed in an attachment instead of ***' || CRLF || '*** message body. ***' || CRLF || '***************************************************' || CRLF || CRLF; SUBJECT VARCHAR2(100) := 'Data Quality Results'; MAX_SIZE NUMBER := 10000; FILENAME1 VARCHAR2(50) := 'chain_store_email.txt'; FILENAME2 VARCHAR2(50) := 'label_slsman_check.txt'; FILENAME3 VARCHAR2(50) := 'label_slsman_check_active.txt'; FILENAME4 VARCHAR2(50) := 'prod_data_check.txt'; FILENAME5 VARCHAR2(50) := 'supplier_goals_status.txt'; FILENAME6 VARCHAR2(50) := 'goal_stats_chk.txt'; FILENAME7 VARCHAR2(50) := 'qpc_changes.txt'; FILENAME8 VARCHAR2(50) := 'salespers_customer_check.txt'; FILENAME9 VARCHAR2(50) := 'fixed_month_end_dates.txt'; FILENAME10 VARCHAR2(50) := NULL; 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_FILE_HANDLE1 UTL_FILE.FILE_TYPE; /** FILE POINTER **/ --L_FILE_HANDLE2 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 **/ V_MESG_LEN NUMBER; ABORT_PROGRAM EXCEPTION; /** USER DEFINED EXCEPTION **/ MESG_LENGTH_EXCEEDED BOOLEAN := FALSE; /** BOOLEAN VARIABLE TO TRAP IF THE MESSAGE LENGHT IS EXCEEDING **/ -- /*** 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 L_SMTP_SERVER := 'xxx.yyy.zzz'; L_SMTP_SERVER_PORT := 25; L_SENDER_NAME := 'xyz@email.com'; 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; FILE_ARRAY(4) := FILENAME4; FILE_ARRAY(5) := FILENAME5; FILE_ARRAY(6) := FILENAME6; FILE_ARRAY(7) := FILENAME7; FILE_ARRAY(8) := FILENAME8; FILE_ARRAY(9) := FILENAME9; FILE_ARRAY(10) := FILENAME10; 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 ); UTL_SMTP.RCPT( CONN, 'xxx@destination.com'); UTL_SMTP.OPEN_DATA ( CONN ); /*** GENERATE THE 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 || '--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; UTL_SMTP.WRITE_DATA ( CONN, L_MESG ); /*** START ATTACHING THE FILES ***/ FOR I IN 1..10 LOOP IF FILE_ARRAY(I) IS NOT NULL THEN BEGIN L_FILE_NAME := FILE_ARRAY(I); /* GET TOTAL MESSAGE LENGTH */ V_MESG_LEN := 0; BEGIN L_FILE_HANDLE := UTL_FILE.FOPEN(L_DIRECTORY_NAME, L_FILE_NAME, 'R' ); LOOP UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE); V_MESG_LEN := V_MESG_LEN+LENGTH(L_LINE || CRLF); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN dbms_output.put_line ('error message :'||SQLERRM); dbms_output.put_line ('error code :'||SQLCODE); --DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM); --DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE); NULL; END; UTL_FILE.FCLOSE(L_FILE_HANDLE); BEGIN /*+ BASED ON MESSAGE LENGTH EITHER PROCESS AS ATTACHMENT OR IN-LINE TEST */ L_FILE_HANDLE := UTL_FILE.FOPEN(L_DIRECTORY_NAME, L_FILE_NAME, 'R' ); IF V_MESG_LEN > MAX_SIZE THEN BEGIN 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 ; BEGIN UTL_SMTP.WRITE_DATA ( CONN, L_MESG ); LOOP UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE); L_MESG := L_LINE || CRLF; UTL_SMTP.WRITE_DATA ( CONN, L_MESG ); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN dbms_output.put_line ('error message :'||SQLERRM); dbms_output.put_line ('error code :'||SQLCODE); RAISE; END; L_MESG := CRLF; UTL_SMTP.WRITE_DATA ( CONN, L_MESG ); UTL_FILE.FCLOSE(L_FILE_HANDLE); END; ELSE BEGIN BEGIN LOOP UTL_FILE.GET_LINE(L_FILE_HANDLE, L_LINE); L_MESG := L_LINE || CRLF; UTL_SMTP.WRITE_DATA ( CONN, L_MESG ); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN dbms_output.put_line ('error message :'||SQLERRM); dbms_output.put_line ('error code :'||SQLCODE); RAISE; END; END; L_MESG := CRLF; UTL_SMTP.WRITE_DATA ( CONN, L_MESG ); UTL_FILE.FCLOSE(L_FILE_HANDLE); -- END; --extra end statement END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN dbms_output.put_line ('error message :'||SQLERRM); dbms_output.put_line ('error code :'||SQLCODE); NULL; END; END; -- END; --extra end statement END IF; END LOOP; 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 NULL; WHEN OTHERS THEN NULL; END; /