ORACL数据库 LOAD DATA 命令

心怀梦想

于 2021-08-08 17:24:30 发布

阅读量252

点赞数 1

分类专栏：数据库文章标签：数据库开发

本文链接：https://blog.csdn.net/u013688006/article/details/119517405

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

#!/bin/bash
set -o errexit
echo "LOAD DATA INTO DB"
file_name="LOAD_DLDY"
inputsql="load data infile 'dldy.txt' append into table CJ_DYDLQX_KFK fields terminated by  X'09' trailing nullcols(
YXDNBBS,
SJSJ \"case when :SJSJ <> 'NULL' then to_date(:SJSJ,'yyyy-MM-dd HH24:MI:ss') end \",
QZJSJ \"case when :SJSJ <> 'NULL' then to_date(:SJSJ,'yyyy-MM-dd HH24:MI:ss') end \",
ADY \"case when :ADY <> 'NULL' then trunc(to_number(:ADY),3) end \",
BDY \"case when :BDY <> 'NULL' then trunc(to_number(:BDY),3) end \",
CDY \"case when :CDY <> 'NULL' then trunc(to_number(:CDY),3) end \",
ADL \"case when :ADL <> 'NULL' then trunc(to_number(:ADL),3) end \",
BDL \"case when :BDL <> 'NULL' then trunc(to_number(:BDL),3) end \",
CDL \"case when :CDL <> 'NULL' then trunc(to_number(:CDL),3) end \",
LXDL \"case when :LXDL <> 'NULL' then trunc(to_number(:LXDL),3) end \",
SJLYDM
)"
echo $inputsql > ${file_name}.txt
source /etc/profile
sqlldr userid=hnjl/dw135dx24kf@10.95.63.52:1521/fzdb control=${file_name}.txt log=${file_name}.log   bad=${file_name}.err rows=20000

本次书写脚本命令遇到两个问题 1:分隔符分隔, 2:脚本文件格式

1: 如果数据是以','或者'|'分隔, fields terminated by ','或者'|',如果数据是以table键分隔.table键是不可见字符,需要这样写 X'09';

2:如果脚本是在window下编写, 文件格式是dos, 放到linux下使用, 需要转换格式
命令: dos2unix file

然后执行脚本, 如果出现错误可以查看log,或者err文件