Email attached CSV file of query from APEX

You can easily write a procedure to write the results of a SQL query in a CSV file, and then email this file as an attachment.

Firstly, you will need a function that takes a SQL query and outputs the text results of this query into a BLOB.

GET_REPORT Function - Turns a query into a BLOB of CSV format

The following function was written by Marc Sewtz (more information can be found: http://marcsewtz.blogspot.com/2008/04/generating-csv-files-and-storing-them.html )

create or replace function get_report (
p_query varchar2
) return blob is

l_cursor integer;
l_cursor_status integer;
l_col_count number;
l_desc_tbl sys.dbms_sql.desc_tab2;
l_col_val varchar2(32767);

l_report blob;
l_raw raw(32767);
begin

-- open BLOB to store CSV file
dbms_lob.createtemporary( l_report, FALSE );
dbms_lob.open( l_report, dbms_lob.lob_readwrite );

-- parse query
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, p_query, dbms_sql.native);
dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl );

-- define report columns
for i in 1 .. l_col_count loop
dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );
end loop;

-- write column headings to CSV file
for i in 1 .. l_col_count loop
l_col_val := l_desc_tbl(i).col_name;
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10); else l_col_val := '"'||l_col_val||'",'; end if; l_raw := utl_raw.cast_to_raw( l_col_val ); dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw ); end loop; l_cursor_status := sys.dbms_sql.execute(l_cursor); -- write result set to CSV file loop exit when dbms_sql.fetch_rows(l_cursor) <= 0; for i in 1 .. l_col_count loop dbms_sql.column_value(l_cursor, i, l_col_val); if i = l_col_count then l_col_val := '"'||l_col_val||'"'||chr(10); else l_col_val := '"'||l_col_val||'",'; end if; l_raw := utl_raw.cast_to_raw( l_col_val ); dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw ); end loop; end loop; dbms_sql.close_cursor(l_cursor); dbms_lob.close( l_report ); -- return CSV file return l_report; end get_report; Procedure to take the BLOB and email it as a CSV attachment I have then written a procedure to take this BLOB and send it via email as an attachment: create or replace procedure email_my_query ( p_query varchar2, p_email_to varchar2, p_subject varchar2 ) AS v_file blob; v_body varchar2(1000); v_id number; BEGIN v_file := get_report(p_query); v_body := 'Here is your CSV File'; begin wwv_flow_api.set_security_group_id; end; v_id := apex_mail.send( p_to => p_email_to, p_from => ' This e-mail address is being protected from spambots. You need JavaScript enabled to view it. ', p_body => v_body, p_subj => p_subject); apex_mail.add_attachment( p_mail_id => v_id, p_attachment => v_file, p_filename => 'test.csv', p_mime_type => 'text/plain'); apex_mail.push_queue(); END email_my_query; Test the Procedure Now all you have to do is run the following to test it out! BEGIN email_my_query('select * from mytable', ' This e-mail address is being protected from spambots. You need JavaScript enabled to view it. ', 'This is a CSV Email'); END; This could then be further extended by creating a scheduled job to routinely email you results from a table. Download an example in a package: <a target="_blank" class="jce_file" title="EMAIL_CSV_DATA_PKG.txt" href="images/stories/APEX/SampleCode/EMAIL_CSV_DATA_PKG.txt"> EMAIL_DATA_CSV_PKG.sql</a> '

Email attached CSV file of query from APEX

New Articles

Popular Articles

I'm being updated!