SHELL+SQLLDR读取文件更新数据至ORACLE
- SQLLDR
- 实现目标
- 数据源文件test.txt
- sqlldr的test.ctl文件
- 解释：
- shell代码：输入两个参数日期、表名--->执行：从指定路径读取数据源文件，读取数据库的表结构（可空和不可空等）,创建该表对应的ctl文件，执行sqlldr脚本，完成数据插入-->返回数据是否全部成功插入
SQLLDR
实现目标
1.拿到一个test.txt的文件，通过shell脚本使用sqlldr命令，读取文件的内容插入到Oracle数据库中
2.注意点
- sqlldr命令可以从.ctl文件中设置数据源的格式、也可以通过sqlldr命令中写入数据
- 本文主要涉及三个文件”：
- 数据源文件test.txt（格式：123456|+|主题bala|+|xxx|+|2019 每个字段以
|+|分割） - slldr格式文件，test.ctl
- shell脚本文件：读取参数，生成新的ctl文件，执行sqlldr脚本，判断sqlldr脚本是否执行成功并返回
数据源文件test.txt
123456|+|主题bala|+|xxx|+|2019
123457|+|主题bala|+|xxx|+|2020
123458|+|主题bala|+|xxx|+|2021sqlldr的test.ctl文件
OPTIONS(DIRECT=true,parallel=true,ERRORS=50,skip=0 ,bindsize=512000,readsize=1024000,skip_index_maintenance=true)
LOAD DATA
CHARACTERSET UTF8
INFILE "/home/file/test_20191018.txt"
APPEND INTO TABLE test
FIELDS TERMINATED BY '|+|'
TRAILING NULLCOLS (
UUID
,TITLE CHAR(2000)
,URL CHAR(1000)
,CRAWL_TIME
)解释：
部分关键字:
userid -- ORACLE username/password
control -- 控制文件
log -- 记录的日志文件
bad -- 坏数据文件
data -- 数据文件
discard -- 丢弃的数据文件
discardmax -- 允许丢弃数据的最大值 (默认全部)
skip -- Number of logical records to skip (默认0)
load -- Number of logical records to load (默认全部)
errors -- 允许的错误记录数 (默认50)
rows --（每次提交的记录数，默认: 常规路径 64, 直接路径 全部，所以使用直接路径的话，效率会比普通的好太多太多）
bindsize --（ 每次提交记录的缓冲区的大小，字节为单位，默认256000)
silent -- 禁止输出信息 (header,feedback,errors,discards,partitions)
direct -- 使用直通路径方式导入 (默认FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- 并行导入
(默认FALSE，注意：parallel并不是让一个sqlldr语句起多个进程来加载数据,而是不锁住加载表,允许别的直接路径加载. 所以要使parallel起作用,应该先将要加载的数据文件分成多个,用多个sqlldr语句同时加载,如下例:
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默认FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable(默认FALSE,这个最好设置为TRUE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (默认 FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (默认 FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (默认 FALSE)
readsize -- size of read buffer (默认 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)
columnarrayrows -- number of rows for direct path column array (默认 5000)
streamsize -- size of direct path stream buffer in bytes (默认 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (默认 FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (默认 7200)
date_cache -- size (in entries) of date conversion cache (默认 1000)- 入库方式
************* 以下是4种装入表的方式
APPEND // 原先的表有数据 就加在后面
INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
REPLACE // 原先的表有数据 原先的数据会全部删除
TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据
*************shell代码：输入两个参数日期、表名—>执行：从指定路径读取数据源文件，读取数据库的表结构（可空和不可空等）,创建该表对应的ctl文件，执行sqlldr脚本，完成数据插入–>返回数据是否全部成功插入
#/bin/bash
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
input_date=$1
input_table_name=$2
#oracle数据库用户名、密码、地址
oracle_name=admin
oracle_password=admin
tns=127.0.0.1:1521/orcl
#sqlldr打日志
logdir=/usr/local/logfile
#sqlldr数据文件日志
baddir=/usr/local/badfile
#sqlldr存放ctlfile的位置
ctldir=/usr/local/ctlfile
set tw=100
set encoding=utf-8
#数据源文件存放位置,每天的存放一个文件夹里
localdir=/usr/local/$input_date
if [ -d $localdir ];then
echo 'file is existed'
else
echo 'file path is not existed'
mkdir -p $localdir
fi
#函数：create_ctl_file用来每天新建一个ctl文件，用来对每天新来的数据源文件进行指定格式、sqlldr配置
create_ctl_file(){
table_name=$1
temp=${ctldir}/temp/${table_name}.lst
table_upper_name=$(echo $table_name|tr '[a-z]' '[A-Z]')
table_lower_name=$(echo $table_name|tr '[A-Z]' '[a-z]')
src_data_file_path=${localdir}/${table_lower_name}_${input_date}.txt
echo 'create ctl:'$src_data_file_path
# 使用sqlplus读取系统结构表，获取每个字段的类型，如果某个字段的长度大于sqlldr一次最大能读取的255长度，那么要一定要指定类型CHAR（100），否则会报错
cols=`sqlplus -S $oracle_name/$oracle_password@$tns <<EOF
set pagesize 0
set heading off
set echo off
set trimspool on
set feedback off
set linesize 1000
spool $temp
select case
when t.COLUMN_ID = 1 then
' '
else
','
end || RPAD(RPAD(T.column_name, 32) || case
when t.DATA_TYPE in ('CHAR', 'VARCHAR', 'VARCHAR2') AND
T.DATA_LENGTH >= 255 THEN
'CHAR(' || T.DATA_LENGTH || ')'
ELSE
''
END,
100) as colm
from user_tab_columns t
LEFT JOIN USER_COL_COMMENTS P
ON T.COLUMN_NAME = P.column_name
AND T.TABLE_NAME = P.table_name
where T.table_name = '${table_upper_name}'
ORDER BY T.COLUMN_ID;
spool off
EOF`
#
ctlfile=${ctldir}/${table_lower_name}.ctl
#将字段类型、sqlldr的配置项写入到ctlfile里去
echo $ctlfile
echo "OPTIONS(DIRECT=true,parallel=true,ERRORS=50,skip=0 ,bindsize=512000,readsize=1024000,skip_index_maintenance=true)" > $ctlfile
echo 'run DATA' >>$ctlfile
echo 'CHARACTERSET UTF8' >>$ctlfile
echo 'INFILE '\"$src_data_file_path\" >>$ctlfile
echo 'APPEND INTO TABLE '$table_upper_name >>$ctlfile
echo "FIELDS TERMINATED BY '|+|'" >>$ctlfile
echo 'TRAILING NULLCOLS (' >>$ctlfile
cat $temp >>$ctlfile
echo ')' >>$ctlfile
}
#函数，run：执行sqlldr，判断执行结果
run(){
table_lower_name=$(echo $1|tr '[A-Z]' '[a-z]')
table_upper_name=$(echo $1|tr '[a-z]' '[A-Z]')
table_name=$table_lower_name
ctlfile=${ctldir}/$table_name.ctl
echo 'start create :'$ctlfile
#执行创建ctlfile函数
create_ctl_file $1
#执行sqlldr命令
runer=`sqlldr $oracle_name/$oracle_password@$tns control=$ctlfile log=${logdir}/${table_upper_name}_${input_date}.log bad=${baddir}/${table_upper_name}_${input_date}.bad `
#runer是执行的返回字段串，最后一个字段是插入的行数，比如"1234."，去除'.'，得出成功插入的行数sql_inserted_num
sql_inserted_num=`echo $runer|awk -F ' ' '{print $NF}'|head -c-2`
#判断是否执行成功，即判断数据源文件的数据是否都插入的表中
file_nums=`cat $src_data_file_path | wc -l`
if [ $file_nums -eq $sql_inserted_num ];then
echo ${table_upper_name}"插入成功"
exit 0
else
echo $file_nums
echo $sql_inserted_num
echo ${table_upper_name}"插入失败"
exit 1
fi
}
#执行run函数
run $input_table_name
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
