生成txt文件

#!/bin/bash
dir=`date -d "-1 day" +%Y%m%d`
day=`date -d "-1 day" +%Y%m%d`01
day1=`date -d "-1 day" +%Y-%m-%d`
sqlplus -s wyypt/wyypt <<EOF > /home/oracle/jrdt/$dir/T_WR_EXAM_$day.txt
set pages 0
set feed off
set heading off
set feedback off
set verify off
SET LINESIZE 2000
#以下解决换行，字段过长问题
set long 2000000000
set longchunksize 255
set echo OFF
set trimspool on
set trimout on
SET WRAP OFF
set termout off

SELECT    EXAM_ID
       || '|'
       || EXAM_NAME
       || '|'
       || EXAM_ST_TIME
       || '|'
       || EXAM_ED_TIME
       || '|'
       || EXAM_CONF
	    || '|'
       || EXAM_STATUS
	    || '|'
       || CREATE_DATE
	    || '|'
       || TYPE
	    || '|'
       || MAX_TIMES
	    || '|'
       || HAS_WHITE_LIST
  FROM T_WR_EXAM;
EOF

发送（获取）文件到其他服务器

#!/bin/bash
dir=`date -d "-1 day" +%Y%m%d`
day=`date -d "-1 day" +%Y%m%d`01
cd /home/oracle/jrdt/$dir
# 先判断文件是否已经生成了
if [ -f "T_WR_EXAM_SCORE_$day.txt" ];then
ftp -n<<!
open 172.20.14.50
user oracle tdkj258@%*
binary
cd /home/oracle/jrdt/$dir
prompt
put(get) T_WR_EXAM_SCORE_$day.txt
close
bye
!
else
echo "文件不存在"
fi

数据库导入文件

#!/bin/bash
source /home/oracle/.bash_profile
dir=`date -d "-1 day" +%Y%m%d`
day=`date -d "-1 day" +%Y%m%d`01

if [ -f "/home/oracle/jrdt/$dir/T_WR_EXAM_$day.txt" ];then
sqlldr jrdt/jrdt2020@fapp control=/home/oracle/jrdt/import/T_WR_EXAM.ctl data=/home/oracle/jrdt/$dir/T_WR_EXAM_$day.txt log=/home/oracle/jrdt/logs/T_WR_EXAM_$day.log
cp /home/oracle/jrdt/$dir/T_WR_EXAM_$day.txt /home/oracle/jrdt/backup/T_WR_EXAM_$day.txt
rm -f /home/oracle/jrdt/$dir/T_WR_EXAM_$day.txt
else
echo " 文件不存在"
fi

导入文件控制器脚本.ctl

load data
# 文件编码解决
CHARACTERSET ZHS16GBK   
truncate into table T_WR_EXAM
fields terminated by '|'
TRAILING NULLCOLS
(EXAM_ID, EXAM_NAME, EXAM_ST_TIME, EXAM_ED_TIME, EXAM_CONF, EXAM_STATUS, CREATE_DATE, TYPE, MAX_TIMES, HAS_WHITE_LIST)

导入文件出现中文乱码

请先查询当前数据库编码

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

在导入文件控制器中加入 characterset xxx

导出文件中文乱码

先用sqlplus查询编码

select userenv(‘language’) from dual;

在环境变量.bash_profile中加入

export NLS_LANG=xxxx