DB Mail  Examples

SQL Example
-- The following SELECT statement will send email to all users who run batch jobs and their database password is going to expire next Monday.

SELECT send_mail(fname ||  '_' || lname || '@domain.com',
                               'Password expiry',
                               'Dear ' || fname || ',' || chr(10) || chr(10) ||
                                     'You database password will expire next Monday. ' ||
                                     'Be sure to update your batch jobs before that date.' || chr(10) || chr(10) ||
                                     'If you need assistance, simply reply to this message with your questions',
                               'helpdesk@domain.com' )
FROM batch_user
WHERE expire_date = TRUNC(SYSDATE + 3)
PL/SQL Example
*  This functions debits the specified account by specified amount
*  only if there are sufficient funds to cover the withdrawal, and it there
*  are not, it sends an email alert to the account manager.
*  This  function returns new account ballance

CREATE OR REPLACE FUNCTION acct_debit (acct_nbr CHAR(10), debit_amt NUMBER(5, 2))
RETURN number(11, 2)
      acct_balance NUMBER(11,2);
      SELECT balance INTO acct_balance
      FROM accounts
      WHERE acct = acct_nbr
      FOR UPDATE OF balance;

      IF acct_balance >= debit_amt THEN
                    acct_balance := acct_balance - debit_amt
                    UPDATE accounts
                    SET balance = acct_balance
                    WHERE acct = acct_nbr;

             -- Insufficient funds.   Send email notification to the account manager
             SELECT send_mail(m.email, 'WARNING: Insufficient funds',
                                                           'Time: ' || SYSDATE || chr(10) || ' Account: ' || acct_nbr, NULL)
             FROM managers m, accounts a
             WHERE m.mgr_id = a.mgr_id
                  AND a.acct = acct_nbr;
        END IF;

        RETURN acct_balance;

Send mail to webmaster@softtreetech.com with questions or comments about this web site.
Copyright 2000 SoftTree Technologies, Inc.
Last modified: October 4, 2000