Sunday
Nov212010

Part Two - E-mail Oracle query results in a spreadsheet

Here's a simple way to e-mail Oracle query results in a spreadsheet. I wanted to make this script a bit more mature prior to posting, but sadly haven't had the time. There's two main things you'll need to look at prior to adopting this in your environment:
    1. The code sample accommodates a resultset of only four columns (ColumnA, ColumnB, ColumnC, and ColumnD).
    2. It will output to a .csv attachment. In our environment this defaults to open in Excel, so its works out very nicely.
Regardless if you're committed to e-mailing spreadsheets to your users I think this should suffice to get you on your way. Here's the sample:
Saturday
Oct162010

Part One - E-mail Query Results in Message Body

I have another post under the "Oracle" section that deals with sending e-mails with results of Jobs that have been scheduled in Oracle Enterprise Manager.  That should suffice for anyone trying to send the results of a single row of a query, but what about queries that return multiple rows of output?

I found a quick-and-dirty way of sending query results spanning multiple rows via e-mail using the built-in tools provided with Oracle. 

The following query sample shows you how to create a PL/SQL "collection" (read:  array) of the VARCHAR2 datatype.  Assuming your query results fit into the maximum size that you can allocate for a variable of this type you should be good to go.  Really I would think that the maximum size restrictions imposed upon e-mail messages in your organization would kick in before you reached the size limitations of the variable, but tentatively try this out and see if it doesn't fit your needs.

I've included a non-funtional sample query to demonstrate how you need to organize your columns.

I assume that when the query results you're sending e-mail notifications out on will sometimes return no rows and you'll want to format the body of the e-mail differently based on that (i.e. why have column headers in the body of the message if there are no rows returned?).