Oracle表按字段和|分格符导出文件

---恢复内容开始---

1.表数据按字段拼接查询

select CUST_ID||'|'||CREDIT_GRADE||'|'||CREDIT_AMOUNT||'|'||EXCEPTION_AMOUNT||'|'||to_char(EFF_DATE,'YYYY-MM-DD HH24:MI:SS')||'|'||BILLING_CYCLE_ID||'|'||ACCT_ID from CUST_CREDIT

2.创建导出脚本 o.sh

#!/bin/sh
export `NLS_LANG=AMERICAN_AMERICA.ZHS16GBK`
filepath=/capritu
sqlname="CUST_CREDIT"
sqlstatement="select CUST_ID||'|'||CREDIT_GRADE||'|'||CREDIT_AMOUNT||'|'||EXCEPTION_AMOUNT||'|'||to_char(EFF_DATE,'YYYY-MM-DD HH24:MI:SS')||'|'||BILLING_CYCLE_ID||'|'||ACCT_ID from "

csvfile="$filepath/$sqlname.csv"
logpath="$filepath/$sqlname.log"

outputdb()
{
sqlplus -S mvno_comm/mvno!comm_sh<< EOF
set head off;
set feedback off;
set pages 0;
set trimout on;
set trimspool on;
set verify off;
set linesize 1000;

spool $csvfile
$sqlstatement $sqlname;
spool off;

exit;
EOF
}
outputdb

3.后台挂起执行脚本

nohup ./o.sh >/dev/null 2>&1 &

转载于:https://www.cnblogs.com/capri-tu/p/10524646.html