DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how can I get address from a table?

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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
    Jul 2002
    Posts
    228
    I want to read the address record from EMAIL_ADDRESS and include this in exec procedure, because if change the recipeints I mustn't chenge the procedure.

    for example:

    exec send_mail ('x', recipient, 'y', 'z');

    Raf

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi,

    messag long; --varchar2(3000);You some issiues in converting varchar2 to long... hv u soled this issue???

    If yes... thatz good.

    wht actually u want...
    I want to read the address record from EMAIL_ADDRESS and include this in exec procedure, because if change the recipeints I mustn't chenge the procedure.

    for example:

    exec send_mail ('x', recipient, 'y', 'z');


    COULD NOT UNDERSTAND
    Cheers!
    OraKid.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    ...
    utl_smtp.mail (conn, v_mitt);
    For email_address in
       (
       Select address
       From   email_address
       )
    Loop
       utl_smtp.rcpt (conn, email_address.address);
    End Loop;
    utl_smtp.data(conn, messag);
    ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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