Link to home
Start Free TrialLog in
Avatar of ralph_rea
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!
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;
/

Open in new window

Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

- add the code and changes which is in bold and underline:

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(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 ||crlf||
       'Cc: ' || cc_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.rcpt(conn, cc_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)
Avatar of ralph_rea
ralph_rea

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?
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;
/

Open in new window

which column and table that holds the cc email?
ASKER CERTIFIED SOLUTION
Avatar of OP_Zaharin
OP_Zaharin
Flag of Malaysia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial