Browse DevX
Sign up for e-mail newsletters from DevX


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




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

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).

Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning.
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