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

    Unanswered: how can I get address from a table?

    hi,
    I've this procedure:

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

    cursor my_cur is
    select ename
    from emp;

    mailhost varchar2(40):= 'pop3.cxx.yyyyyyyyy';

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

    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: '||recipient;

    for rec_msg in my_cur
    loop
    messag := messag||chr(10)||rec_msg.ename;
    end loop;
    utl_smtp.helo(conn, mailhost);
    utl_smtp.mail (conn, v_mitt);
    utl_smtp.rcpt (conn, recipient);
    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;


    I have a table EMAIL_ADDRESS:

    ADDRESS VARCHAR2(64)


    I'd like to send to recipient get address from table EMAIL_ADDRESS.

    How can I send this addresses??

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how can I get address from a table?

    Originally posted by raf
    hi,
    I've this procedure:

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

    cursor my_cur is
    select ename
    from emp;

    mailhost varchar2(40):= 'pop3.cxx.yyyyyyyyy';

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

    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: '||recipient;

    for rec_msg in my_cur
    loop
    messag := messag||chr(10)||rec_msg.ename;
    end loop;
    utl_smtp.helo(conn, mailhost);
    utl_smtp.mail (conn, v_mitt);
    utl_smtp.rcpt (conn, recipient);
    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;


    I have a table EMAIL_ADDRESS:

    ADDRESS VARCHAR2(64)


    I'd like to send to recipient get address from table EMAIL_ADDRESS.

    How can I send this addresses??

    Thanks
    Raf
    I'm probably misunderstanding, but if you want to send the same email to each address in the table then why not:

    Code:
    BEGIN
      FOR r IN (SELECT * FROM email_address) LOOP
        send_mail( 'x', r.address, 'y', 'z' );
      END LOOP;
    END;

Posting Permissions

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