本文遵循BY-SA版权协议，转载请附上原文出处链接。

本文作者: 黑伴白

本文链接: http://heibanbai.com.cn/posts/56abc3d9/

通过spool工具抽取Oracle数据库表数据落地文件

前言

此前博文写了通过sqlldr工具装载数据入Oracle数据库表，今天我们再说下，通过spool工具抽取Oracle数据库表数据，落地为数据文件

spool参数释义

• pagesize 设置每页允许输出的最大行数，默认为24，避免分页可设定为0
• linesize 设置每行允许输出的最大字符,默认为80，这个应该根据需要设置,太小会造成该行显示不全，太大了会导致导出的文件变大
• feedback 设置是否显示处理记录的条数
• heading 设置是否显示数据的字段名
• echo 设置是否显示执行的SQL命令
• closep 设置域输出分隔符
• trimspool 设置是否去除每行末尾的空格
• termout 设置是否显示脚本中的命令执行结果
• newwidth 设置输出number类型域的长度
• timing 设置是否显示“已用时间：xxx”
• wrap 让它不要自动换行，当行的长度大于LINESIZE的时候，超出的部分会被截掉
• newpage 设置页与页之间的分隔{1|n|NONE};当值为0时在每页开头有一个小的黑方框;当值为n时在页和页之间隔着n个空行;当为none时，会在页和页之间没有任何间隔

脚本参数说明

• dbSid: Orale库SID
• dbUser: Oracle数据库用户名
• dbPass: Oracle数据库密码
• ctlFile: 数据控制文件
• dataFile: 数据文件
• datadate: 数据日期
• dbcode: 数据库编码

ctl控制文件说明

ctl文件中为sql语句
注意：语句中不能带有分号”;”

ctl文件示例

select
EMPNO||'|'||
ENAME||'|'||
JOB||'|'||
MGR||'|'||
HIREDATE||'|'||
SAL||'|'||
COMM||'|'||
DEPTNO
from emp

脚本源码

#!/bin/bash

dbSid=$1
dbUser=$2
dbPass=$3
ctlFile=$4
dataFile=$5
datadate=$6
dbcode=$7

ctlName=`basename ${ctlFile}`

#Sys data [yyyymmdd]
sysDate=`date +'%Y%m%d'`

#Define log params
logPath=/app/log/public/ora_spool_data/${sysDate}
logFile=${logPath}/${ctlName}.log

#Log function
Log()
{
  createTime=`date  +"%Y-%m-%d %H:%M:%S"`
  echo "[${createTime}] $*" |tee -a ${logFile} 2>/dev/null
}

#CheckDir function
CheckDir()
{
  if [ ! -d $1 ]; then
    mkdir_log=` mkdir -p -m 775 $1 `
    if [ $? -ne 0 ]; then
      Log "[ERROR] [$1] create failed, dir not exist, please check..."
      exit 255
    fi    
  fi
}
CheckDir "${logPath}"

###################   Main program   ###################
#check the parameter
if [ $# -ne 6 -a $# -ne 7 ]; then
   Log "[ERROR] Please input the right parameter ..."
   Log "[ERROR] Eg:sh ora_spool_data.sh oraSid user passwd /app/file/userinfo.ctl /app/file/userinfo.del"
   exit 1
fi

#Check ctlFile
if [ ! -f ${ctlFile} ];then
    Log "[ERROR] ${ctlFile} not exist, please check..."
    exit 2
fi

if [ "${dbcode}" != "X" ];then
    export NLS_LANG=${dbcode}
fi

#Check datapath
datapath=`dirname ${dataFile}`
if [ ! -d ${datapath} ];then
   Log "[ERROR] ${datapath} is not exists, please check...."
   exit 3
fi

Log "=============${ctlFile}============="
Log "[INFO]: Begin to spool data to ${dataFile} from ${tabName}"
#Begin time
beginTime=`date +"%Y-%m-%d %H:%M:%S"`

sql=`cat ${ctlFile}|sed -e 's/#datadate#/'${datadate}'/g'`

Log "${sql}"

sqlplus -s ${dbUser}/${dbPass}@${dbSid}<<EOF>/dev/null
set trimspool on
set echo off
set linesize 30240
set pagesize 0
set newpage 1
set heading off
set term off
set feedback off

spool ${dataFile}
$sql;
exit;
spool off
EOF

#End time
endTime=`date +"%Y-%m-%d %H:%M:%S"`

Log "[INFO]: beginTime: ${beginTime}"
Log "[INFO]: endTime: ${endTime}"

costTime=`expr $(date +%s -d "${endTime}") - $(date +%s -d "${beginTime}")`
hour=`expr $costTime / 3600`
min=`expr $costTime % 3600 / 60`
sec=`expr $costTime % 3600 % 60`

Log "[INFO]: End to spool data to ${dataFile} from ${tabName}, ${hour}h${min}m${sec}s..."
Log "[INFO]: dataFile:${dataFile}"
Log "[INFO]: logFile :${logFile}"

---

蚂蚁再小也是肉🥩！