PL/SQL UTL_SMTP.WRITE_DATA

The UTL_SMTP.WRITE_DATA procedure is part of the UTL_SMTP package in Oracle PL/SQL, which allows PL/SQL programs to send email messages using the Simple Mail Transfer Protocol (SMTP). The WRITE_DATA procedure is specifically used to write the body of an email message to the SMTP connection.

This procedure sends a chunk of data (text or binary) as part of an email message. It is commonly used to include the email content, such as the body, or to encode attachments in the message.

Syntax

UTL_SMTP.WRITE_DATA (
   c     IN OUT NOCOPY UTL_SMTP.CONNECTION,
   data  IN      VARCHAR2
);

c: The SMTP connection handle, established using UTL_SMTP.OPEN_CONNECTION.

data: The data to be sent as part of the email. This is typically text in the email body or encoded binary data for attachments.

Key Points

Chunk Writing:

If the message body is large, you can call WRITE_DATA multiple times to send it in chunks. This avoids the limitations of VARCHAR2 size in PL/SQL.

Encoding:

If the email includes non-textual data (e.g., attachments), the data must be encoded (e.g., Base64) before using WRITE_DATA.

Line Termination:

SMTP requires lines to end with a CRLF sequence (#13#10). Ensure your message adheres to this format to avoid errors.

Example

Below is an example of sending an email using UTL_SMTP.WRITE_DATA to compose the body:

DECLARE
   smtp_conn   UTL_SMTP.CONNECTION;
   host        VARCHAR2(100) := 'smtp.example.com';
   port        NUMBER := 25;
   sender      VARCHAR2(100) := '[email protected]';
   recipient   VARCHAR2(100) := '[email protected]';
   subject     VARCHAR2(100) := 'Test Email';
BEGIN
   -- Establish SMTP connection
   smtp_conn := UTL_SMTP.OPEN_CONNECTION(host, port);
   UTL_SMTP.HELO(smtp_conn, 'example.com');
   UTL_SMTP.MAIL(smtp_conn, sender);
   UTL_SMTP.RCPT(smtp_conn, recipient);
   UTL_SMTP.OPEN_DATA(smtp_conn);

   -- Write email headers
   UTL_SMTP.WRITE_DATA(smtp_conn, 'From: ' || sender || CHR(13) || CHR(10));
   UTL_SMTP.WRITE_DATA(smtp_conn, 'To: ' || recipient || CHR(13) || CHR(10));
   UTL_SMTP.WRITE_DATA(smtp_conn, 'Subject: ' || subject || CHR(13) || CHR(10));
   UTL_SMTP.WRITE_DATA(smtp_conn, CHR(13) || CHR(10)); -- Blank line to separate headers from body

   -- Write email body
   UTL_SMTP.WRITE_DATA(smtp_conn, 'Hello,' || CHR(13) || CHR(10));
   UTL_SMTP.WRITE_DATA(smtp_conn, 'This is a test email sent using Oracle PL/SQL.' || CHR(13) || CHR(10));

   -- End email body
   UTL_SMTP.CLOSE_DATA(smtp_conn);

   -- End SMTP connection
   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;
/

Best Practices

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

Line Length: SMTP servers typically limit line lengths to 1,000 characters. Ensure proper formatting of the data.

Security:

Avoid hardcoding sensitive details like passwords.
Use encrypted connections if your Oracle Database version supports UTL_SMTP.STARTTLS.
Testing: Use small test emails to validate your SMTP configurations before implementing complex features.

By using UTL_SMTP.WRITE_DATA, you can compose detailed and structured emails, including complex scenarios like multipart messages and file attachments.