利用sqlplus,生成csv 文件，并且将csv文件作为邮件的附件发送出去

 这篇文章介绍，在Oracle EBS 前台，调用后台的sql 脚本，实现将查询出来的数据写入到csv文件，并且以邮件附件的形式发送出去。

/***************************************************************************************************************
* PROGRAM NAME : EBS 前台请求调用sqlplus
* DESCRIPTION  : 
* PARAMETERS   : 1   temp file path 
*                2   attachment file name
*                3   start date
*		         4   end date
*                5   email subject
*                6   sender email address   
*                7   mail list
* AUTHOR       :  daisy
*********************************************************************************************************************/

set linesize 2000
set trimspool on
set heading off
set echo off
set newpage none
set serveroutput on size 999999
set head off
set feed off
set define on
--GET PARAMETERS FROM USER INPUT
define path = &1
define temp_file =&2
define subject=&5
define v_sender=&6
define mail_table_list = &7

col s_start_date new_value start_date noprint
select to_char(to_date('&&v_start_date','yyyy/mm/dd hh24:mi:ss'),'YYYY-MM-DD','NLS_DATE_LANGUAGE = AMERICAN') s_start_date from dual;

col s_end_date new_value end_date noprint
select to_char(to_date('&&v_end_date','yyyy/mm/dd hh24:mi:ss'),'YYYY-MM-DD','NLS_DATE_LANGUAGE = AMERICAN') s_end_date from dual;

--路径下的文件
spool &path/&temp_file

--START WRITE MAIL ATTACHMENT
select 'aaa','bbb','ccc','ddd','eee','fff' from dual;
--end WRITE MAIL ATTACHMENT
spool off

--GET USER MAIL LIST AND CREATE MAIL COMMAND PARAMETERS
variable mail_list varchar2(1000);
BEGIN
  FOR r_cmd IN (SELECT cmd
                  FROM (SELECT purf.row_low_range_or_name,row_number() over(PARTITION BY purf.row_low_range_or_name ORDER BY purf.row_low_range_or_name,puc.user_column_name) seq,
                               pucif.VALUE mail,lead(pucif.VALUE,1) over(PARTITION BY purf.row_low_range_or_name ORDER BY purf.row_low_range_or_name,puc.user_column_name) s_t,
                               lead(pucif.VALUE,1) over(PARTITION BY purf.row_low_range_or_name ORDER BY purf.row_low_range_or_name,puc.user_column_name)||' '||pucif.VALUE cmd
                          FROM pay_user_tables put,pay_user_columns puc,pay_user_rows_f purf,pay_user_column_instances_f pucif
                         WHERE put.user_table_id = puc.user_table_id AND put.user_table_id = purf.user_table_id AND pucif.user_row_id = purf.user_row_id
                           AND pucif.user_column_id = puc.user_column_id AND put.user_table_name = '&&mail_table_list') 
 WHERE seq = 1) 
  LOOP
    :mail_list := :mail_list||' '||r_cmd.cmd;
  END LOOP;  
END;
/
col v_mail new_value v_mail_list noprint
select :mail_list v_mail from dual;
--USE LINUX COMMAND mutt TO SEND MAIL
--host /usr/local/bin/mutt -e "set charset=utf-8 from=&8 content_type=text/html" &v_mail_list -s "&v_subject" < &path/&temp_file
host /usr/local/bin/mutt -e "set charset=utf-8 from=&6 content_type=text/html" &v_mail_list -s "&&5" -a &path/&temp_file<&path/gdw.html
--host rm &path/&file_name &path/&temp_file
host rm &path/&temp_file
exit