oracle load.sh 导入文本
来源：互联网 发布：淘宝csgo买哪个版本 编辑：程序博客网 时间：2024/06/15 05:50
oracle load.sh 是通过shell对sqlldr重新封装，使sqlldr使用更方便，导入文本更方便，不用再很麻烦的配置sqlldr控制文件。
1 可以根据传入参数指定分隔符 -a
2 指定行结束符 -e
3 指定字段内容被特殊字符包裹 -ae
4 指定装入模式 -m
5 指定装入文本字符集
6 指定控制文件来源 -cm 若为A 追加 -c 自定义控制文件内容 若为H 则根据第一行标题 若为R则根据-c自定义控制文件导入
自定一控制文件格式为：
字段@字段处理
-----------------
eg:sqlldr.config
ETL_DT@"NVL(:ETL_DT,'2010-01-01')"
------------------------------------
如以上控制文件若ETL_DT 为空则写入'2010-01-01'
源码：
#!/bin/ksh############################################################################################脚本名称: load.sh#脚本功能: 导入数据文件到指定数据库的指定数据表中#涉及文件:#脚本调用: sh load.sh -u user -p passwd -i sid -a '|' -f file.unl -t tablename#脚本参数: #输出文件: #返回值 : #使用实例: # sh load.sh -u fhts -p fhts -i odsbptdb -a '|' -f tmp.unl -t tmp -m truncate# yyyy-mm-dd (日期格式，如果不填默认yyyymmdd)#编写人 : sqniu#编写日期: 2009/10/16###########################################################################################usage(){ echo echo " $0(数据装载)" echo " ====================" echo " Usage:" echo " sh $0 -u -p -i -t -f -a -e -d " echo " 参数:" echo " -u oracle数据库用户" echo " -p oracle数据库密码" echo " -i oracle tra_id" echo " -t 要装载的数据库表" echo " -f 需要装载数据文件(包括绝对路径)" echo " [-a] 记录分隔符,默认为'|'" echo " [-ae] 包含字段数据特殊符" echo " [-d] 载入时间类型" echo " [-l] 装载数据行数 默认全部装入" echo " [-e] 载如文件的结束符" echo " [-nls] 装载数据字符集 UTF8 或者ZHS16GBK" echo " [-s] 跳过文件行数" echo " [-m] 数据装载方式TRUNCATE APPEND INSERT,默认TRUNCATE" echo " [-txt] 文件预处理S清除文件空格A文件尾加分隔符U文件转换UNIX格式" echo " [-c] 参数文件 字段名@字段后置处理" echo " [-cm] 参数文件内容默认追加，A 末尾追加，R 全量替换 ，H 依据文件头信息" echo exit -1}# 函数writeLog(){ echo -e $1 echo -e $1 >>$LOG}mk_control_file(){_USER_PSWD=$1_Separator=$2_File=$3_TableName=$4_Meathod=$5DescTempFile=${Base_File}.desc.$DescTempFile2=${Base_File}.2.desc.$SqlLog=${Base_File}.log.$sqlplus -S ${_USER_PSWD} <<TMP >>/dev/nullset echo off;set feedback off;set tab off;set heading off;set pagesize 0;set linesize 1000;set numwidth 12;set termout off;set trimout on;set trimspool on;spool ${DescTempFile};desc ${_TableName}spool off;TMPif [ $? != 0 ]then writeLog "\n====================\n数据库连接错误！\n====================" exit -1fiLine_File="AAAAA"if [ -f ${DescTempFile} ]then cat ${DescTempFile}|sed '1,2d'|sed 's/[ ][ ]*/ /g'|sed '$d'|sed 's/^ //g'|awk '{if ($2=="DATE" || $4=="DATE") {print $1 " DATE \"'${DateType}'\""} else {print $1}}'|sed's/$/,/g'|sed '$s/,$//'|sed '/^$/d'|sed 's/NOT NULL //g'|sed 's/ VARCHAR2/ CHAR/g'|sed 's/ VARCHAR/ CHAR/g'>>${DescTempFile2}Line_File=`head -1 ${DescTempFile}`else rm -f ${DescTempFile} rm -f ${DescTempFile2} writeLog "\n====================\n数据库连接错误！\n====================" exit -1fiif [ ${Line_File:0:5} = "ERROR" ]thenrm -f ${DescTempFile}rm -f ${DescTempFile2}writeLog "\n====================\n表不存在！\n===================="exit -1fiecho "LOAD DATA ${Nls_String} INFILE '${_File}' ${Line_closed} BADFILE 'bad_${_TableName}.bad' ${_Meathod} INTO TABLE ${_TableName} FIELDS TERMINATEd BY \"${_Separator}\" " > ${Base_File}.ctlecho ${End_Sqlite} >>${Base_File}.ctlecho "TRAILING NULLCOLS ">>${Base_File}.ctlecho "(">>${Base_File}.ctlcat ${DescTempFile2} >>${Base_File}.ctlrm -f ${_TableName}rm -f ${DescTempFile}rm -f ${DescTempFile2}}mk_exec_shell(){_USER_PSWD=$1_Separator=$2_File=$3_TableName=$4_SkipRow=$5 echo "sqlldr ${_USER_PSWD} control=${Base_File}.ctl skip=${_SkipRow} ${Load_rows} rows=${CtlRow} errors=10000 bindsize=${BindSize} readsize=${ReadSize} log=log_${Base_File}.log bad=bad_${Base_File}.bad" > load_${Base_File}.sh}if [ $# -lt 5 ]then echo "参数有误,请检查......" usagefiwhile [ 1 ] do if [ "$1" = "-u" ] then shift 1 user=$1 fi if [ "$1" = "-p" ] then shift 1 passwd=$1 fi if [ "$1" = "-i" ] then shift 1 server=$1 fi if [ "$1" = "-t" ] then shift 1 TableName=$1 fi if [ "$1" = "-f" ] then shift 1 File=$1 fi if [ "$1" = "-a" ] then shift 1 Separator=$1 fi if [ "$1" = "-d" ] then shift 1 DateType=$1 fi if [ "$1" = "-s" ] then shift 1 SkipLine=$1 fi if [ "$1" = "-m" ] then shift 1 Meathod=$1 fi if [ "$1" = "-l" ] then shift 1 Load_rows=$1 fi if [ "$1" = "-ae" ] then shift 1 End_Sqlite=$1 fi if [ "$1" = "-e" ] then shift 1 Line_closed=$1 fi if [ "$1" = "-nls" ] then shift 1 Nls_String=$1 fi if [ "$1" = "-c" ] then shift 1 Config=$1 fi if [ "$1" = "-txt" ] then shift 1 Prepare=$1 fi if [ "$1" = "-cm" ] then shift 1 ConfigP=$1 fi shift 1 if [ $# -eq 0 ] then break fi done if [ "$SkipLine" = "" ] then SkipLine=0 fi if [ "$Separator" = "" ] then Separator="|" fi if [ "$DateType"x = ""x ] then DateType=yyyymmdd fi if [ "$Meathod" = "" ] then Meathod=TRUNCATE fi if [ "$End_Sqlite" != "" ] then End_Sqlite="OPTIONALLY ENCLOSED BY "$End_Sqlite fi if [ "$Load_rows" != "" ] then Load_rows="load="$Load_rows fi if [ "$Nls_String" != "" ] then Nls_String="CHARACTERSET "$Nls_String fi if [ "$Line_closed" = "w" ] then Line_closed=\""str "X\'0D0A\'\" fiUSER_PSWD=$user/$passwd@$serverBase_File=`basename $File`# 环境变量区DATE=`date +%Y%m%d`LOG=$HOME/log/$DATE/LoadTable/$File.$DATE LOG_DIR=`dirname $LOG`CtlRow=100000BindSize=16384000ReadSize=16384000if [ ! -d $LOG_DIR ]then mkdir -p $LOG_DIR retcode=$? if [ "$retcode" != "0" ] then echo -e "\n==================\n 不能创建日志文件路径\n==================" exit -1 fifiif [ ! -f $LOG ]then >$LOG retcode=$? if [ "$retcode" != "0" ] then echo -e "\n==================\n 不能创建日志文件 \n===================" exit -1 fifi cp $File ${File}.tempif [ "${Prepare:0:1}" = "U" ] || [ "${Prepare:1:1}" = "U" ] || [ "${Prepare:2:1}" = "U" ]then dos2unix ${File}.tempfi if [ "${Prepare:0:1}" = "S" ] || [ "${Prepare:1:1}" = "S" ] || [ "${Prepare:2:1}" = "S" ]then sed -i 's/ //g' ${File}.tempfiif [ "${Prepare:0:1}" = "A" ] || [ "${Prepare:1:1}" = "A" ] || [ "${Prepare:2:1}" = "A" ]then sed -i 's/$/'${Separator}'/g' ${File}.tempfimk_control_file $USER_PSWD $Separator ${File}.temp $TableName $Meathodif [ "$ConfigP" = "" ]then echo ")">>${Base_File}.ctlfiif [ "$ConfigP" = "A" ]then while read LINE do if [ `echo ${LINE:0:1}` == '#' ] then continue fi Tab_Line1=`echo ${LINE}|awk -F '@' '{print $1}'|tr a-z A-Z` Tab_Line2=`echo ${LINE}|awk -F '@' '{print $2}'` sed -i 's/'${Tab_Line1}'/'${Tab_Line1}${Tab_Line2}'/g' ${Base_File}.ctl done<${Config}fiif [ "$ConfigP" = "R" ]then sed -i '5,$d' ${Base_File}.ctl while read LINE do if [ `echo ${LINE:0:1}` == '#' ] then continue fi Tab_Line1=`echo ${LINE}|awk -F '@' '{print $1}'|tr a-z A-Z` Tab_Line2=`echo ${LINE}|awk -F '@' '{print $2}'` echo ${Tab_Line1}${Tab_Line2},>> ${Base_File}.ctl done<${Config} sed -i '$s/,$//' ${Base_File}.ctl sed -i '/^$/d' ${Base_File}.ctl echo ")" >>${Base_File}.ctlfiif [ "$ConfigP" = "H" ]then SiikipLine=1 sed -i '5,$d' ${Base_File}.ctl head -1 ${File}.temp >${Base_File}.hctl sed -i 's/'${Separator}'/,/g' ${Base_File}.hctl sed -i '$s/,$//' ${Base_File}.hctl sed -i '/^$/d' ${Base_File}.hctl cat ${Base_File}.hctl >>${Base_File}.ctl echo ")" >>${Base_File}.ctlfimk_exec_shell $USER_PSWD $Separator ${File}.temp $TableName $SkipLineecho -e "\n=================================\n日志:log_${Base_File}.log\n=================================- oracle load.sh 导入文本
- 文本数据导入Oracle
- oracle 导入文本数据
- oracle文本导出导入实例
- ORACLE sql load 数据导入笔记
- oracle load导入TXT文件总结
- oracle load data 导入数据方法总结
- oracle大数据导入之sql load
- sql*load批量导入Oracle数据库
- informix 从文本导入海量数据(informix load)
- mysql导入文本数据LOAD DATA INFILE语法
- jquery 之div 导入文本数据load 数据方法
- Oracle的txt数据导入【Oracle数据库的Load详解】
- Oracle中导入文本数据的方法
- 使用控制文件，将文本导入oracle
- oracle的文本导入、导出技巧
- CSV文本数据如何导入oracle(实例)
- Oracle中导入文本数据的方法
- linux c 字符串左补指定字符
- 使用第3方组件 Myxls 实现gridView 分页导出
- GT-Grid的帮助文档汇总
- Android UI开发第九篇——SlidingDrawer 抽屉效果
- 【共享】人力资源管理部分流程图
- oracle load.sh 导入文本
- 关于TextBox扩展控件验证问题
- 按条件查询 因为翻页是按刷新实现的 只能把条件写在session里
- linux环境变量
- AZ Award 项目经验总结
- libevent跨平台事件驱动库简介
- zencart 数据迁移到magento 数据库操作
- oracle 可选模糊条件查询
- 日期添加几天的解决方法
