Term: UTL_SMTP

Definition:
In Oracle PL/SQL, UTL_SMTP is a built in utility package, which is used for sending emails over SMTP. SMTP stands for Simple Mail Transfer Protocol.
The package communicates through the SMTP protocol as per RFC821. The main disadvantages of UTL_SMTP are its inability to format the message content and the fact that it doesn't allow email clients to send emails over the SMTP server.

UTL_SMTP uses two record types: CONNECTION and REPLIES, whose signature is show below:

TYPE connection IS RECORD (

 host VARCHAR2(255), -- remote host name

 port PLS_INTEGER, -- remote port number

 tx_timeout PLS_INTEGER, -- Transfer time out (in seconds) 

 private_tcp_con utl_tcp.connection, -- private, for implementation use

 private_state PLS_INTEGER -- private, for implementation use

);





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; -- multiple reply lines

The list of UTL_SMTP functions is compiled below as per the Oracle documentation:

1) CLOSE_DATA Function and Procedure - Closes the data session

Example Syntax:

UTL_SMTP.CLOSE_DATA ( c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.CLOSE_DATA ( c IN OUT NOCOPY connection);

2) COMMAND Function and Procedure - Performs a generic SMTP command

Example Syntax:

UTL_SMTP.COMMAND ( c IN OUT NOCOPY connection, 

cmd IN VARCHAR2, 

arg IN VARCHAR2 DEFAULT NULL) RETURN reply;

UTL_SMTP.COMMAND ( c IN OUT NOCOPY connection, 

cmd IN VARCHAR2, 

arg IN VARCHAR2 DEFAULT NULL);

3) COMMAND_REPLIES Function - Performs initial handshaking with SMTP server after connecting

Example Syntax:

UTL_SMTP.COMMAND_REPLIES ( c IN OUT NOCOPY connection, 

cmd IN VARCHAR2, 

arg IN VARCHAR2 DEFAULT NULL) RETURN replies;

4) DATA Function and Procedure - Performs initial handshaking with SMTP server after connecting, with extended information returned

Example Syntax:

UTL_SMTP.DATA ( c IN OUT NOCOPY connection, body IN VARCHAR2 

CHARACTER SET ANY_CS) RETURN reply;

UTL_SMTP.DATA ( c IN OUT NOCOPY connection, 

body IN VARCHAR2 CHARACTER SET ANY_CS);

5) EHLO Function and Procedure - Performs initial handshaking with SMTP server after connecting, with extended information returned

Example Syntax:

UTL_SMTP.EHLO ( c IN OUT NOCOPY connection, domain IN) RETURN replies;

UTL_SMTP.EHLO ( c IN OUT NOCOPY connection, domain IN);

6) HELO Function and Procedure - Performs initial handshaking with SMTP server after connecting

Example Syntax:

UTL_SMTP.HELO ( c IN OUT NOCOPY connection, domain IN VARCHAR2) RETURN reply;

UTL_SMTP.HELO ( c IN OUT NOCOPY connection, domain IN VARCHAR2);

7) HELP Function - Sends HELP command

Example Syntax:

UTL_SMTP.HELP ( c IN OUT NOCOPY connection, 

command IN VARCHAR2 DEFAULT NULL) RETURN replies;

8) MAIL Function and Procedure - Initiates a mail transaction with the server, the destination is a mailbox

Example Syntax:

UTL_SMTP.MAIL ( c IN OUT NOCOPY connection, 

sender IN VARCHAR2, 

parameters IN VARCHAR2 DEFAULT NULL) RETURN reply;

UTL_SMTP.MAIL ( c IN OUT NOCOPY connection, 

sender IN VARCHAR2, 

parameters IN VARCHAR2 DEFAULT NULL);

9) NOOP Function and Procedure - The null command

Example Syntax:

UTL_SMTP.NOOP ( c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.NOOP ( c IN OUT NOCOPY connection);

10) OPEN_CONNECTION Functions - Opens a connection to an SMTP server

Example Syntax:

UTL_SMTP.OPEN_CONNECTION ( host IN VARCHAR2, 

port IN PLS_INTEGER DEFAULT 25, c OUT connection, 

tx_timeout IN PLS_INTEGER DEFAULT NULL) RETURN reply; 



UTL_SMTP.OPEN_CONNECTION ( host IN VARCHAR2, port 

IN PLS_INTEGER DEFAULT 25, 

 tx_timeout IN PLS_INTEGER DEFAULT NULL) RETURN connection;

11) OPEN_DATA Function and Procedure - Sends the DATA command

Example Syntax:

UTL_SMTP.OPEN_DATA ( c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.OPEN_DATA ( c IN OUT NOCOPY connection);

12) QUIT Function and Procedure - Terminates an SMTP session and disconnects from the server

Example Syntax:

UTL_SMTP.QUIT ( c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.QUIT ( c IN OUT NOCOPY connection);

13) RCPT Function - Specifies the recipient of an e-mail message

Example Syntax:

UTL_SMTP.RCPT ( c IN OUT NOCOPY connection, 

recipient IN VARCHAR2, 

parameters IN VARCHAR2 DEFAULT NULL) RETURN reply;

UTL_SMTP.RCPT ( c IN OUT NOCOPY connection, recipient 

IN VARCHAR2, 

parameters IN VARCHAR2 DEFAULT NULL);

14) RSET Function and Procedure - Terminates the current mail transaction

Example Syntax:

UTL_SMTP.RSET ( c IN OUT NOCOPY connection) RETURN reply;

UTL_SMTP.RSET ( c IN OUT NOCOPY connection);

15) VRFY Function - Verifies the validity of a destination e-mail address

Example Syntax:

UTL_SMTP.VRFY ( c IN OUT NOCOPY connection 

recipient IN VARCHAR2) RETURN reply;

16) WRITE_DATA Procedure - Writes a portion of the e-mail message

Example Syntax:

UTL_SMTP.WRITE_DATA ( c IN OUT NOCOPY connection, data 

IN VARCHAR2 CHARACTER SET ANY_CS);

17) WRITE_RAW_DATA Procedure - Writes a portion of the e-mail message with RAW data

Example Syntax:

UTL_SMTP.WRITE_RAW_DATA ( c IN OUT NOCOPY connection, data IN RAW);