SQLLOAD應用舉例

SQLLOAD應用舉例[@more@]

sqlload_ctl.sh檔案內容如下:
. ${HOME}/.biconfig

TABNAME=tb_individual_bill

INTCODE=05111

#!/bin/sh

sql_clean() {

sqlplus -s ${loghwods}<set echo off;
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 echo off;
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