[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

send mail to C.C.

Posted on 2011-05-10
5
Medium Priority
?
414 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:ralph_rea
  • 4
5 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35727411
- 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;
/
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35727423
- kindly notice that the new variable is "cc_recipient" not "cc recipient" (its not seen properly because i put the underline)
0
 

Author Comment

by:ralph_rea
ID: 35727474
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

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35727490
which column and table that holds the cc email?
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35727547
- change your select statement to query for cc email data.
- then add the parameter passing for cc, i named it as destinatario2 (see code as below). you need to change the <table.columnname> to the actual table.column where you get the cc detail from. just follow the step of how you get R.EMAIL_ADDRESS that you've done earlier :


        SEND_ALERT_EDILE (V_SENDER ,
                destinatario1 => R.EMAIL_ADDRESS ,
                destinatario2 => <table.columnname> ,
                   mailhost => V_MAILHOST ,
                   oggetto => V_OBJ_ALERT,
                   MESSAGE => 'Regards');

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question