The UTL_SMTP package is used to send emails through the Simple Mail Transfer Protocol (SMTP).
The UTL_SMTP package depends on the UTL_TCP package and must be ensured to be effective.
Applicability
This topic applies only to OceanBase Database in Oracle mode.
Overview of UTL_SMTP subprograms
The following table lists the UTL_SMTP subprograms supported in the current version of OceanBase Database and their brief descriptions.
Subprogram |
Description |
|---|---|
| OPEN_CONNECTION | Opens a connection to an SMTP service and returns the connection. |
| CLOSE_CONNECTION | Closes an SMTP connection, thereby terminating the SMTP operation. This subprogram is used to cancel an email in a data session. |
| QUIT | Sends the QUIT command to the server to terminate the SMTP session and disconnect from the server. |
| AUTH | Sends an authentication command to an SMTP server. |
| COMMAND | Executes a general SMTP command. |
| COMMAND_REPLIES | Executes a general SMTP command and receives multiple response lines. |
| HELO | Executes the HELO command to initiate the initial handshake with an SMTP service. |
| EHLO | Executes the EHLO command to initiate the initial handshake with an SMTP service. |
Executes the MAIL command to start an email transaction with an SMTP service and declare the sender's email address. |
|
| RCPT | Executes the RCPT command to specify the email recipient. |
| OPEN_DATA | Sends the DATA command to the server to indicate the start of sending email content. Subsequently, the write_data and write_raw_data subprograms are used to write the email content. |
| WRITE_DATA | Sends text email content. This subprogram can be called multiple times to append data to the email. |
| WRITE_RAW_DATA | Sends raw data email content. This subprogram can be called multiple times to append data to the email. |
| CLOSE_DATA | Sends a <CR><LF>.<CR><LF> to indicate the completion of sending email text. |
| DATA | Specifies the email body. |
| VRFY | Sends the VRFY command to verify the validity of the target email address. |
| HELP | Sends the HELP command to obtain help information. |
| NOOP | Sends the NOOP command to verify the validity of the target email address. |
| RSET | Sends the RSET command to terminate the current email transaction. |
Example
DECLARE
-- Define email server information
l_mailhost VARCHAR2(100) := 'smtp.example.com'; -- SMTP server address
l_mail_conn UTL_SMTP.connection; -- Connection object
-- Email content
l_from VARCHAR2(100) := 'sender@example.com'; -- Sender's email address
l_to VARCHAR2(100) := 'receiver@example.com'; -- Recipient's email address
l_subject VARCHAR2(100) := 'Test Email'; -- Email subject
l_message VARCHAR2(4000) := 'This is a test email from OceanBase Database.'; -- Email body
BEGIN
-- Start connecting to the SMTP server
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25); -- 25 is the standard port for SMTP service
-- Send the HELO command to the server
UTL_SMTP.helo(l_mail_conn, l_mailhost);
-- Set the sender's email address
UTL_SMTP.mail(l_mail_conn, l_from);
-- Set the recipient's email address
UTL_SMTP.rcpt(l_mail_conn, l_to);
-- Start data transmission
UTL_SMTP.open_data(l_mail_conn);
-- Add email header information
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); -- Empty line to separate headers and body
-- Write the email 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('Email sent successfully!');
EXCEPTION
WHEN OTHERS THEN
-- If an error occurs, attempt to close the connection (if it is open)
IF UTL_SMTP.is_open(l_mail_conn) THEN
UTL_SMTP.quit(l_mail_conn);
END IF;
-- Display the exception information
RAISE;
END;
/