Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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