Oracle | UTL

Oracle UTL_MAIL Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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