PL/SQL UTL_SMTP.OPEN_DATA – Oracle PL/SQL Tutorial

The UTL_SMTP.OPEN_DATA function and procedure in Oracle PL/SQL are part of the UTL_SMTP package, which facilitates sending email messages using the Simple Mail Transfer Protocol (SMTP). These functions allow developers to open the body of an email message for writing. Once the email content is defined, the content is sent using the UTL_SMTP.WRITE_DATA procedure.

Syntax

The syntax for UTL_SMTP.OPEN_DATA is as follows:

Function:

FUNCTION OPEN_DATA (
    c IN OUT NOCOPY connection
) RETURN INTEGER;

Procedure:

PROCEDURE OPEN_DATA (
    c IN OUT NOCOPY connection
);

Parameters

c: A handle to the SMTP connection, typically obtained using the UTL_SMTP.OPEN_CONNECTION function.

Return Value (Function)

For the function variant, the return value is an integer that corresponds to the SMTP status code of the operation.

Functionality

The UTL_SMTP.OPEN_DATA function or procedure is used to start the data-writing process after the email headers (e.g., MAIL FROM, RCPT TO) have been specified.

After calling OPEN_DATA, the user can write the email’s body using UTL_SMTP.WRITE_DATA.

Once the email content is written, the UTL_SMTP.CLOSE_DATA procedure must be invoked to finalize the data.

Example

Below is an example of sending an email using UTL_SMTP.OPEN_DATA:

DECLARE
   smtp_conn UTL_SMTP.CONNECTION;
   mail_host VARCHAR2(50) := 'smtp.example.com';
   sender VARCHAR2(50) := '[email protected]';
   recipient VARCHAR2(50) := '[email protected]';
   subject VARCHAR2(100) := 'Test Email';
   message_body CLOB := 'This is a test email sent from PL/SQL using UTL_SMTP.';
BEGIN
   -- Open connection
   smtp_conn := UTL_SMTP.OPEN_CONNECTION(mail_host, 25);
   UTL_SMTP.HELO(smtp_conn, 'example.com');
   
   -- Specify the sender and recipient
   UTL_SMTP.MAIL(smtp_conn, sender);
   UTL_SMTP.RCPT(smtp_conn, recipient);
   
   -- Open data writing
   UTL_SMTP.OPEN_DATA(smtp_conn);
   
   -- Write headers
   UTL_SMTP.WRITE_DATA(smtp_conn, 'From: ' || sender || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA(smtp_conn, 'To: ' || recipient || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA(smtp_conn, 'Subject: ' || subject || UTL_TCP.CRLF || UTL_TCP.CRLF);
   
   -- Write the message body
   UTL_SMTP.WRITE_DATA(smtp_conn, message_body);
   
   -- Finalize email content
   UTL_SMTP.CLOSE_DATA(smtp_conn);
   
   -- End the SMTP session
   UTL_SMTP.QUIT(smtp_conn);
EXCEPTION
   WHEN OTHERS THEN
      IF smtp_conn IS NOT NULL THEN
         UTL_SMTP.QUIT(smtp_conn);
      END IF;
      RAISE;
END;
/

Key Points

SMTP Connection: Ensure the SMTP server allows connections from the Oracle database’s IP address.

Error Handling: Always use exception handling to close the SMTP connection in case of errors.

Email Format: Properly format the email headers and body using UTL_SMTP.WRITE_DATA.

Finalize Data: Always close the data stream with UTL_SMTP.CLOSE_DATA before quitting the session.