Oracle | UTL

Oracle UTL_MAIL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Send emails, w/wo attachments, from inside the database. Our recommendation is that UTL_MAIL only be used for the most basic email requirements: Instead (click on the link at page bottom) use UTL_SMTP which has far richer functionality.

AUTHID CURRENT_USER

Dependencies

DBMS_OUTPUT	UTL_MAIL_INTERNAL	UTL_TCP
PLITBLM	UTL_RAW	V$VERSION
UTL_ENCODE	UTL_SMTP

Documented Yes

Exceptions

Error Code	Reason
ORA-29261	INVALID_ARGUMENT
ORA-44101	INVALID_PRIORITY

First Available 2002-2003

Initialization Parameter SMTP_OUT_SERVER <port_number>

SMTP_OUT_SERVER = 9090

Mime Types

Value

'text/plain'

'text/plain; charset=us-ascii'
'text/plain; charset=ISO-8859-1'
'text/html; charset=utf-8'
'text/plain; charset=windows-1254'

'application/octet'

Security Model Owned by SYS with no privileges granted.

Source {ORACLE_HOME}/rdbms/admin/utlmail.sql
{ORACLE_HOME}/rdbms/admin/prvtmail.plb

SQL> @?/rdbms/admin/utlmail.sql

      

      Package created.

      

      Synonym created.

      

      SQL> @?/rdbms/admin/prvtmail.plb

      

      SP2-0808: Package created with compilation warnings

      

      SP2-0810: Package Body created with compilation warnings

      

      Grant succeeded.

      

      SP2-0810: Package Body created with compilation warnings

      

      Errors for PACKAGE BODY UTL_MAIL:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      94/24 PLW-07203: parameter 'ADDR_LIST' may benefit from use of the

      NOCOPY compiler hint

      

      126/28 PLW-06010: keyword "DATA" used as a defined name

      137/23 PLW-06010: keyword "DATA" used as a defined name

      145/26 PLW-06010: keyword "DATA" used as a defined name

      305/29 PLW-07203: parameter 'SMTP_SERVERS' may benefit from use of the

      NOCOPY compiler hint

      

      306/29 PLW-07203: parameter 'SMTP_PORTS' may benefit from use of the

      NOCOPY compiler hint

      

      351/10 PLW-06002: Unreachable code

      354/9 PLW-06002: Unreachable code

      356/9 PLW-06002: Unreachable code

      429/14 PLW-06009: procedure "SEND_I" OTHERS handler does not end in

      RAISE or RAISE_APPLICATION_ERROR

Subprograms

Packages an email message into the appropriate format, locates SMTP information, and delivers the message to the SMTP server for forwarding to the recipients utl_mail.send( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

--the SMTP_OUT_SERVER parameter must be set

conn sys@pdbdev as sysdba

      

      ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;

      

      conn uwclass/uwclass@pdbdev

      

      CREATE TABLE emails_sent (

      email_addr VARCHAR2(100));

      

      CREATE OR REPLACE PROCEDURE eblast IS

       CURSOR mcur IS

       SELECT per_h_email

       FROM mlib.person

       WHERE per_ok2_email = 'Y'

       AND per_h_email IS NOT NULL

       AND per_h_email NOT IN (SELECT email_addr FROM emails_sent);

      

       vSender VARCHAR2(30) := 'mailsys@morganslibrary.org';

       vSubj   msg.subject%TYPE;

       vMesg   VARCHAR2(4000);

       vMType  VARCHAR2(30) := 'text/plain; charset=us-ascii';

      BEGIN

        SELECT subject, msgcol

        INTO vSubj, vMesg

        FROM msg;

      

        FOR mrec IN mcur

        LOOP

          utl_mail.send(vSender,  mrec.per_h_email, NULL, NULL, vSubj, vMesg, vMType, NULL);

        END LOOP;

      END eblast;

      /

SEND_ATTACH_RAW

Represents the SEND Procedure overloaded for RAW attachments utl_mail.send_attach_raw ( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, attachment IN RAW, att_inline IN BOOLEAN DEFAULT TRUE, att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

--the SMTP_OUT_SERVER parameter must be set

conn sys@pdbdev as sysdba

      

      ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;

      

      conn pm/pm

      

      UPDATE online_media

      SET product_text = 'This is a UTL_MAIL demo';

      COMMIT;

      

      CREATE OR REPLACE PROCEDURE Mail_Attach (fname VARCHAR2) IS

       vInHandle utl_file.file_type;

       rfile     RAW(32767);

       flen      NUMBER;

       bsize     NUMBER;

       ex        BOOLEAN;

      

       vSender   VARCHAR2(30) := 'mailsys@morganslibrary.org';

       vSubj     VARCHAR2(50) := 'April Library News';

       vMesg     VARCHAR2(4000);

       vMType    VARCHAR2(30) := 'text/plain; charset=us-ascii';

      

       CURSOR mcur IS

       SELECT per_h_email

       FROM ml.person

       WHERE per_ok2_email = 'Y'

       AND per_h_email IS NOT NULL;
      

      BEGIN

        vMesg := 'Please print and complete attachment';

      

        SELECT utl_raw.cast_to_raw(product_text)

        INTO rf

        FROM online_media

        WHERE rownum = 1;

      

        utl_file.fgetattr('ORALOAD', fname, ex, flen, bsize);

        vInHandle := utl_file.fopen('ORALOAD', fname, 'R');

        utl_file.get_raw(l_output, rfile, flen);

        utl_file.fclose(vInHandle);

      

        FOR mrec IN mcur

        LOOP

          utl_mail.send_attach_raw(

            sender => vSender,

            recipients => mrec.per_h_email,

            subject => vSubj,

            message => vMesg,

            attachment => rfile,

            att_inline => FALSE,

            att_filename => fname);

        END LOOP;

      END;

      /

SEND_ATTACH_VARCHAR2

Represents the SEND Procedure overloaded for VARCHAR2 attachments utl_mail.send_attach_varchar2( sender IN VARCHAR2 CHARACTER SET ANY_CS, recipients IN VARCHAR2 CHARACTER SET ANY_CS, cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, message IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii', priority IN PLS_INTEGER DEFAULT 3, attachment IN VARCHAR2 CHARACTER SET ANY_CS, att_inline IN BOOLEAN DEFAULT TRUE, att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT 'text/plain; charset=us-ascii, att_filename IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL, replyto IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL);

Same as SEND_ATTACH_RAW except that the attachment must be an ASCII text file