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

    Unanswered: how create procedure to send mail

    hi.

    I've table USER_ADDRESS:


    USER_NAME.......USER_PWD..........USER_EMAIL...... ..............DATA_EXPIRE
    009.............TEN...............mail@xxx.com.... ..............15/03/2006
    001.............ONE...............mail1@xxx.com... ..............16/03/2006
    005.............FOUR..............mail2@xxx.com... ..............17/03/2006
    007.............FIVE..............mail3@xxx.com... ..............20/03/2006
    0021............SIX...............mail4@xxx.com... ..............25/03/2006
    0041............SEVEN.............mail5@xxx.com... ..............27/03/2006

    I create this trigger that fire when user update the column USER_PWD:

    CREATE OR REPLACE TRIGGER USER_BEFORE_UPD BEFORE UPDATE
    OF USER_PWD ON USER_ADDRESS FOR EACH ROW
    Begin
    SELECT trunc(SYSDATE)+90
    INTO :NEW.DATA_EXPIRE
    FROM DUAL;
    End;

    I created this procedure to send mail:

    CREATE OR REPLACE procedure send_mail ( mitt IN varchar2,
    recipient IN varchar2,
    object IN varchar2,
    messagge IN varchar2) IS
    mailhost varchar2(40):= 'post.xww.tel.com';

    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);
    messag := 'DATE: '||to_char(sysdate, 'dd-mon-yy hh24:mi:ss')||crlf||
    'FROM: <'||mitt||'>'||crlf||
    'SUBJECT: '||object||crlf||
    'TO: '||recipient;
    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;

    Now I'd like to schedule one procedure (every night) that:

    when (DATA_EXPIRE - sysdate) < 3 and (DATA_EXPIRE - sysdate) > 0 days

    I must send mail to user_name (in my case only to mail1@xxx.com and mail2@xxx.com)
    with this message 'Attention, yours password will expire' ||DATA_EXPIRE||'you must change it, otherwise will come changed automatically.'

    when (DATA_EXPIRE - sysdate) = 0 days (password expired)

    I must change the password automatically with this parameter
    update USER_ADDRESS
    SET USER_PWD = SUBSTR(USER_NAME,1,2)||SUBSTR(SYSDATE,1,2)||'$';
    and send mail to user_name (in my case only to mail@xxx.com)
    with this message 'Attention, yours password expired, the new password is '||USER_PWD||

    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Use the DBMS_JOB package to schedule your procedure.

    PS: Use the following to check dates:

    when (TRUNC(DATA_EXPIRE) - TRUNC(sysdate)) = 0 days ...


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Quote Originally Posted by LKBrwn_DBA

    Use the DBMS_JOB package to schedule your procedure.

    PS: Use the following to check dates:

    when (TRUNC(DATA_EXPIRE) - TRUNC(sysdate)) = 0 days ...



    OK, but how can I write the stored procedure that send mail only to addresses with the difference between date I have said over.

    my procedure must call "send_mail" procedure but it has this parameter:
    ( mitt IN varchar2,
    recipient IN varchar2,
    object IN varchar2,
    messagge IN varchar2)

    how can I pass recipent parameter in table USER_ADDRESS??

    Have yu any idea??

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    You could probably do it with a cursor like so:
    Code:
    declare
      cursor c_recipients is
      select user_email, user_name
         from user_address
       where (DATA_EXPIRE - sysdate) < 3 and (DATA_EXPIRE - sysdate) > 0;
    
    begin
      for r_recipients in c_recipients loop
        send_mail('MITT_VALUE',
                      r_recipients.user_email,
                      'OBJECT VALUE',
                      'Your email will expire');
        
        update USER_ADDRESS
        SET USER_PWD = SUBSTR(r_recipients.user_name,1,2)||SUBSTR(SYSDATE,1,2)||'$'
        where user_name = r_recipients.user_name;
    
       end loop;
      commit;
    end;
    That would be one way to do it - add your own exception logic
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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