Hi
I am using Oracle 11g.
I have a requirment to read data from couple of tables which while written to a file takes more than 10 MB or even more dependng upon the date range.
This file I want to send as an email attachement.
1. I tried UTL_TCP to read table data and write it to a clob and finally dispose it as an attachement and send the email. However I could not find any proper code to compress the attached file.
2. I found anoher way of writing data to an external file on server using UTL_FILE and then compress it, using utl_compress.lz_compress usibg below code.
3. Now my requiremnt is to attach this file in an email using Oracle PLSQL code.
Could you please help me to know how to attach an external file from server in an email?
DECLARE
in_filename VARCHAR2(100);
src_file BFILE;
v_content BLOB;
v_blob_len INTEGER;
v_file utl_file.file_type;
v_buffer RAW(32767);
v_amount BINARY_INTEGER := 32767;
v_pos INTEGER := 1;
BEGIN
in_filename := 'empdata.csv';
src_file := bfilename('DIR1', in_filename);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
v_content := utl_compress.lz_compress(src_file, 9);
v_blob_len := dbms_lob.getlength(v_content);
v_file := utl_file.fopen('DIR1', in_filename || '.gz','wb');
WHILE v_pos < v_blob_len
LOOP
dbms_lob.READ(v_content, v_amount, v_pos, v_buffer);
utl_file.put_raw(v_file, v_buffer, TRUE);
v_pos := v_pos + v_amount;
END LOOP;
utl_file.fclose(v_file);
EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(v_file) THEN
utl_file.fclose(v_file);
END IF;
RAISE;
END;
/
Thanks
Arun