Quick and dirty Oracle Email procedure


CREATE OR REPLACE PROCEDURE SEND_DB_MAIL
    (
       Sender        IN   VARCHAR2
     , Recipients    IN   VARCHAR2
     , CCs           IN   VARCHAR2  := NULL
     , BCCs          IN   VARCHAR2  := NULL
     , Subject       IN   VARCHAR2  := NULL
     , Message       IN   VARCHAR2  := NULL
    )
IS
    MAILHOST    CONSTANT    VARCHAR2(30)    := 'email.server.address';
    MAILPORT    CONSTANT    NUMBER          := 25;

    cnn         UTL_SMTP.CONNECTION;
       
    PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
    BEGIN
        UTL_SMTP.WRITE_DATA(cnn, name || ': ' || header || UTL_TCP.CRLF);
    END;
    
    PROCEDURE specify_recipients(recipients IN VARCHAR2)
    IS
        recipient   VARCHAR2(254);
        
        CURSOR c
        IS
            SELECT  REGEXP_SUBSTR(recipients,'[^,|^;]+', 1, LEVEL)
            FROM    dual
            CONNECT BY REGEXP_SUBSTR(recipients, '[^,|^;]+', 1, LEVEL) IS NOT NULL;        
    BEGIN        
        OPEN c;
        LOOP
            FETCH c INTO recipient;
            EXIT WHEN c%NOTFOUND;
            recipient := TRIM(recipient);
            IF LENGTH(recipient) > 0 THEN
                UTL_SMTP.RCPT(cnn, recipient);
            END IF;
        END LOOP;
        CLOSE c;
    END;
       
BEGIN
    cnn := UTL_SMTP.OPEN_CONNECTION (MAILHOST, MAILPORT);
    
    UTL_SMTP.HELO(cnn, MAILHOST);
    UTL_SMTP.MAIL(cnn, Sender);
    specify_recipients(Recipients);
    specify_recipients(CCs);
    specify_recipients(BCCs);      

    UTL_SMTP.OPEN_DATA(cnn);
    send_header('Date',         TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));
    send_header('From',         Sender);
    send_header('To',           Recipients);
    send_header('CC',           CCs);
    send_header('BCC',          BCCs);
    send_header('Subject',      Subject);

    UTL_SMTP.WRITE_DATA(cnn, UTL_TCP.CRLF || Message);
       
    UTL_SMTP.CLOSE_DATA(cnn);
    UTL_SMTP.QUIT(cnn);

EXCEPTION
   WHEN UTL_SMTP.INVALID_OPERATION
   THEN
       UTL_SMTP.QUIT(cnn);
       DBMS_OUTPUT.PUT_LINE (' Invalid Operation in SMTP transaction.');
   WHEN UTL_SMTP.TRANSIENT_ERROR
   THEN
       UTL_SMTP.QUIT(cnn);
      DBMS_OUTPUT.PUT_LINE (' Temporary problems with sending email - try again later.');
   WHEN UTL_SMTP.PERMANENT_ERROR
   THEN
       UTL_SMTP.QUIT(cnn);
      DBMS_OUTPUT.PUT_LINE (' Errors in code for SMTP transaction.' || SQLCODE || '/' || SQLERRM);
END SEND_DB_MAIL;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s