CREATE OR REPLACE PACKAGE BODY DB_MOD.WM_EMAIL_HANDLER
AS
conn UTL_SMTP.CONNECTION;
email_code CLOB := empty_clob;
/*
* ACCEPTS AN EMAIL ARRAY (AS DEFINED IN THE SPECS) AND
* RETURNS A STRING CONTAINING THE EMAILS IN THE ARRAY,
* SEPARATED BY COMMAS (,).
*
* INPUTS:
* p_string
* The type of email address that this array represents.
* - "To: "
* - "Cc: "
* - "Bcc: "
* p_recipients
* The array representing the email addresses to use.
*
* OUTPUTS:
* A string containing all of the email addresses from
* the array, separated by commas (,).
*/
FUNCTION addressEmail( p_string IN VARCHAR2,
p_recipients IN ARRAY )
RETURN VARCHAR2
IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT
LOOP
UTL_SMTP.RCPT( conn, p_recipients( i ) );
IF ( l_recipients IS NULL ) THEN
l_recipients := p_string || p_recipients(i) ;
ELSE
l_recipients := l_recipients || ', ' || p_recipients(i);
END IF;
END LOOP;
RETURN l_recipients;
END addressEmail;
/*
* THIS IS A "DIRTY" ATTEMP TO FIX THE PROBLEM WHICH
* OCCURS IN (relatively) LARGE EMAILS.
* THE ACTUAL STRING THAT IS PRODUCED DOES NOT CONTAIN
* ANY STRANGE CHARACTERS, HOWEVER ONCE THE EMAIL
* MAKES IT TO THE MAIL SERVER, THERE MUST BE A
* PROBLEM WITH THE ENCODING AND EXTRA CHARACTERS
* ARE ADDED THUS MESSING-UP THE EMAIL CONTENT.
* IF SENDING HTML FORMATTED EMAILS, SOME OF THE EMAIL
* BODY CONTENT MAY BE OBSCURED AS A HTML TAG MAY BE
* IMPROPERLY CLOSED/OPENED.
*
* THIS ISSUE MUST BE ADDRESSED PROPERLY BEFORE THIS
* EMAILING ROUTINE CAN BE USED.
*
*
* INPUTS:
* in_emailStr
* The email string that is to be cleaned.
*
* OUTPUT:
* The cleaned email string.
*/
FUNCTION CLEAN_EMAIL_STR( in_emailStr IN VARCHAR2 )
RETURN VARCHAR2
IS
cleanSize INTEGER := 1372;
dirtyStr VARCHAR2(3 CHAR) := ' ';
dirtyStr_starter VARCHAR2(4 CHAR) := '';
dirtyStr_concat VARCHAR2(10 CHAR) := '';
cIndex INTEGER := 1;
source_strSize INTEGER;
jumpSize INTEGER;
jumpCount INTEGER := 0;
BEGIN
source_strSize := LENGTH( in_emailStr );
-- WHILE ( cIndex <= source_clobSize )
-- LOOP
-- jumpSize := LEAST( cleanSize - LENGTH( dirtyStr_starter ), source_clobSize - ( cIndex - 1 ) );
-- DBMS_OUTPUT.PUT_LINE( jumpCount || ': ' || DBMS_LOB.SUBSTR( email_code, jumpSize, cIndex ) );
-- DBMS_LOB.WRITE( email_code_final, jumpSize, DBMS_LOB.GETLENGTH( email_code_final ) + 1, DBMS_LOB.SUBSTR( email_code, jumpSize, cIndex ) );
-- DBMS_LOB.WRITE( email_code_final, LENGTH( dirtyStr_concat ), DBMS_LOB.GETLENGTH( email_code_final ) + 1, dirtyStr_concat );
-- cIndex := cIndex + jumpSize;
-- jumpCount := jumpCount + 1;
-- END LOOP;
RETURN in_emailStr;
END CLEAN_EMAIL_STR;
/*
* APPENDS A VARCHAR2 TO THE EMAIL CLOB.
*
* in_emailStr
* The string that is to be added to
* the email CLOB.
*/
PROCEDURE APPEND_TO_EMAIL_CLOB( in_emailStr IN VARCHAR2 )
IS
temp_clobSize INTEGER;
temp_strSize INTEGER;
BEGIN
temp_clobSize := DBMS_LOB.GETLENGTH( email_code );
temp_strSize := LENGTH( in_emailStr );
-- WRITE THE STRING TO THE CLOB
DBMS_LOB.WRITE( email_code, temp_strSize, temp_clobSize + 1, in_emailStr );
EXCEPTION
WHEN OTHERS THEN
BEGIN
temp_strSize := LENGTH( in_emailStr );
-- WRITE THE STRING TO THE CLOB
DBMS_LOB.WRITE( email_code, temp_strSize, 1, in_emailStr );
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END APPEND_TO_EMAIL_CLOB;
/*
* RETURNS THE STRING WHICH EXPLAINS THE THREAT
* LEVEL NUMBER.
*
* INPUTS:
* in_threatLevel
* The threat level of the email.
* 1) DEBUG
* 2) INFO
* 3) WARNING
* 4) MINOR
* 5) CRITICAL
*/
FUNCTION GET_THREAT_LEVEL_NAME( in_threatLevel IN NUMBER DEFAULT 5 )
RETURN VARCHAR2
IS
BEGIN
CASE in_threatLevel
WHEN 1 THEN
RETURN 'DEBUG';
WHEN 2 THEN
RETURN 'INFO';
WHEN 3 THEN
RETURN 'WARNING';
WHEN 4 THEN
RETURN 'MINOR';
ELSE
RETURN 'CRITICAL';
END CASE;
END GET_THREAT_LEVEL_NAME;
/*
* CREATES THE SUBJECT TEXT FOR AN ALERT EMAIL.
*
* INPUTS:
* in_subject_msg
* The content of the email subject which
* appears after the severity level.
* in_threatLevel
* The threat level of the email.
* 1) DEBUG
* 2) INFO
* 3) WARNING
* 4) MINOR
* 5) CRITICAL
* in_LOG4J_ID_OVERRIDE
* IF ANY LOGGING THAT THIS PROCEDURE DOES IS
* TO BE APPENDED TO AN EXISTING LOG IN THE
* WM_LOG4J_LOGS TABLE, THEN ITS LOG4J_ID
* MUST BE PASSED HERE.
*/
FUNCTION CREATE_HTML_EMAIL_SUBJECT( in_subject_msg IN VARCHAR2,
in_threatLevel IN NUMBER DEFAULT 5,
in_LOG4J_ID_OVERRIDE IN NUMBER DEFAULT NULL )
RETURN VARCHAR2
IS
junk NUMBER := -1;
out_subject VARCHAR2(32000 CHAR);
BEGIN
IF ( in_LOG4J_ID_OVERRIDE IS NOT NULL ) THEN
junk := in_LOG4J_ID_OVERRIDE;
END IF;
out_subject := GET_THREAT_LEVEL_NAME( in_threatLevel ) || ': ' || in_subject_msg;
RETURN TRIM( out_subject );
EXCEPTION
WHEN OTHERS THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'CREATE_HTML_EMAIL_SUBJECT', 'ERROR', 'SQL ERROR: ' || SQLERRM );
IF ( in_LOG4J_ID_OVERRIDE IS NULL ) THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'CREATE_HTML_EMAIL_SUBJECT', 'ERROR', 'An unknown error occurred while creating the email subject.', 'Y' );
ELSE
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'CREATE_HTML_EMAIL_SUBJECT', 'ERROR', 'An unknown error occurred while creating the email subject.' );
END IF;
RETURN in_subject_msg;
END CREATE_HTML_EMAIL_SUBJECT;
/*
* BUILDS THE HTML STRUCTURE FOR THE BODY OF AN
* ALERT EMAIL.
*
* in_processName
* The name of the process that is creating this alert.
* in_msg
* The message that you want to see in the email body.
* in_threatLevel
* The threat level of the email.
* 1) DEBUG
* 2) INFO
* 3) WARNING
* 4) MINOR
* 5) CRITICAL
* in_LOG4J_ID_OVERRIDE
* IF ANY LOGGING THAT THIS PROCEDURE DOES IS
* TO BE APPENDED TO AN EXISTING LOG IN THE
* WM_LOG4J_LOGS TABLE, THEN ITS LOG4J_ID
* MUST BE PASSED HERE.
*/
FUNCTION CREATE_HTML_EMAIL_BODY( in_processName IN VARCHAR2,
in_msg IN VARCHAR2,
in_threatLevel IN NUMBER DEFAULT 5,
in_LOG4J_ID_OVERRIDE IN NUMBER DEFAULT NULL )
RETURN VARCHAR2
IS
junk NUMBER := -1;
out_bodyHTML VARCHAR2(32000 CHAR);
selectedBorderColor VARCHAR2(6 CHAR) := '555555';
regularBorderColor VARCHAR2(6 CHAR) := 'FFFFFF';
bgColor1 VARCHAR2(6 CHAR) := '006699';
bgColor2 VARCHAR2(6 CHAR) := '9BBB59';
bgColor3 VARCHAR2(6 CHAR) := 'FFCC00';
bgColor4 VARCHAR2(6 CHAR) := 'FF9900';
bgColor5 VARCHAR2(6 CHAR) := 'CC0033';
fgColor1 VARCHAR2(6 CHAR) := 'FFFFFF';
fgColor2 VARCHAR2(6 CHAR) := '000000';
fgColor3 VARCHAR2(6 CHAR) := '000000';
fgColor4 VARCHAR2(6 CHAR) := '000000';
fgColor5 VARCHAR2(6 CHAR) := 'FFFFFF';
BEGIN
IF ( in_LOG4J_ID_OVERRIDE IS NOT NULL ) THEN
junk := in_LOG4J_ID_OVERRIDE;
END IF;
out_bodyHTML := '' ||
'
' ||
'
' ||
'
' ||
in_processName ||
' Alert' ||
'
' ||
'
' ||
'
' ||
'
' ||
in_msg ||
'
' ||
'
' ||
'
' ||
'
' ||
'
' ||
'
' ||
'
' || CHR(38) || 'nbsp;
' ||
'
' || CHR(38) || 'nbsp;
' ||
'
' || CHR(38) || 'nbsp;
' ||
'
' || CHR(38) || 'nbsp;
' ||
'
' || CHR(38) || 'nbsp;
' ||
'
' ||
'
' ||
'
' ||
'
' ||
'
' ||
'';
RETURN TRIM( out_bodyHTML );
EXCEPTION
WHEN OTHERS THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'CREATE_HTML_EMAIL_BODY', 'ERROR', 'SQL ERROR: ' || SQLERRM );
IF ( in_LOG4J_ID_OVERRIDE IS NULL ) THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'CREATE_HTML_EMAIL_BODY', 'ERROR', 'An unknown error occurred while creating the email HTML body.', 'Y' );
ELSE
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'CREATE_HTML_EMAIL_BODY', 'ERROR', 'An unknown error occurred while creating the email HTML body.' );
END IF;
RETURN in_msg;
END CREATE_HTML_EMAIL_BODY;
/*
* SENDS HTML FORMATTED TEXT AS THE BODY.
*
* p_to
* An array of email addresses to
* which the email is to be delivered.
* p_cc
* An array of email addresses to
* which the email is to be delivered
* as a CC (carbon-copy).
* p_bcc
* An array of email addresses to
* which the email is to be delivered
* as a BCC (blind carbon-copy).
* p_from
* The email address that the email is
* to be sent from.
* p_fromName
* The name that you want the recipients
* see as the sender.
* If this is blank, the p_from address
* will be used.
* p_subject
* This is the subject of the email.
* p_body
* This is the text that should comprise the
* content of the email body.
* p_html_flag
* This is the flag that specifies the content
* type of the p_body text that was entered.
* p_smtp_hostname
* This is the hostname for the email server.
* This can either be an I.P. address or a
* valid alias.
* If this is left NULL or blank, the value
* stored in the WM_APP_PROPERTIES table
* under the MAILHOST setting will be used.
* p_smtp_portnum
* This is the port number to use when
* transmitting the email via the above
* stated email server.
* If this is left NULL or blank, the value
* stored in the WM_APP_PROPERTIES table
* under the MAILHOST_PORT setting will be used.
* in_LOG4J_ID_OVERRIDE
* IF ANY LOGGING THAT THIS PROCEDURE DOES IS
* TO BE APPENDED TO AN EXISTING LOG IN THE
* WM_LOG4J_LOGS TABLE, THEN ITS LOG4J_ID
* MUST BE PASSED HERE.
*
* PROPER USAGE:
WM_EMAIL_HANDLER.SEND_EMAIL( p_to => WM_EMAIL_HANDLER.ARRAY( 'someone@somewhere.com', 'someone_else@somewhere.com' ),
p_cc => WM_EMAIL_HANDLER.ARRAY( 'john.doe@somewhere.com' ),
p_bcc => WM_EMAIL_HANDLER.ARRAY( 'jane.doe@somewhere.com', 'jonny.doe@somewhere.com' ),
p_from => 'oracle.DEV@somewhere.com',
p_fromName => 'Oracle Database Account ',
p_subject => 'Just a test HTML email',
p_body => 'Some HTML here.',
p_html_flag => 'Y',
p_smtp_hostname => 'devftp-1',
p_smtp_portnum => '25' );
*/
PROCEDURE SEND_EMAIL( p_to IN ARRAY DEFAULT ARRAY(),
p_cc IN ARRAY DEFAULT ARRAY(),
p_bcc IN ARRAY DEFAULT ARRAY(),
p_from IN VARCHAR2,
p_fromName IN VARCHAR2 DEFAULT NULL,
p_subject IN VARCHAR2,
p_body IN VARCHAR2 DEFAULT NULL,
p_html_flag IN VARCHAR2 DEFAULT 'N',
p_smtp_hostname IN VARCHAR2 DEFAULT NULL,
p_smtp_portnum IN VARCHAR2 DEFAULT NULL,
in_LOG4J_ID_OVERRIDE IN NUMBER DEFAULT NULL )
IS
junk NUMBER := -1;
smtp_writeBlockSize INTEGER := 1900;
email_code_temp VARCHAR2(32767) DEFAULT NULL;
email_date VARCHAR2(255) DEFAULT TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' );
email_toList LONG;
email_ccList LONG;
email_bccList LONG;
cut_amount NUMBER;
cut_offset NUMBER;
server_host VARCHAR2(255);
server_port VARCHAR2(255);
nlChar VARCHAR2(2 CHAR) := CHR(13) || CHR(10);
email_charIndex INTEGER := 0;
BEGIN
IF ( in_LOG4J_ID_OVERRIDE IS NOT NULL ) THEN
junk := in_LOG4J_ID_OVERRIDE;
END IF;
IF ( ( p_smtp_hostname IS NULL ) OR ( LENGTH( TRIM( NVL( p_smtp_hostname, '' ) ) ) = 0 ) ) THEN
SELECT TRIM( NVL( PROPERTY_VALUE, '' ) )
INTO server_host
FROM WM_APP_PROPERTIES
WHERE LOWER( PROPERTY_NAME ) = 'mailhost';
ELSE
server_host := TRIM( p_smtp_hostname );
END IF;
IF ( ( p_smtp_portnum IS NULL ) OR ( LENGTH( TRIM( NVL( p_smtp_portnum, '' ) ) ) = 0 ) ) THEN
SELECT TRIM( NVL( PROPERTY_VALUE, '' ) )
INTO server_port
FROM WM_APP_PROPERTIES
WHERE LOWER( PROPERTY_NAME ) = 'mailhost_port';
ELSE
server_port := TRIM( p_smtp_portnum );
END IF;
IF ( ( LENGTH( TRIM( NVL( server_host, '' ) ) ) > 0 )
AND
( LENGTH( TRIM( NVL( server_port, '' ) ) ) > 0 ) ) THEN
conn := UTL_SMTP.OPEN_CONNECTION( server_host, server_port );
UTL_SMTP.HELO( conn, server_host );
UTL_SMTP.MAIL( conn, p_from );
email_toList := addressEmail( 'To: ', p_to );
email_ccList := addressEmail( 'Cc: ', p_cc );
email_bccList := addressEmail( 'Bcc: ', p_bcc );
IF ( ( p_body IS NOT NULL )
AND
( email_toList IS NOT NULL )
AND
( p_from IS NOT NULL )
AND
( p_subject IS NOT NULL ) ) THEN
-- CREATE THE CLOB SPACE.
DBMS_LOB.CREATETEMPORARY( email_code, false, 10 );
-- INITIALIZE THE MIME HEADER.
email_code_temp := email_code_temp || 'MIME-Version: 1.0' || nlChar ||
'Date: ' || email_date || nlChar ||
email_toList || nlChar;
-- IF ANY CC RECIPIENTS EXIST, THEN
-- ADD THEM TO THE MIME HEADER.
IF ( email_ccList IS NOT NULL ) THEN
email_code_temp := email_code_temp || email_ccList || nlChar;
END IF;
-- IF ANY BCC RECIPIENTS EXIST, THEN
-- ADD THEM TO THE MIME HEADER.
IF ( email_bccList IS NOT NULL ) THEN
email_code_temp := email_code_temp || email_bccList || nlChar;
END IF;
email_code_temp := email_code_temp || 'From: ' || NVL( p_fromName, p_from ) || nlChar ||
'Subject: ' || p_subject || nlChar ||
'Reply-To: ' || p_from || nlChar;
APPEND_TO_EMAIL_CLOB( email_code_temp );
IF ( p_body IS NOT NULL ) THEN
IF ( UPPER( TRIM( p_html_flag ) ) = 'Y' ) THEN
email_code_temp := 'Content-Type: text/html; ' ||
'Content-Transfer-Encoding: base64;' || nlChar;
APPEND_TO_EMAIL_CLOB( email_code_temp );
----------------------------------------------------
-- Write the HTML portion of the message
APPEND_TO_EMAIL_CLOB( p_body );
-- APPEND_TO_EMAIL_CLOB( p_body );
-- APPEND_TO_EMAIL_CLOB( p_body );
-- APPEND_TO_EMAIL_CLOB( p_body );
ELSE
email_code_temp := 'Content-Type: text/plain; ' ||
'Content-Transfer-Encoding: base64;' || nlChar;
APPEND_TO_EMAIL_CLOB( email_code_temp );
----------------------------------------------------
-- Write the plain text portion of the email
APPEND_TO_EMAIL_CLOB( p_body );
APPEND_TO_EMAIL_CLOB( p_body );
APPEND_TO_EMAIL_CLOB( p_body );
APPEND_TO_EMAIL_CLOB( p_body );
END IF;
END IF;
----------------------------------------------------
-- Send the email in byte chunks to UTL_SMTP
cut_offset := 1;
UTL_SMTP.OPEN_DATA( conn );
cut_amount := smtp_writeBlockSize;
WHILE cut_offset < DBMS_LOB.GETLENGTH( email_code )
LOOP
UTL_SMTP.WRITE_DATA( conn, DBMS_LOB.SUBSTR( email_code, LEAST( cut_amount, DBMS_LOB.GETLENGTH( email_code ) ), cut_offset ) );
cut_offset := cut_offset + cut_amount;
cut_amount := LEAST( smtp_writeBlockSize, DBMS_LOB.GETLENGTH( email_code ) - cut_amount );
END LOOP;
DBMS_LOB.FREETEMPORARY( email_code );
UTL_SMTP.CLOSE_DATA( conn );
END IF;
UTL_SMTP.QUIT( conn );
ELSE
DBMS_OUTPUT.PUT_LINE( 'Error loading mailhost or port' );
END IF;
EXCEPTION
WHEN OTHERS THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'SEND_EMAIL', 'ERROR', 'SQL ERROR: ' || SQLERRM );
IF ( in_LOG4J_ID_OVERRIDE IS NULL ) THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'SEND_EMAIL', 'ERROR', 'An unknown error occurred while sending an email.', 'Y' );
ELSE
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'SEND_EMAIL', 'ERROR', 'An unknown error occurred while sending an email.' );
END IF;
RAISE;
END SEND_EMAIL;
/*
* SENDS A HTML FORMATTED EMAIL USING A COMMON ALERT
* FORMAT.
* THE MESSAGE IS PACKAGED INSIDE THE COMMON ALERT
* HTML CODE.
*
* INPUTS:
* in_processName
* The name of the process that is creating this alert.
* in_msg
* The message that you want to see in the email body.
* in_subject_msg
* The message that is to appear in the email subject
* after the alert level indicator.
* in_threatLevel
* The threat level of the email.
* 1) DEBUG
* 2) INFO
* 3) WARNING
* 4) MINOR
* 5) CRITICAL
* p_to
* An array of email addresses to
* which the email is to be delivered.
* p_cc
* An array of email addresses to
* which the email is to be delivered
* as a CC (carbon-copy).
* p_bcc
* An array of email addresses to
* which the email is to be delivered
* as a BCC (blind carbon-copy).
* p_from
* The email address that the email is
* to be sent from.
* p_fromName
* The name that you want the recipients
* see as the sender.
* If this is blank, the p_from address
* will be used.
* p_smtp_hostname
* This is the hostname for the email server.
* This can either be an I.P. address or a
* valid alias.
* If this is left NULL or blank, the value
* stored in the WM_APP_PROPERTIES table
* under the MAILHOST setting will be used.
* p_smtp_portnum
* This is the port number to use when
* transmitting the email via the above
* stated email server.
* If this is left NULL or blank, the value
* stored in the WM_APP_PROPERTIES table
* under the MAILHOST_PORT setting will be used.
* in_LOG4J_ID_OVERRIDE
* IF ANY LOGGING THAT THIS PROCEDURE DOES IS
* TO BE APPENDED TO AN EXISTING LOG IN THE
* WM_LOG4J_LOGS TABLE, THEN ITS LOG4J_ID
* MUST BE PASSED HERE.
*
* PROPER USAGE:
WM_EMAIL_HANDLER.SEND_ALERT( in_processName => 'My Process',
in_msg => 'A critical exception occurred which caused the created of member files to fail.
Please see logs for more details.',
in_subject_msg => 'Member Creation Failure',
in_threatLevel => '5',
in_to => WM_EMAIL_HANDLER.ARRAY( 'someone@somewhere.com', 'someone_else@somewhere.com' ),
in_cc => WM_EMAIL_HANDLER.ARRAY( 'john.doe@somewhere.com' ),
in_bcc => WM_EMAIL_HANDLER.ARRAY( 'jane.doe@somewhere.com', 'jonny.doe@somewhere.com' ),
in_from => 'oracle.DEV@somewhere.com',
in_fromName => 'Oracle Database Account ',
in_smtp_hostname => 'devftp-1',
in_smtp_portnum => '25' );
*/
PROCEDURE SEND_ALERT( in_processName IN VARCHAR2,
in_msg IN VARCHAR2 DEFAULT NULL,
in_subject_msg IN VARCHAR2 DEFAULT NULL,
in_threatLevel IN NUMBER DEFAULT 5,
in_to IN ARRAY DEFAULT ARRAY(),
in_cc IN ARRAY DEFAULT ARRAY(),
in_bcc IN ARRAY DEFAULT ARRAY(),
in_from IN VARCHAR2,
in_fromName IN VARCHAR2 DEFAULT NULL,
in_smtp_hostname IN VARCHAR2 DEFAULT NULL,
in_smtp_portnum IN VARCHAR2 DEFAULT NULL,
in_LOG4J_ID_OVERRIDE IN NUMBER DEFAULT NULL )
IS
PRAGMA AUTONOMOUS_TRANSACTION;
junk NUMBER := -1;
BEGIN
IF ( in_LOG4J_ID_OVERRIDE IS NOT NULL ) THEN
junk := in_LOG4J_ID_OVERRIDE;
END IF;
SEND_EMAIL( p_to => in_to,
p_cc => in_cc,
p_bcc => in_bcc,
p_from => in_from,
p_fromName => in_fromName,
p_subject => CREATE_HTML_EMAIL_SUBJECT( NVL( in_subject_msg, in_processName || ' Exception' ), in_threatLevel, in_LOG4J_ID_OVERRIDE ),
p_body => CREATE_HTML_EMAIL_BODY( in_processName, NVL( in_msg, 'An unknown exception occurred.' ), in_threatLevel, in_LOG4J_ID_OVERRIDE ),
p_html_flag => 'Y',
p_smtp_hostname => in_smtp_hostname,
p_smtp_portnum => in_smtp_portnum,
in_LOG4J_ID_OVERRIDE => in_LOG4J_ID_OVERRIDE );
EXCEPTION
WHEN OTHERS THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'SEND_ALERT', 'ERROR', 'SQL ERROR: ' || SQLERRM );
IF ( in_LOG4J_ID_OVERRIDE IS NULL ) THEN
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'SEND_ALERT', 'ERROR', 'An unknown error occurred while sending emails for the ' || in_processName || ' process.', 'Y' );
ELSE
junk := WM_LOG4J_LOGGER.LOG_MESSAGE( junk, 'WM EMAIL HANDLER', 'SEND_ALERT', 'ERROR', 'An unknown error occurred while sending emails for the ' || in_processName || ' process.' );
END IF;
END SEND_ALERT;
END;
/