linux 下,可以使用cli程序把csv文件导入到数据库。具体代码如下:
$ORACLE_HOME/bin/sqlplus -S username/password@instanceName > /dev/null 2>&1 <<!!#对sqlplus作一些设置,使之只输出我们需要的文本
set echo off
set pagesize 0
set verify off
set feedback off
set termout off
set linesize 3000
set trimspool on
#查询输出到临时文件
spool /tmp/some_tmp.csv
#select语句
select column1||','||column2||','||column3 ....
from some_table where condition
spool off
#这里可以添加多个查询,且每个查询可输出到不同的文件,如下面注释
#spool /tmp/some_tmp.csv
#select column1||','||column2||','||column3 ....
#from some_table where condition
#spool off
set markup HTML off
quit
!!
#计算记录总行数,如果是0则不发邮件
NUM=`cat /tmp/some_tmp.csv | wc -l`
if[ $NUM -gt 0 ]
then
#先写excel的每列的title
echo 'Title_Of_Column1, Title_Of_Column2, ....Title_Of_ColumnN' > /tmp/attachement.csv
#数据正文
cat /tmp/some_tmp.csv >> /tmp/attachement.csv
#发邮件
(
echo "From: [email protected]"
echo "To: [email protected]"
echo "MIME-Version: 1.0"
echo "Content-Type: multipart/mixed"
echo ' boundary="A_Boundary_Name_You_Can_Change_It"'
echo "Subject: Subject"
echo ""
echo ""
#附件分隔符,上面的boundary前面加--
echo "--A_Boundary_Name_You_Can_Change_It"
echo 'Content-Transfer-Encoding: x-uuencode'
echo 'Content-Type: text/csv'
echo ' name="attachement.csv"'
echo 'Content-Disposition: attachment'
echo ' filename="attachement.csv"'
echo ""
uuencode /tmp/attachement.csv attachement.csv
echo "--A_Boundary_Name_You_Can_Change_It"
#附件结束
) | mailx -t
fi
#删除临时文件
rm -f /tmp/*.csv
exit 0
如下为Java调用本地的转换文件,其中String[] params就是参数,示例传递了两个参数:“123”, “234”,传递的参数全部是字符串类型,使用时需要转换成具体的格式:[java] view plaincopy
/**
* 调用本地的转换文件(带参数)
*
* @Description:
* @param transFileName
* @throws KettleException
* @author 李文锴
* @since:2012-8-15 下午02:58:54
*/
public static void callNativeTransWithParam(String transFileName) throws KettleException {
// 初始化
EnvUtil.environmentInit()
StepLoader.init()
// 转换元对象
TransMeta transMeta = new TransMeta(transFileName)
// 转换
Trans trans = new Trans(transMeta)
String[] params = {"123", "234"}// 传递参数
// 执行转换
trans.execute(params)
// 等待转换执行结束
trans.waitUntilFinished()
}
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)