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: create or replace procedure "Tallarico"."RyanBoyerSpreadsheetDemo"; as boyer_date number := 0; lv_subject VARCHAR2(200); lv_sender VARCHAR2(200) := 'Oracle@RyanBoyer.net'; -- Sender, but use servername@localdomain name if it doesn't have a corresponding mailbox lv_recipients VARCHAR2(200) := 'recipient@RyanBoyer.net'; -- Recipients is comma-delimited lv_priority PLS_INTEGER := NULL; -- Configurable lv_last BOOLEAN := FALSE; lv_count NUMBER := 0; lv_message VARCHAR2(32000); lv_conn utl_smtp.connection; lv_mycolumns VARCHAR2(500) := 'ColumnA, ColumnB, ColumnC,ColumnD'||chr(13); CURSOR cur_query IS /* Insert your query here */ select ColumnA as a, ColumnB as b, ColumnC as c, ColumnD as d from Table1; BEGIN /* The following will get today's date */ select TO_CHAR(CURRENT_DATE, 'YYYYMMDD') into boyer_date FROM dual; lv_subject := 'Here is the spreadsheet for ' || boyer_date; -- Configure to taste lv_conn:= demo_mail.begin_mail(lv_sender, lv_recipients, lv_subject, demo_mail.multipart_mime_type, lv_priority); demo_mail.begin_attachment (lv_conn,'text/html',FALSE,'test.csv','7 bit'); demo_mail.write_text( lv_conn, lv_mycolumns); FOR rec IN cur_query LOOP /* Depending on the number of columns being returned in the query, the lv_message could be quite large */ lv_message := rec.a||','||rec.b||','||rec.c||','||rec.d||chr(13); demo_mail.write_text( lv_conn,lv_message); END LOOP; demo_mail.end_attachment(lv_conn,TRUE); demo_mail.end_mail(lv_conn); END; / SHOW ERRORS