UTL_SMTP is a system package used for sending emails via the Simple Mail Transfer Protocol (SMTP).
UTL_SMTP relies on the UTL_TCP system package, so it is necessary to ensure that the UTL_TCP package is functioning properly.
Applicability
This content applies only to the Oracle-compatible mode of OceanBase Database.
Overview of UTL_SMTP subprograms
The following table describes the UTL_SMTP subprograms supported in the current version of OceanBase Database.
| Subprogram | Description |
|---|---|
| OPEN_CONNECTION | Opens and returns a connection to the SMTP service. |
| CLOSE_CONNECTION | Closes the SMTP connection, thereby terminating the SMTP operation. It is only used to cancel an email during the data session process. |
| QUIT | Sends the QUIT command to the server to terminate the SMTP session and disconnect from the server. |
| AUTH | Sends an authentication command to the SMTP server. |
| COMMAND | Executes a general SMTP command. |
| COMMAND_REPLIES | Executes a general SMTP command and receives multiple reply lines. |
| HELO | Executes the HELO command to perform an initial handshake with the SMTP service. |
| EHLO | Executes the EHLO command to perform an initial handshake with the SMTP service. |
Executes the MAIL command to start a mail transaction with the SMTP service and declare the sender's email address. |
|
| RCPT | Executes the RCPT command to specify the recipient of an email. |
| OPEN_DATA | Sends the DATA command to the server to indicate the start of the email content. You can then write the email content by using the write_data and write_raw_data subprograms. |
| WRITE_DATA | Sends text-based email content. You can call this subprogram multiple times to add data to the email. |
| WRITE_RAW_DATA | Sends raw data-based email content. You can call this subprogram multiple times to add data to the email. |
| CLOSE_DATA | Sends a <CR><LF>.<CR><LF> delimiter to indicate the completion of the email text. |
| DATA | Specifies the body of an email. |
| VRFY | Sends the VRFY command to verify the validity of the destination email address. |
| HELP | Sends the HELP command to request help information. |
| NOOP | Sends the NOOP command to verify the validity of the destination email address. |
| RSET | Sends the RSET command to cancel the current email transaction. |
Example
DECLARE
-- Define the mail server information.
l_mailhost VARCHAR2(100) := 'smtp.example.com'; -- The SMTP server address.
l_mail_conn UTL_SMTP.connection; -- The connection object.
-- Define the mail content.
l_from VARCHAR2(100) := 'sender@example.com'; -- The sender's email address.
l_to VARCHAR2(100) := 'receiver@example.com'; -- The recipient's email address.
l_subject VARCHAR2(100) := 'Test mail'; -- The subject of the mail.
l_message VARCHAR2(4000) := 'This is a test mail from OceanBase Database. '; -- The body of the mail.
BEGIN
-- Connect to the SMTP server.
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25); -- 25 is the standard port for the SMTP service.
-- Send the HELO command to the server.
UTL_SMTP.helo(l_mail_conn, l_mailhost);
-- Set the sender of the mail.
UTL_SMTP.mail(l_mail_conn, l_from);
-- Set the recipient of the mail.
UTL_SMTP.rcpt(l_mail_conn, l_to);
-- Start data transmission.
UTL_SMTP.open_data(l_mail_conn);
-- Add the mail header.
UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || l_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf); -- An empty line separates the header and the body.
-- Write the mail body.
UTL_SMTP.write_data(l_mail_conn, l_message || UTL_TCP.crlf);
-- End data transmission.
UTL_SMTP.close_data(l_mail_conn);
-- Close the connection.
UTL_SMTP.quit(l_mail_conn);
DBMS_OUTPUT.PUT_LINE('The mail has been sent successfully!');
EXCEPTION
WHEN OTHERS THEN
-- If an error occurs, attempt to close the connection (if it has been opened).
IF UTL_SMTP.is_open(l_mail_conn) THEN
UTL_SMTP.quit(l_mail_conn);
END IF;
-- Raise the exception.
RAISE;
END;
/