CREATE OR REPLACE PROCEDURE send_mail (
p_msg_to IN VARCHAR2,
p_msg_subject IN VARCHAR2,
p_msg_text IN VARCHAR2,
p_msg_from IN VARCHAR2 DEFAULT NULL,
p_mailhost IN VARCHAR2 DEFAULT NULL,
p_mailport IN VARCHAR2 DEFAULT '25')
IS
v_conc UTL_SMTP.connection;
v_msg_from VARCHAR2 (50);
v_mailhost VARCHAR2 (200);
v_mailport VARCHAR2 (5);
v_messages VARCHAR2 (2000);
v_action VARCHAR2 (60);
BEGIN
v_messages := NULL;
v_action := 'START';
IF p_mailhost IS NULL
THEN
v_mailhost := 'internal-xxx-router.gmail.com';
END IF;
IF p_mailport IS NULL
THEN
v_mailport := '25';
END IF;
v_conc :=
UTL_SMTP.open_connection (NVL (p_mailhost, v_mailhost),
TO_NUMBER (NVL (p_mailport, v_mailport)));
UTL_SMTP.helo (v_conc, NVL (p_mailhost, v_mailhost));
UTL_SMTP.mail (v_conc, NVL (p_msg_from, v_msg_from));
BEGIN
FOR one_recipient IN (WITH DATA AS (SELECT p_msg_to str FROM DUAL)
SELECT TRIM (REGEXP_SUBSTR (str,
'[^,]+',
1,
LEVEL))
str
FROM DATA
CONNECT BY INSTR (str,
',',
1,
LEVEL - 1) > 0)
LOOP
UTL_SMTP.rcpt (v_conc, one_recipient.str);
END LOOP;
END;
UTL_SMTP.open_data (v_conc);
UTL_SMTP.write_data (
v_conc,
'From: '
|| v_msg_from
|| ' '
|| UTL_TCP.crlf
|| 'To: '
|| p_msg_to
|| UTL_TCP.crlf
|| 'Subject: '
|| p_msg_subject
|| UTL_TCP.crlf
|| p_msg_text);
UTL_SMTP.close_data (v_conc);
UTL_SMTP.quit (v_conc);
v_messages := 'Success';
v_action := 'END';
EXCEPTION
WHEN UTL_SMTP.invalid_operation
THEN
v_messages :=
'Invalid Operation in Mail attempt using UTL_SMTP.'
|| SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
WHEN UTL_SMTP.transient_error
THEN
v_messages :=
'Temporary e-mail issue - try again '
|| SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
WHEN UTL_SMTP.permanent_error
THEN
v_messages :=
'Permanent Error Encountered. '
|| SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
WHEN OTHERS
THEN
v_messages := SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
END send_mail;
/
BEGIN
send_mail (p_msg_to => 'testsmtp@gmail.com',
p_msg_subject => 'Test Email',
p_msg_text => 'Dear Rajasekhar,..',
p_msg_from => 'refresh_alerts@xyz.com',
p_mailhost => NULL,
p_mailport => NULL);
END;
p_msg_to IN VARCHAR2,
p_msg_subject IN VARCHAR2,
p_msg_text IN VARCHAR2,
p_msg_from IN VARCHAR2 DEFAULT NULL,
p_mailhost IN VARCHAR2 DEFAULT NULL,
p_mailport IN VARCHAR2 DEFAULT '25')
IS
v_conc UTL_SMTP.connection;
v_msg_from VARCHAR2 (50);
v_mailhost VARCHAR2 (200);
v_mailport VARCHAR2 (5);
v_messages VARCHAR2 (2000);
v_action VARCHAR2 (60);
BEGIN
v_messages := NULL;
v_action := 'START';
IF p_mailhost IS NULL
THEN
v_mailhost := 'internal-xxx-router.gmail.com';
END IF;
IF p_mailport IS NULL
THEN
v_mailport := '25';
END IF;
v_conc :=
UTL_SMTP.open_connection (NVL (p_mailhost, v_mailhost),
TO_NUMBER (NVL (p_mailport, v_mailport)));
UTL_SMTP.helo (v_conc, NVL (p_mailhost, v_mailhost));
UTL_SMTP.mail (v_conc, NVL (p_msg_from, v_msg_from));
BEGIN
FOR one_recipient IN (WITH DATA AS (SELECT p_msg_to str FROM DUAL)
SELECT TRIM (REGEXP_SUBSTR (str,
'[^,]+',
1,
LEVEL))
str
FROM DATA
CONNECT BY INSTR (str,
',',
1,
LEVEL - 1) > 0)
LOOP
UTL_SMTP.rcpt (v_conc, one_recipient.str);
END LOOP;
END;
UTL_SMTP.open_data (v_conc);
UTL_SMTP.write_data (
v_conc,
'From: '
|| v_msg_from
|| ' '
|| UTL_TCP.crlf
|| 'To: '
|| p_msg_to
|| UTL_TCP.crlf
|| 'Subject: '
|| p_msg_subject
|| UTL_TCP.crlf
|| p_msg_text);
UTL_SMTP.close_data (v_conc);
UTL_SMTP.quit (v_conc);
v_messages := 'Success';
v_action := 'END';
EXCEPTION
WHEN UTL_SMTP.invalid_operation
THEN
v_messages :=
'Invalid Operation in Mail attempt using UTL_SMTP.'
|| SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
WHEN UTL_SMTP.transient_error
THEN
v_messages :=
'Temporary e-mail issue - try again '
|| SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
WHEN UTL_SMTP.permanent_error
THEN
v_messages :=
'Permanent Error Encountered. '
|| SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
WHEN OTHERS
THEN
v_messages := SUBSTR (TO_CHAR (SQLCODE) || '-' || SQLERRM, 1, 2000);
v_action := 'ERROR';
DBMS_OUTPUT.put_line (v_action || '~' || v_messages);
END send_mail;
/
BEGIN
send_mail (p_msg_to => 'testsmtp@gmail.com',
p_msg_subject => 'Test Email',
p_msg_text => 'Dear Rajasekhar,..',
p_msg_from => 'refresh_alerts@xyz.com',
p_mailhost => NULL,
p_mailport => NULL);
END;
No comments:
Post a Comment