Browse DevX
Sign up for e-mail newsletters from DevX


Real-time Messaging in Oracle: Send E-mails Programmatically from the Database-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Oracle's UTL_SMTP Package
Oracle introduced the UTL_SMTP package in the 8i version of its database. (SMTP stands for Simple Mail Transfer Protocol, a 20-year-old protocol that uses TCP port 25 to establish communication between a client and a server.) Oracle designed the UTL_SMTP package to enable developers to send e-mails from the database.

You need 8i or a higher version with the Java Virtual Machine (JVM) installed to use UTL_SMTP. In addition, you have to have plsql.jar loaded into your database. Otherwise, you'll get the following exception when you call the UTL_SMTP APIs to send an e-mail: ORA-29540: class oracle/plsql/net/TCPConnection does not exist.

By default, the $ORACLE_HOME/javavm/install/initjvm.sql main script (which installs JVM) does not run the initplsj.sql script that loads the plsql.jar file into your database. Run the $ORACLE_HOME/RDBMS/ADMIN/initplsj.sql script manually as a SYS or INTERNAL user to resolve the problem. If you don't have this script available, you can either request it from Oracle Support or as a workaround simply load plsql.jar directly using the loadjava utility:

loadjava -user sys/password@database -resolve

I use the following APIs of the UTL_SMTP package in this Solution's code:

  • OPEN_CONNECTION() opens the connection to SMTP server.
  • HELO() performs the initial handshake with the SMTP server after connecting, and it identifies the sender for the server.
  • MAIL() initiates a mail transaction with a server but does not actually send a message.
  • RCPT() identifies the recipient of the message. In order to send a message to multiple recipients, you have to call this procedure multiple times. Each RCPT invocation schedules delivery to a single e-mail address.
  • DATA() specifies the body of the e-mail.
  • QUIT() terminates an SMTP session and disconnects from the server.

To utilize the APIs, put the following calls in your program in the order specified:

  2. Call HELO
  3. Call MAIL
  4. Call RCPT for each recipient
  5. Format the body of the e-mail and call MAIL
  6. Call QUIT

Note: SMTP requires a Carriage Return (ASCII 13) and Line Feed (ASCII 10) combination (CRLF) to terminate lines. I use a CRLF constant defined in UTL_TCP package to address this.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date