linux sqlload 管道,SQLLOAD应用举例

sqlload_ctl.sh文件内容如下:

. ${HOME}/.biconfig

TABNAME=tb_individual_bill

INTCODE=05111

#!/bin/sh

sql_clean() {

sqlplus -s ${loghwods}

set heading off;

set verify off;

set feedback off;

set show off;

set trim off;

set pages 0;

set concat on;

set lines 300;

set trimspool on;

set trimout on;

whenever sqlerror exit 20;

truncate table ${TABNAME};

exit 0;

EOF

}

infile_list() {

unset FLIST

export FLIST=`find $FDIR -name "*${NOW}*.AVL"`

bb=`echo $FLIST|wc -w |awk '{printf"%sn",$1}'`

if [ $bb -eq 0 ]

then

echo "没有文件"

exit 0

fi

>${INFILE_LIST}

for F in ${FLIST}

do

echo "INFILE "${F}"" >> ${INFILE_LIST}

done

}

control_fields() {

sqlplus -s ${loghwods}

set heading off;

set verify off;

set feedback off;

set show off;

set trim off;

set pages 0;

set concat on;

set lines 300;

set trimspool on;

set trimout on;

spool ${FIELDS_TMP};

/*****************

select decode (rownum, 1, ' ', ' , ')||

rpad (column_name, 33, ' ')||

'"trim(:'||

trim(column_name)||

')"'

from user_tab_columns

where table_name= upper ('${TABNAME}')

order by column_id;

*****************/

SELECT

decode (xh, 1, ' ', ' , ')||

rpad (column_name, 33, ' ')||

rpad('CHAR('|| data_length ||')', 16, ' ')||

'"trim(:'||

trim(column_name)||

')"'

FROM

(

select

RANK() OVER ( PARTITION BY table_name ORDER BY column_id ASC ) AS xh

,column_name

,data_length

from user_tab_columns

where table_name= upper ('${TABNAME}')

) A

ORDER BY A.xh;

select ')' from sys.dual;

spool off;

exit;

EOF

}

control_head() {

echo "LOAD DATA" > ${CTL_HEAD_FILE}

}

control_fix1() {

echo "APPEND" >${CTL_FILE_FIX1}

echo "INTO TABLE ${TABNAME}" >> ${CTL_FILE_FIX1}

echo "FIELDS TERMINATED BY ' '" >> ${CTL_FILE_FIX1}

echo "TRAILING NULLCOLS" >> ${CTL_FILE_FIX1}

echo "(" >> ${CTL_FILE_FIX1}

}

combine_files() {

>${CONTROL_FILE}

cat ${CTL_HEAD_FILE} >> ${CONTROL_FILE}

cat ${INFILE_LIST} >> ${CONTROL_FILE}

cat ${CTL_FILE_FIX1} >> ${CONTROL_FILE}

cat ${FIELDS_TMP} >> ${CONTROL_FILE}

}

init_tmpfile() {

>${CTL_HEAD_FILE}

>${INFILE_LIST}

>${CTL_FILE_FIX1}

>${FIELDS_TMP}

rm -f ${LOG}

rm -f ${BADLOG}

}

clear_tmpfile() {

rm -f ${CTL_HEAD_FILE}

rm -f ${INFILE_LIST}

rm -f ${CTL_FILE_FIX1}

rm -f ${FIELDS_TMP}

}

sqlldr_cmd() {

echo "sqlldr userid=${loghwods} control=${CONTROL_FILE} log=${LOG} bad=${BADLOG} ${LOADS} errors=10000000 bindsize=200000 silent=HEADER direct=TRUE parallel=TRUE readsize=20000000 external_table=NOT_USED columnarrayrows=20000 streamsize=20000000" > ${SQLLDR_CMD}

}

chmod_files() {

chmod +x ${CONTROL_FILE}

chmod +x ${SQLLDR_CMD}

}

if [ $# -ne 2 ]

then

echo "参数:[绝对路径][日期]"

exit -1;

fi

FDIR=$1

NOW=$2

CONTROL_FILE=${HOME}/ctl/${INTCODE}.ctl

CTL_HEAD_FILE=/tmp/${TABNAME}_$$.hd

INFILE_LIST=/tmp/${TABNAME}_$$.inf

CTL_FILE_FIX1=/tmp/${TABNAME}_$$.fx1

FIELDS_TMP=/tmp/${TABNAME}_$$.fld

SQLLDR_CMD=${HOME}/sload/${INTCODE}.sld

LOG=${logdir}/sload/${INTCODE}${NOW}.log

BADLOG=${logdir}/sload/bad/${INTCODE}${NOW}bad.log

# LOADS="load=500"

init_tmpfile;

sql_clean;

RETCODE=$?

##

# 强化检查

if [ ${RETCODE} -ne 0 ]

then

echo "错误A"

exit 20;

fi

control_head;

infile_list ${TABNAME} ${FDIR} ${NOW}

control_fix1;

control_fields;

combine_files;

clear_tmpfile;

sqlldr_cmd;

cat ${CONTROL_FILE}

cat ${SQLLDR_CMD}

chmod_files;

sql_clean;

timex ${SQLLDR_CMD}

if [ ! -f ${LOG} ]

then

echo "${LOG}没找到";

exit -1;

fi

cp ${LOG} /tmp/${INTCODE}.log

if grep "successfully" ${LOG}

then

LOGNUM=`grep "successfully" ${LOG}|awk '{print $1}'`;

else

LOGNUM=0

fi

if [ -f ${BADLOG} ]

then

BADNUM=`wc -w ${BADLOG}|awk '{print $1}'`;

else

BADNUM=0;

fi

if [ ${LOGNUM} -eq 0 -o ${LOGNUM} -le ${BADNUM} ]

then

exit 20; #装载失败

fi