ecause Oracle does not provide a tool for outputting messages in real-time, Oracle database developers have always faced the challenge of monitoring their stored procedure execution in real-time. They’ve had to use the DBMS_OUTPUT.PUT_LINE call, which doesn’t return the results of a procedure until the procedure has completed.

In this 10-Minute Solution, I will demonstrate how you can send e-mails directly from an Oracle8i database as a real-time messaging solution. Besides no longer needing to wait for stored procedures to complete in order to monitor them, such a feature offers the developer a few other benefits:

• Debugging long batch processes in minutes rather than hours
• Calculating the execution times for specified code blocks
• Exporting selected data from the database and putting it in operating system (OS) text files, such as Excel spreadsheets.
• Reviewing stored procedure information remotely.

How do I output messages from my running stored procedures so I can review them in real-time?even when I’m not in the office?



Group all necessary procedures and functions in your custom package and send e-mail directly from an Oracle database using the Oracle8i UTL_SMTP supplied package.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 
plsql/jlib/plsql.jar

UTL_SMTP APIs
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:

1. Call OPEN_CONNECTION
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.

The EmailUtils Package Specification
My EmailUtils package includes the following APIs:

• SetSender/GetSender – Sets/gets the sender
• SetRecipient/GetRecipient – sets/gets the recipient
• SetCcrecipient/GetCcrecipient – sets/gets the cc: recipient
• SetMailHost/GetMailHost – sets/gets the mail host
• SetSubject/GetSubject – sets/gets the subject
• Send – Sends mail

Listing 1 shows the EmailUtils package specification:

create or replace package EmailUtils as      procedure SetSender(pSender in varchar2);   function GetSender      return varchar2;   procedure SetRecipient(pRecipient in varchar2);   function GetRecipient      return varchar2;   procedure SetCcRecipient(pCcRecipient in varchar2);   function GetCcRecipient      return varchar2;   procedure SetMailHost(pMailHost in varchar2);   function GetMailHost      return varchar2;   procedure SetSubject(pSubject in varchar2);   function GetSubject      return varchar2;   procedure Send(pMessage in varchar2);   procedure Send(pSender      in varchar2,                  pRecipient   in varchar2,                  pMailHost    in varchar2,                   pCcRecipient in varchar2 := null,                   pSubject     in varchar2 := null,                   pMessage     in varchar2 := null);   end EmailUtils;/

As you see, the Send procedure is overloaded: the package specification includes two versions of this procedure. One version will be invoked when at least three mandatory parameters are specified, pSender, pRecipient and pMailHost:

   procedure Send(pSender      in varchar2,                  pRecipient   in varchar2,                  pMailHost    in varchar2,                   pCcRecipient in varchar2 := null,                   pSubject     in varchar2 := null,                   pMessage     in varchar2 := null);

The other version will execute only when the pMessage parameter value is provided:

   procedure Send(pMessage in varchar2);

This second version is the one that I use for debugging. All the e-mail messages share the same sender, recipient, mail host, cc recipient, and subject information, which I set at the beginning of the session with “setters” procedures. Here is an example of the PL/SQL block that does that:

begin   EmailUtils.SetSender(‘[email protected]’);   EmailUtils.SetRecipient(‘[email protected]’);   EmailUtils.SetCcRecipient(‘[email protected]’);   EmailUtils.SetMailHost(‘MyServer.MyCompany.com’);   EmailUtils.SetSubject(‘DeleteClassifications procedure: Run 1’);end;/

An actual e-mail message would be specified in each Send procedure invocation. You would insert all the EmailUtils.Send() calls inside the code you are debugging, the same way I utilized the DBMS_OUTPUT.PUT_LINE() calls previously, i.e.:

vMessage := ‘Point 1.’                           || utl_tcp.crlf ||            ‘Rows processed: ‘ || to_char(vRows) || utl_tcp.crlf ||            ‘Elapsed time: ‘   || vTime;EmailUtils.Send(vMessage);vMessage := ‘Point 3.’                           || utl_tcp.crlf ||            ‘Rows processed: ‘ || to_char(vRows) || utl_tcp.crlf ||            ‘Elapsed time: ‘   || vTime;EmailUtils.Send(vMessage);

Listing 2 shows the EmailUtils specification body with overloaded Send procedures. As you can see, the code in the Send procedure is rather straightforward. The UTL_SMTP package does not provide an API to format the content of the message according to the RFC 822 standard (like, for example, setting the subject). The user is responsible for formatting the message. That’s why the following block is included inside each Send procedure to format the e-mail’s header:

vMessage := ‘Date: ‘    ||             to_char(sysdate, ‘fmDy, DD Mon YYYY fxHH24:MI:SS’)  ||                                                   utl_tcp.crlf ||             ‘From: ‘    || pSender              || utl_tcp.crlf ||             ‘Subject: ‘ || pSubject             || utl_tcp.crlf ||            ‘To: ‘      || pRecipient           || utl_tcp.crlf;

Also, you may get an error (ORA-06502: PL/SQL: numeric or value error) if the message length exceeds the 2,000-character limit. You can avoid this error with the following block, which won’t allow messages to go over 2,000 characters:

if length(vMessage) > 2000then   vMessage := substr(vMessage, 1, 2000); end if;

If you need to send long e-mails with more than 2,000 characters, use the other three UTL_SMTP APIs, which provide a more fine-grain control than the DATA() procedure does. First, OPEN_DATA() sends the DATA command. Then WRITE_DATA() adds data to the string you’re sending. You can call WRITE_DATA() as many time as needed, so you could write 2,000 characters at a time to overcome the character limit. Finally, CLOSE_DATA() ends the e-mail message by sending a period enclosed in the CRLF combination.

Real-time Messaging Makes Your Life Easier
Sending e-mails from the database is that easy. Once you try this straightforward operation, I’m sure you’ll find it useful and convenient for many of your database operations, such as debugging, remote database monitoring, and exporting database data.

Every database developer has gone through the debugging process of putting multiple DBMS_OUTPUT calls into code. After starting a SQL*Plus session, he or she enters SET SERVEROUTPUT ON and runs the procedure. The messages that were put in the DBMS_OUTPUT.PUT_LINE calls show up onscreen–but only after the procedure has completed. This process is extremely inconvenient when debugging long batch processes, which usually are run overnight. You could wait 10 to 12 hours just to find out that your procedure went through the wrong branch of code! You’d then have to fix the bug and start another 10- to 12-hour run. However, if you have real-time access to this information, you may catch the problem within the first 5 to 10 minutes.

The DBMS_OUTPUT package has other shortcomings too. For example, it does not accept variables of Boolean type and it has a 255-character-per-line limit (try to output a long message and you’ll get this exception: ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line). Listing all its shortcomings is beyond the scope of this article, but the important conclusion is that the DBMS_OUTPUT package does not allow database developers to see the messages in real-time.

Reviewing these messages when you are out of the office would be another really useful function. That would be a problem if your messages are sitting in a SQL*Plus window in your office or in OS files on the server.

Speaking of OS files on the server, wouldn’t you like to export selected data from the database and place it in an Excel spreadsheet? One way to get data into OS text files is to use Oracle’s UTL_FILE package, which provides a restricted version of standard OS stream file input/output. However, PL/SQL programs can access only the directories specified in the UTL_FILE_DIR parameter of the instance initialization file INIT.ORA. In my experience, this parameter is almost always empty. To make the directory available for file access, you have to ask a DBA to modify the initialization file and bounce the database. That’s a lot of trouble. With the EmailUtils package, you can simply put the data in an e-mail body, send it to yourself, and upon receiving it copy and paste it in your desired document format (Excel or Word).