oracle - Want two excel sheets as an attachment in mail through PL/SQL -
my requirement send data coming oracle tables excel sheet attachment through mail customers. able 1 excel sheet 1 table attachment need 2 excel sheets 2 tables.
declare p_email varchar2(255) ; p_subject varchar2(255) := 'weekly report'; p_message clob; l_mailhost varchar2(255) := 'mail.abc.com'; l_mail_conn utl_smtp.connection; v_clob clob := empty_clob(); v_len integer; v_index integer; s_clob clob := empty_clob(); s_len integer; s_index integer; headerlines_v clob := empty_clob(); headerlines_s clob := empty_clob(); length_v integer; length_s integer; crlf varchar2(2) := chr(13)||chr(10); v_date varchar2(15) := to_char(trunc(sysdate),'dd_mm_yyyy'); cursor cur_a select a, b, c table1 cursor cur_b select x, y, z table2 begin /*table header in attachment*/ dbms_lob.createtemporary( v_clob, true ); headerlines_v := 'a,b,c'|| utl_tcp.crlf; --// create csv header line dbms_lob.writeappend( v_clob, length(headerlines_v), headerlines_v ); --// write clob dbms_lob.createtemporary( s_clob, true ); headerlines_s := 'x,y,z'|| utl_tcp.crlf; --// create csv header line dbms_lob.writeappend( s_clob, length(headerlines_s), headerlines_s ); --// write clob --// start loop add data lines csv select to_char(trunc(sysdate),'mm_dd_yyyy') v_date dual; --select * p_email email module_name = 'report'; p_message:= '<html> <body> <p> <font color="black",font face ="arial",font size ="2.5"> hello all, <br/><br/> please find attachment download details <br/> <br/>thanks <br/> application team </p> </body> </html>'; l_mail_conn := utl_smtp.open_connection(l_mailhost, 25); utl_smtp.helo(l_mail_conn, l_mailhost); utl_smtp.mail(l_mail_conn, 'abc@yahoo.in'); utl_smtp.rcpt(l_mail_conn, 'abc@gmail.com'); utl_smtp.open_data(l_mail_conn ); utl_smtp.write_data(l_mail_conn, 'mime-version: 1.0' || crlf || 'from: ' || 'abc@yahoo.in' || crlf || 'subject: '||'dowload status'||'['||v_date||']'|| crlf || 'to: ' || 'abc@gmail.com'|| crlf || 'content-type: multipart/mixed;' || crlf || ' boundary="---yourboundary"' || crlf ||crlf); -- email body utl_smtp.write_data(l_mail_conn, '-----yourboundary'||crlf); utl_smtp.write_data(l_mail_conn, 'content-type: text/html' || crlf); utl_smtp.write_data(l_mail_conn, 'content-transfer-encoding: 8bit' || crlf || crlf); utl_smtp.write_data(l_mail_conn, p_message||crlf); -- begin attachment utl_smtp.write_data(l_mail_conn, '-----yourboundary'||crlf); utl_smtp.write_data(l_mail_conn, 'content-type: text/plain;'||crlf); utl_smtp.write_data(l_mail_conn, 'content-transfer-encoding: 8bit' || crlf); --utl_smtp.write_data(l_mail_conn, 'mime-type: application/zip' || crlf); utl_smtp.write_data(l_mail_conn, 'content-disposition: attachment;'|| crlf); utl_smtp.write_data(l_mail_conn, ' filename="download_data_1||.csv"'||crlf||crlf); utl_smtp.write_data(l_mail_conn, utl_tcp.crlf); rec in cur_van loop v_clob := v_clob || || ',' || b || ',' || c || utl_tcp.crlf; end loop; v_len := dbms_lob.getlength(v_clob); v_index := 1; while v_index <= v_len loop utl_smtp.write_data(l_mail_conn, dbms_lob.substr(v_clob, 32000, v_index)); v_index := v_index + 32000; end loop; utl_smtp.write_data(l_mail_conn, utl_tcp.crlf); utl_smtp.close_data(l_mail_conn ); if dbms_lob.isopen( v_clob ) = 1 dbms_lob.freetemporary( v_clob ); end if; -- begin attachment utl_smtp.open_data(l_mail_conn ); utl_smtp.write_data(l_mail_conn, '-----yourboundary'||crlf); utl_smtp.write_data(l_mail_conn, 'content-type: text/plain;'||crlf); utl_smtp.write_data(l_mail_conn, 'content-transfer-encoding: 8bit' || crlf); --utl_smtp.write_data(l_mail_conn, 'mime-type: application/zip' || crlf); utl_smtp.write_data(l_mail_conn, 'content-disposition: attachment;'|| crlf); utl_smtp.write_data(l_mail_conn, ' filename="download_data_2||.csv"'||crlf||crlf); utl_smtp.write_data(l_mail_conn, utl_tcp.crlf); rec_2 in cur_syn loop s_clob := s_clob || x || ',' || y || ',' || z || utl_tcp.crlf; end loop; s_len := dbms_lob.getlength(s_clob); s_index := 1; while s_index <= s_len loop utl_smtp.write_data(l_mail_conn, dbms_lob.substr(s_clob, 32000, s_index)); v_index := s_index + 32000; end loop; utl_smtp.write_data(l_mail_conn, utl_tcp.crlf); utl_smtp.write_data(l_mail_conn, '-----yourboundary'||crlf); utl_smtp.write_data(l_mail_conn, utl_tcp.crlf || '.' || utl_tcp.crlf); if dbms_lob.isopen( s_clob ) = 1 dbms_lob.freetemporary( s_clob ); end if; utl_smtp.close_data(l_mail_conn ); utl_smtp.quit(l_mail_conn); exception when others dbms_output.put_line ('error: '|| sqlcode ||'err msg :'||sqlerrm); end;
after struggling lot, achieved above requirement. code is:
declare v_from_name varchar2(100) := 'abc@yahoo.in'; v_to_name varchar2(100) := 'abc@gmail.com'; v_subject varchar2(100); v_message_body clob; v_message_type varchar2(100) := ' text/html'; v_smtp_server varchar2(255) := 'mail.abc.com'; n_smtp_server_port number := 25; conn utl_smtp.connection; v_date varchar2(15) := to_char(trunc(sysdate),'dd/mm/yyyy'); type attach_info record ( attach_name varchar2(40), data_type varchar2(40) default 'text/plain', attach_content clob default '' ); type array_attachments table of attach_info; attachments array_attachments := array_attachments(); n_offset number; n_amount number := 1900; crlf varchar2(5) := chr(13) || chr(10); v_clob clob := empty_clob(); v_len integer; v_index integer; s_clob clob := empty_clob(); s_len integer; s_index integer; c_mime_boundary constant varchar2(256) := 'the boundary can anything'; headerlines_v clob := empty_clob(); headerlines_s clob := empty_clob(); length_v integer; length_s integer; cursor cur_a select a,b,c table1 trunc(data_date) = trunc (sysdate); cursor cur_b select x,y, z table2 trunc (cs_start_time) = trunc (sysdate); begin dbms_lob.createtemporary( v_clob, true ); headerlines_v := 'a,b,c'|| utl_tcp.crlf; --// create csv header line dbms_lob.writeappend( v_clob, length(headerlines_v), headerlines_v ); --// write rec in cur_a loop v_clob := v_clob || rec.rowid || ',' ||a || ',' ||b || ',' ||c || utl_tcp.crlf; end loop; dbms_lob.createtemporary( s_clob, true ); headerlines_s := ' x,y,z'|| utl_tcp.crlf; --// create csv header line dbms_lob.writeappend( s_clob, length(headerlines_s), headerlines_s ); --// write clob --// start loop add data lines csv rec_2 in cur_b loop s_clob := s_clob || || ',' || b || ',' ||c || utl_tcp.crlf; end loop; -- fill data example attachments.extend(2); in 1..2 loop select 'file1'|| '.csv','text/plain',v_clob || to_char(1) attachments(1) dual; select 'file2' || '.csv','text/plain',s_clob || to_char(2) attachments(2) dual; end loop; select to_char(trunc(sysdate),'mm/dd/yyyy') v_date dual; --select * p_email email module_name = 'report'; v_message_body:= '<html> <body> <p> <font color="black",font face ="arial",font size ="2.5"> hello all, <br/><br/> <br/> please find attachment today's download details<br/> <br/>thanks <br/>application team </p> </body> </html>'; -- open smtp connection ... conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port); utl_smtp.helo(conn, v_smtp_server); utl_smtp.mail(conn, v_from_name); utl_smtp.rcpt(conn, v_to_name); -- open data utl_smtp.open_data(conn); -- message info utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('to: ' || v_to_name || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('date: ' || to_char(sysdate, 'dy, dd mon yyyy hh24:mi:ss') || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('from: ' || v_from_name || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('subject: ' || 'dowload status'||'['||v_date||']'||crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('mime-version: 1.0' || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('content-type: multipart/mixed; boundary="secbound"' || crlf || crlf)); -- message body utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--secbound' || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('content-type: ' || v_message_type || crlf || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || crlf)); -- attachment part in attachments.first .. attachments.last loop -- attach info utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--secbound' || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('content-type: ' || attachments(i).data_type || ' name="'|| attachments(i).attach_name || '"' || crlf)); utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('content-disposition: attachment; filename="' || attachments(i).attach_name || '"' || crlf || crlf)); -- attach body n_offset := 1; while n_offset < dbms_lob.getlength(attachments(i).attach_content) loop utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset))); n_offset := n_offset + n_amount; end loop; utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || crlf)); end loop; -- last boundry utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw( '--secbound' || crlf)); if dbms_lob.isopen( v_clob ) = 1 dbms_lob.freetemporary( v_clob ); end if; if dbms_lob.isopen( s_clob ) = 1 dbms_lob.freetemporary( s_clob ); end if; -- close data utl_smtp.close_data(conn); utl_smtp.quit(conn); end;
Comments
Post a Comment