Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: utl_smtp package with a table

    Hi,

    I run this procedure:

    CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
    destinatario1 IN varchar2,
    object IN varchar2,
    messagge IN varchar2) IS

    cursor my_cur is
    select ename
    from emp;

    mailhost varchar2(40):= 'pop.mail.xxxxx.it';

    conn utl_smtp.connection;
    crlf varchar2(2):=CHR(13)||CHR(10);
    messag varchar2(3000);
    v_mitt varchar2(2000) := mitt;
    BEGIN

    for rec_msg in my_cur
    loop
    messag := rec_msg.ename;
    end loop;

    conn := utl_smtp.open_connection (mailhost, 25);

    messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||destinatario1||' '||crlf||messagge;

    utl_smtp.helo(conn, mailhost);
    utl_smtp.mail (conn, v_mitt);
    utl_smtp.rcpt (conn, destinatario1);
    utl_smtp.data(conn, messag);
    utl_smtp.quit(conn);
    EXCEPTION
    when utl_smtp.transient_error or utl_smtp.permanent_error then
    utl_smtp.quit(conn);

    raise_application_error (-20000, 'error: '||sqlerrm);
    END send_mail;




    This procedure run correctly if I insert in MESSAGE one text, but don't send the values of col ENAME in table scott.emp;

    What I wrong in this procedure??

    Could you help me?

    Thanks
    Raf

  2. #2
    Join Date
    Jul 2002
    Posts
    227
    I tried this procedure

    CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
    destinatario1 IN varchar2,
    object IN varchar2,
    messagge IN varchar2) IS

    cursor my_cur is
    select ename
    from emp;

    mailhost varchar2(40):= 'pop.mail.xxxxx.it';

    conn utl_smtp.connection;
    crlf varchar2(2):=CHR(13)||CHR(10);
    messag varchar2(3000);
    v_mitt varchar2(2000) := mitt;
    BEGIN
    conn := utl_smtp.open_connection (mailhost, 25);


    for rec_msg in my_cur
    loop

    messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||destinatario1||' '||crlf||rec_msg.ename;

    utl_smtp.helo(conn, mailhost);
    utl_smtp.mail (conn, v_mitt);
    utl_smtp.rcpt (conn, destinatario1);
    utl_smtp.data(conn, messag);
    messag := rec_msg.ename;
    end loop;

    utl_smtp.quit(conn);

    EXCEPTION
    when utl_smtp.transient_error or utl_smtp.permanent_error then
    utl_smtp.quit(conn);
    raise_application_error (-20000, 'error: '||sqlerrm);
    END send_mail;


    but It send 14 mails for each record of the table emp.
    I'd like to send just one mail with 14 records.

    How can I do it??

    THANKS
    Raf

  3. #3
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: utl_smtp package with a table

    Originally posted by raf
    ...

    for rec_msg in my_cur
    loop
    messag := rec_msg.ename;
    end loop;

    conn := utl_smtp.open_connection (mailhost, 25);

    messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||destinatario1||' '||crlf|| messagge;

    you are using the following "message"-variables:

    (1) messag internal declaration
    (2) messagge as a parameter.

    in the cursor loop you set the value of messag
    and in the next statement your OVERWRITE the messag once again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •