Oracle | UTL

Oracle UTL_SMTP 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 from inside the database. For demos written by Bryn Llewellyn [click here].

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
ALL_SCHEMES	VARCHAR2(80)	'CRAM-MD5 PLAIN LOGIN'
NON_CLEARTEXT_PASSWORD_SCHEMES	VARCHAR2(80)	'CRAM-MD5'

Data Types -- connection TYPE connection IS RECORD( host VARCHAR2(255), -- remote host name port PLS_INTEGER, -- remote port number tx_timeout PLS_INTEGER, -- Transfer time-out (sec.) private_tcp_con utl_tcp.connection, private_state PLS_INTEGER); / -- reply & replies TYPE reply IS RECORD ( code PLS_INTEGER, -- 3-digit reply code text VARCHAR2(508) -- text message); / TYPE replies IS TABLE OF reply INDEX BY BINARY_INTEGER; /

Dependencies

DBMS_AQELM	UTL_ENCODE	UTL_RAW
DBMS_ISCHED	UTL_I18N	UTL_SMT_LIB
PLITBLM	UTL_MAIL	UTL_TCP

Documented Yes

Exceptions

Error Code	Reason
ORA-24249	UNSUPPORTED_SCHEME: Unsupported authentication scheme
ORA-24250	NO_SUPPORTED_SCHEME: No supported authentication scheme
ORA-29277	INVALID_OPERATION: Operation is invalid
ORA-29278	TRANSIENT_ERROR: Transient server error in 400 range
ORA-29279	PERMANENT_ERROR: Permanent server error in 500 range

First Available 8.1.7

Reply Codes

Codes	Description
211	System status, or system help reply
214	Help message [Information on how to use the receiver or the meaning of a particular non-standard command; this reply is useful only to the human user]
220	<domain> Service ready
221	<domain> Service closing transmission channel
250	Requested mail action okay, completed
251	User not local; will forward to <forward-path>
252	OK, pending messages for node <node> started. Cannot VRFY user (for example, info is not local), but will take message for this user and attempt delivery.
253	OK, <messages> pending messages for node <node> started
354	Start mail input; end with <CRLF>.<CRLF>
355	Octet-offset is the transaction offset
421	<domain> Service not available, closing transmission channel (This may be a reply to any command if the service knows it must shut down)
450	Requested mail action not taken: mailbox unavailable [for example, mailbox busy]
451	Requested action terminated: local error in processing
452	Requested action not taken: insufficient system storage
453	You have no mail.
454	TLS not available due to temporary reason. Encryption required for requested authentication mechanism.
458	Unable to queue messages for node <node>
459	Node <node> not allowed: reason
500	synt error, command unrecognized (This may include errors such as command line too long.)
501	synt error in parameters or arguments
502	Command not implemented
503	Bad sequence of commands
504	Command parameter not implemented
521	<Machine> does not accept mail.
530	Must issue a STARTTLS command first. Encryption required for requested authentication.
534	Authentication mechanism is too weak.
538	Encryption required for requested authentication mechanism.
550	Requested action not taken: mailbox unavailable [for mailbox not found, no access]
551	User not local; please try <forward-path>
552	Requested mail action terminated: exceeded storage allocation
553	Requested action not taken: mailbox name not allowed [for example, mailbox synt incorrect]
554	Transaction failed

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Best practice is to REVOKE EXECUTE from PUBLIC and grant on an as-need basis to specific application owners only

Source {ORACLE_HOME}/rdbms/admin/utlsmtp.sql

Subprograms

AUTH

Sends AUTH command to authenticate to the SMTP server

Overload 1 utl_smtp.auth( c IN OUT NOCOPY connection, username IN VARCHAR2, password IN VARCHAR2, schemes IN VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES) RETURN REPLY;

TBD

Overload 2 utl_smtp.auth( c IN OUT NOCOPY connection, username IN VARCHAR2, password IN VARCHAR2, schemes IN VARCHAR2 DEFAULT NON_CLEARTEXT_PASSWORD_SCHEMES);

TBD

CLOSE_CONNECTION

Closes the SMTP connection. Use to abort current SMTP operation mid-session: Otherwise use quit. utl_smtp.close_connection(c IN OUT NOCOPY connection);

See SEND EMAIL Demo below

CLOSE_DATA

Closes the data session
Overload 1 utl_smtp.close_data(c IN OUT NOCOPY connection) RETURN reply;

See Demos below

Overload 2 utl_smtp.close_data(c IN OUT NOCOPY connection);

See Demos below

COMMAND

Perform generic SMTP commands

Overload 1 utl_smtp.command(c IN connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL) RETURN reply;

TBD

Overload 2 utl_smtp.command( c IN connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL);

PROCEDURE send_email(

       pFromUser IN VARCHAR2,

       pToUser   IN VARCHAR2,

       pSubject  IN VARCHAR2 DEFAULT NULL,

       pBody     IN VARCHAR2 DEFAULT NULL) AUTHID DEFINER IS

      

       conn          UTL_SMTP.CONNECTION;

       crlf          VARCHAR2(2)    := CHR(13) || CHR(10);

       EmailServer   VARCHAR2(60)   := 'smtp.mlib.org';

       mesg          VARCHAR2(4000) := 'Hello World';

       pwd           VARCHAR2(200)  := 'sEcReT';

       SenderAddress VARCHAR2(200)  := '<sender@domain.com>';

       SenderName    VARCHAR2(50)   := 'damorgan18c';

       pToList       VARCHAR2(4000);

       vToReceivers  VARCHAR2(200);

       uname         VARCHAR2(200);

      BEGIN

        conn := utl_smtp.open_connection(EmailServer, 25);

        utl_smtp.ehlo(conn, EmailServer);

        --utl_smtp.helo( conn, EmailServer );

      

        utl_smtp.command(conn, 'AUTH LOGIN');

        utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(uname))));

        utl_smtp.command(conn, utl_raw.cast_to_varchar2(

          utl_encode.base64_encode( utl_raw.cast_to_raw(pwd))));

        utl_smtp.mail(conn, SenderAddress);

        utl_smtp.rcpt(conn, pToList);

      

        mesg:= 'Date: '|| TO_CHAR(SYSDATE, 'DD MON RR HH24:MI:SS' )|| crlf ||

               'From: "' || SenderName || '" ' || SenderAddress || crlf ||

               'Subject: ' || pSubject || crlf ||

               'To: '|| pToList || crlf||

               pBody || crlf || crlf;

      

        utl_smtp.data(conn, mesg);

        utl_smtp.quit(conn);

      EXCEPTION

        WHEN OTHERS THEN

          RAISE_APPLICATION_ERROR(-20001,SQLERRM);

      END send_email;

      /

COMMAND_REPLIES

Perform generic SMTP commands utl_smtp.command_replies( c IN connection, cmd IN VARCHAR2, arg IN VARCHAR2 DEFAULT NULL) RETURN REPLIES;

TBD

DATA

Specifies the body of an e-mail message

Overload 1 utl_smtp.data( c IN OUT NOCOPY connection body IN VARCHAR2 CHARACTER SET ANY_CS) RETURN reply;

See Demos Below

Overload 2 utl_smtp.data( c IN OUT NOCOPY connection body IN VARCHAR2 CHARACTER SET ANY_CS);

See Demos Below

EHLO

Performs initial handshaking with SMTP server after connecting

Overload 1 utl_smtp.ehlo( c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN REPLIES;

DECLARE

       conn utl_smtp.connection;

       rply utl_smtp.reply;

      BEGIN

        rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);

        rply := utl_smtp.ehlo(conn, 'UW');

        utl_smtp.quit(conn);

      END;

      /

Overload 2 utl_smtp.ehlo( c IN OUT NOCOPY connection, domain IN VARCHAR);

DECLARE

       conn utl_smtp.connection;

       rply utl_smtp.reply;

      BEGIN

        rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);

        utl_smtp.ehlo(conn, 'UW');

        utl_smtp.quit(conn);

      END;

      /

HELO

Performs initial handshaking with SMTP server after connecting

Overload 1 utl_smtp.helo( c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN REPLY;

DECLARE

       conn utl_smtp.connection;

       rply utl_smtp.reply;

      BEGIN

        rply := utl_smtp.open_connection('mail.uw.edu', 25, conn);

        rply := utl_smtp.helo(conn, 'UW');

        utl_smtp.quit(conn);

      END;

      /

Overload 2 utl_smtp.helo( c IN OUT NOCOPY connection, domain IN VARCHAR2);

See Demos Below

HELP

Sends HELP Command utl_smtp.help( c IN OUT NOCOPY connection, command IN VARCHAR2 DEFAULT NULL) RETURN replies;

DECLARE

       conn  utl_smtp.connection;

       rplys utl_smtp.replies;

      BEGIN

        conn := utl_smtp.open_connection('mail.uw.edu', 25, conn);

        rplys := utl_smtp.help(conn, 'AUTH LOGIN');

      

        FOR i IN rplys.FIRST .. rplys.LAST LOOP

          dbms_output.put_line(rplys);

        END LOOP;

      

        utl_smtp.quit(conn);

      END;

      /

MAIL

Initiates a mail transaction with the server

Overload 1 utl_smtp.mail( c IN OUT NOCOPY connection, sender IN VARCHAR2, parameters IN VARCHAR2 DEFAULT NULL) RETURN reply;

See Demos Below

Overload 2 utl_smtp.mail( c IN OUT NOCOPY connection, sender IN VARCHAR2, parameters IN VARCHAR2 DEFAULT NULL);

See Demos Below

NOOP

No operation. A call that can be used for testing that does precisely nothing.
Overload 1 utl_smtp.noop(c IN OUT NOCOPY connection) RETURN reply;

DECLARE

       eServer VARCHAR2(60) := 'smtp.mlib.org';

       conn    utl_smtp.connection;

       rply    utl_smtp.reply;

      BEGIN

        conn := utl_smtp.open_connection(eServer, 25);

        rply := utl_smtp.noop(conn);

      END;

      /

Overload 2 utl_smtp.noop(c IN OUT NOCOPY connection);

DECLARE

       eServer VARCHAR2(60) := 'smtp.mlib.org';

       conn    utl_smtp.connection;

      BEGIN

        conn := utl_smtp.open_connection(eServer, 25);

        utl_smtp.noop(conn);

      END;

      /

OPEN_CONNECTION

Opens a connection to an SMTP server

Overload 1 utl_smtp.open_connection( host IN VARCHAR2, port IN PLS_INTEGER DEFAULT 25, c OUT connection, tx_timeout IN PLS_INTEGER DEFAULT NULL, wallet_path IN VARCHAR2 DEFAULT NULL, wallet_password IN VARCHAR2 DEFAULT NULL, secure_connection_before_smtp IN BOOLEAN DEFAULT FALSE) RETURN reply;

See Demos Below

Overload 2 utl_smtp.open_connection( host IN VARCHAR2, port IN PLS_INTEGER DEFAULT 25, c OUT connection, tx_timeout IN PLS_INTEGER DEFAULT NULL, wallet_path IN VARCHAR2 DEFAULT NULL, wallet_password IN VARCHAR2 DEFAULT NULL, secure_connection_before_smtp IN BOOLEAN DEFAULT FALSE) RETURN connection;

See Demos Below

OPEN_DATA

Sends the DATA command
Overload 1 utl_smtp.open_data(c IN OUT NOCOPY connection) RETURN reply;

See Demos Below

Overload 2 utl_smtp.open_data(c IN OUT NOCOPY connection);

See Demos Below

QUIT

Terminates an SMTP session and disconnects from the server
Overload 1 utl_smtp.quit(c IN OUT NOCOPY connection) RETURN reply;

See Demos Below

Overload 2 utl_smtp.quit(c IN OUT NOCOPY connection);

See Demos Below

RCPT

Specifies the recipient of an e-mail message

Overload 1 utl_smtp.rcpt( c IN OUT NOCOPY connection, recipient IN VARCHAR2, parameters IN VARCHAR2 DEFAULT NULL) RETURN reply;

See Demos Below

Overload 2 utl_smtp.rcpt( c IN OUT NOCOPY connection recipient IN VARCHAR2, parameters IN VARCHAR2 DEFAULT NULL);

See Demos Below

RSET

Aborts the current mail transaction
Overload 1 utl_smpt.rset(c IN OUT NOCOPY connection) RETURN reply;

TBD

Overload 2 utl_smpt.rset(c IN OUT NOCOPY connection);

TBD

STARTTLS

Sends the STARTTLS command to secure the SMTP connection using SSL/TLS
Overload 1 utl_smtp.starttls(c IN OUT NOCOPY connection) RETURN REPLY;

TBD

Overload 2 utl_smtp.starttls(c IN OUT NOCOPY connection);

TBD

VRFY

Verifies the validity of a destination e-mail address utl_smtp.vrfy( c IN OUT NOCOPY connection, recipient IN VARCHAR2) RETURN reply;

TBD

WRITE_DATA

Writes a portion of the e-mail message utl_smtp.write_data( c IN OUT NOCOPY connection, data IN VARCHAR2 CHARACTER SET ANY_CS);

See Demos Below

WRITE_RAW_DATA

Writes a portion of the e-mail message with RAW data utl_smtp.write_raw_data( c IN OUT NOCOPY connection, data IN RAW);

See Demos Below

Demo

Enable Java In The Database To enable UTL_SMTP in the database java must be enabled run $ORACLE_HOME/javavm/install/initjvm.sql $ORACLE_HOME/javavm/install/init_jis.sql $ORACLE_HOME/rdbms/admin/initplsj.sql

Send Email CREATE OR REPLACE PROCEDURE send_mail ( pSender VARCHAR2, pRecipient VARCHAR2, pSubject VARCHAR2, pMessage VARCHAR2) AUTHID CURRENT_USER IS mailhost CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com'; crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10); mesg VARCHAR2(1000); mail_conn utl_smtp.connection; BEGIN mail_conn := utl_smtp.open_connection(mailhost, 25); mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf || 'From: <'|| pSender ||'>' || crlf || 'Subject: '|| pSubject || crlf || 'To: '||pRecipient || crlf || '' || crlf || pMessage; utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn, pSender); utl_smtp.rcpt(mail_conn, pRecipient); utl_smtp.data(mail_conn, mesg); utl_smtp.quit(mail_conn); EXCEPTION WHEN INVALID_OPERATION THEN utl_smtp.close_connection(mail_conn); WHEN TRANSIENT_ERROR THEN utl_smtp.close_connection(mail_conn); WHEN PERMANENT_ERROR THEN utl_smtp.close_connection(mail_conn); WHEN OTHERS THEN utl_smtp.close_connection(mail_conn); END send_mail; /

Demo from OTN DECLARE c utl_smtp.connection; PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AUTHID DEFINER AS BEGIN utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF); END; BEGIN c := utl_smtp.open_connection('smtp-server.morganslibrary.org'); utl_smtp.helo(c, 'morganslibrary.org'); utl_smtp.mail(c, 'mailsys@morganslibrary.com'); utl_smtp.rcpt(c, 'recipient@oracle.com'); utl_smtp.open_data(c); send_header('From', '"Sender" <sender@morganslibrary.org>'); send_header('To', '"Recipient" <recipient@oracle.com>'); send_header('Subject', 'Hello'); utl_smtp.write_data(c, UTL_TCP.CRLF || 'Hello, world!'); utl_smtp.close_data(c); utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN NULL; END; raise_application_error(-20000, SQLERRM); END; /