ralph_rea
asked on
send mail to C.C.
Hi,
I created a stored procedure (see SEND_ALERT_TEST) to send mail and It run correcty but I'd like to send mail also to CC.
How can I modify my stored procedure to send to C.C.?
Thanks in advance!
I created a stored procedure (see SEND_ALERT_TEST) to send mail and It run correcty but I'd like to send mail also to CC.
How can I modify my stored procedure to send to C.C.?
Thanks in advance!
CREATE OR REPLACE PROCEDURE SEND_ALERT_TEST (sender IN VARCHAR2,
recipient IN VARCHAR2,
mailhost IN VARCHAR2,
object_in IN VARCHAR2,
MESSAGE IN VARCHAR2)
IS
v_mailhost VARCHAR2(64):= mailhost;
conn utl_smtp.connection;
crlf VARCHAR2(2):=CHR(13)||CHR(10);
messag VARCHAR2(32767);
v_mittente VARCHAR2(2000) := sender;
CURSOR riga IS
SELECT WR_ID, WO_ID, DESCRIPTION, REQUESTOR, PHONE, date_assigned, PROB_TYPE
FROM (
SELECT A.WR_ID, B.WO_ID, A.DESCRIPTION, A.REQUESTOR, A.PHONE, A.date_assigned, A.PROB_TYPE,
MAX(A.WR_ID) OVER (PARTITION BY A.PROB_TYPE) AS MAX_WRID
FROM TAB_WR A, TAB_WO B
WHERE A.WO_ID = B.WO_ID
AND A.PROB_TYPE = 'ELETTRIC')
WHERE WR_ID = MAX_WRID;
BEGIN
conn := utl_smtp.open_connection (v_mailhost, 25);
messag := 'DATE: '||TO_CHAR(SYSTIMESTAMP, 'Dy, dd Mon yy hh24:mi:ss TZHTZM','nls_date_language=english')||crlf||
'FROM: <'>'||crlf||
'SUBJECT: '||object_in||crlf||
'TO: '||recipient;
FOR rec_msg IN riga
LOOP
messag := messag||crlf||'Rdl:'||RPAD(rec_msg.WR_ID,10)||crlf||crlf||'Odl:'||RPAD(rec_msg.WO_ID,10)||crlf||crlf||'Description:'||RPAD(rec_msg.DESCRIPTION,200)||crlf||crlf||'REQUESTOR:'||RPAD(rec_msg.REQUESTOR,18)||crlf||crlf||'PHONE:'||RPAD(rec_msg.PHONE,10)||crlf||crlf||'DATE_REQUESTED:'||RPAD(rec_msg.date_assigned,4)||'PROB_TYPE:'||RPAD(rec_msg.PROB_TYPE,15);
END LOOP;
utl_smtp.helo(conn, mailhost);
utl_smtp.mail (conn, v_mittente);
utl_smtp.rcpt (conn, recipient);
utl_smtp.data(conn, messag||crlf||crlf||NVL(MESSAGE,'-'));
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(conn);
RAISE_APPLICATION_ERROR (-20000, 'failed: '||SQLERRM);
END SEND_ALERT_TEST;
/
- kindly notice that the new variable is "cc_recipient" not "cc recipient" (its not seen properly because i put the underline)
ASKER
OP_Zaharin,
Thanks for your answer, but now I have to modify the stored procedure (see AFM_ALERT) with parameters to add also addresses of the CC recipient.
Have you any idea?
Thanks for your answer, but now I have to modify the stored procedure (see AFM_ALERT) with parameters to add also addresses of the CC recipient.
Have you any idea?
CREATE OR REPLACE PROCEDURE AFM_ALERT IS
V_OBJ_ALERT_EDILE PROCEDURE_PARAMS.MAILOBJECT%TYPE;
V_SENDER EMAIL_ADDRESS_LIST.EMAIL_ADDRESS%TYPE;
V_MAILHOST EMAIL_MAILHOST.MAILHOST%TYPE;
CURSOR C_RECIPIENTS_EDILE IS
SELECT EMAIL_ADDRESS
FROM EMAIL_ADDRESS_LIST
WHERE ACTIVE='Y'
AND ADDRESS_TYPE='R';
BEGIN
SELECT EMAIL_ADDRESS
INTO V_SENDER
FROM EMAIL_ADDRESS_LIST
WHERE ACTIVE='Y'
AND ADDRESS_TYPE='S'
AND ROWNUM=1;
SELECT MAILHOST
INTO V_MAILHOST
FROM EMAIL_MAILHOST
WHERE ACTIVE='Y'
AND ROWNUM=1;
SELECT MAILOBJECT
INTO V_OBJ_ALERT
FROM PROCEDURE_PARAMS;
FOR R IN C_RECIPIENTS_EDILE LOOP
SEND_ALERT_EDILE (V_SENDER ,
destinatario1 => R.EMAIL_ADDRESS ,
mailhost => V_MAILHOST ,
oggetto => V_OBJ_ALERT,
MESSAGE => 'Regards');
END LOOP;
END AFM_ALERT;
/
which column and table that holds the cc email?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE OR REPLACE PROCEDURE SEND_ALERT_TEST (sender IN VARCHAR2,
recipient IN VARCHAR2,
cc_recipient IN VARCHAR2,
mailhost IN VARCHAR2,
object_in IN VARCHAR2,
MESSAGE IN VARCHAR2)
IS
v_mailhost VARCHAR2(64):= mailhost;
conn utl_smtp.connection;
crlf VARCHAR2(2):=CHR(13)||CHR(
messag VARCHAR2(32767);
v_mittente VARCHAR2(2000) := sender;
CURSOR riga IS
SELECT WR_ID, WO_ID, DESCRIPTION, REQUESTOR, PHONE, date_assigned, PROB_TYPE
FROM (
SELECT A.WR_ID, B.WO_ID, A.DESCRIPTION, A.REQUESTOR, A.PHONE, A.date_assigned, A.PROB_TYPE,
MAX(A.WR_ID) OVER (PARTITION BY A.PROB_TYPE) AS MAX_WRID
FROM TAB_WR A, TAB_WO B
WHERE A.WO_ID = B.WO_ID
AND A.PROB_TYPE = 'ELETTRIC')
WHERE WR_ID = MAX_WRID;
BEGIN
conn := utl_smtp.open_connection (v_mailhost, 25);
messag := 'DATE: '||TO_CHAR(SYSTIMESTAMP, 'Dy, dd Mon yy hh24:mi:ss TZHTZM','nls_date_language
'FROM: <'>'||crlf||
'SUBJECT: '||object_in||crlf||
'To: ' || recipient ||crlf||
'Cc: ' || cc_recipient;
FOR rec_msg IN riga
LOOP
messag := messag||crlf||'Rdl:'||RPAD
END LOOP;
utl_smtp.helo(conn, mailhost);
utl_smtp.mail (conn, v_mittente);
utl_smtp.rcpt (conn, recipient);
utl_smtp.rcpt(conn, cc_recipient);
utl_smtp.data(conn, messag||crlf||crlf||NVL(ME
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
utl_smtp.quit(conn);
RAISE_APPLICATION_ERROR (-20000, 'failed: '||SQLERRM);
END SEND_ALERT_TEST;
/