utl_smtp package with a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: utl_smtp package with a table

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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 2001
    Location
    Slovenia
    Posts
    422

    Re: utl_smtp package with a table

    I am not sure what are you trying to do, but this part
    Originally posted by raf
    for rec_msg in my_cur
    loop
    messag := rec_msg.ename;
    end loop;
    will assign to "messag" only the last ename fetched.
    I don't think you meant it this way.

    Uff, even more... code after opening connection overwrites even this ename
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    see u are assigning messag to ename first and then this
    messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||destinatario1||' '||crlf||messagge;

    You have to concat the messag (ename) with messag('DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||destinatario1||' '||crlf||messagge

    try this and modify,

    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);

    --but u hv to decide where u need to paste the fetched ename...

    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||messagge || i.ename;

    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);

    messag := rec_msg.ename;
    end loop;

    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;

    Cheers!
    Cheers!
    OraKid.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi try this made a an error in my previ. reply

    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);

    --but u hv to decide where u need to paste the fetched ename...

    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||messagge || i.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;

    Cheers!
    Cheers!
    OraKid.

  5. #5
    Join Date
    Jul 2002
    Posts
    228
    ok balajiyes,
    I tried this procedure, 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

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi if u have to achive this

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

    You have to loop each record.

    Cheers!

Posting Permissions

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


Click Here to Expand Forum to Full Width