17,377
社区成员
发帖
与我相关
我的任务
分享
cat /home/oracle/dbscripts/SendMail.sh
#!/bin/bash
username=ccps
pass=lotery2013
FileName=$1
Title=$2
SendTo=$3
#CLOB_DIR="/home/oracle/dbscripts/logs"
sqlplus -s $username/$pass <<EOF
set serveroutput on;
declare
l_bfile bfile;
l_clob clob;
l_str varchar2(1000);
src_offset number:=1;
dst_offset number:=1;
lang_ctx number:= dbms_lob.default_lang_ctx;
charset_id number:=0;
warning number;
begin
charset_id := NLS_CHARSET_ID('ZHS16GBK');
insert into ccps.ccps_sendmails
VALUES (ccps.ccps_sendmails_seq.nextval, '$SendTo', 'ccps','dba01@lotery.hk','lotery1401','$Title',
empty_clob(),'smtp','smtp.gmail.com','465', null, null, 0, 10000,0, sysdate, sysdate, null,10309001,
'sharkdb01','2013012405321967635495',2) returning S_CONTENT into l_clob;
l_bfile := bfilename('CLOB_DIR','$FileName');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadclobfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile),
src_offset,dst_offset,charset_id,lang_ctx,warning);
dbms_lob.fileclose(l_bfile);
commit;
exception when others then
l_str:=sqlerrm(sqlcode);
dbms_output.put_line(l_str);
end;
/
exit;
EOF