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

declare myRecipients varchar2(100) := 'Recipient@RyanBoyer.net'; mySubject varchar2(100) := 'This will appear in the subject line'; -- myColumns is used to populate column headers when there is data returned by the query -- chr(9) is the tab character. myColumns varchar2(200):= 'Column1' ||chr(9)|| 'Column2' ||chr(9)|| 'ThirdColumn' ||chr(9); -- The following cursor is the SELECT and FROM portions of your query which establishes what each row of the collection is comprised of. -- You'll specify the full query later. I include "WHERE ROWNUM =1;" just to limit the definition. cursor c1 is select my.Column1, my.Column2, my.Column3 as thirdColumn from myTable -- Again - we're not running this query ever, so no point in returning more than 1 row. where rownum = 1; -- Each "row" of collection will share datatypes of the collection. type myResults is table of c1%rowtype; allmyResults myResults; -- 32767 is maximum size for varchar2 -- "xmessage" will be the Body of the e-mail. We're populating it with the column names now in case our full query returns data. xmessage varchar2(32767); begin SELECT select my.Column1, my.Column2, my.Column3 as thirdColumn -- Tuck query results into the collection. BULK COLLECT INTO allmyResults FROM from myTable WHERE my.Column1 = 'aCertainCriteria'; -- If there are results returned by the query then we'll want to format the body of the message with column headers. if allmyResults.count > 0 then xmessage := 'There were rows returned by the message query, so this will be the first line of the message body.' --chr(10)chr(13) are carriage return, line feed. The pipe character (|'s) concatenate. ||chr(10)||chr(13)|| 'Second line of message body when data is returned by the query.' ||chr(10)||chr(13); 'Third line of message. With the following line being column headers (in the event rows were returned by your query).' ||chr(10)||chr(13)|| -- Adds column headers because your query returned data. myColumns ||chr(10)||chr(13); -- Loop through each element in the collection for i in allmyResults.first..allmyResults.last loop -- In the declaration we populated the myColumns variable with the names of your columns. The chr(9)'s insert tabs to delimit the data. xmessage := xmessage || allmyResults(i).Column1 ||chr(9)|| allmyResults(i).Column2 ||chr(9)|| allmyResults(i).ThirdColumn ||chr(10)||chr(13); end loop; else -- If the full query doesn't return rows you will follow this path. xmessage := 'There were no rows returned by the query and this will appear in the message body.' ||chr(10)||chr(13)|| 'This is the second line of the message body when no rows are returned.' ||chr(10)||chr(13); end if; -- Here is what sends the e-mail. sys.utl_mail.send( 'Sender@RyanBoyer.net', myRecipients,null, null, mySubject, xmessage, 'text/plain; charset=us-ascii', 3); END;