DATE=`date +%d%m%Y%H%M%S`
#v_data_file=${p_source_directory}/${p_source_file}
archieve_dir=${p_source_directory}/../archieve
#ls $p_source_directory/* >file_dir
for i in ${p_source_directory}/*
do
echo "the file is $i"
case "$i" in
*Nav*)
v_filename=${file##*/}
v_table_name="XXHYBRID_FORECAST_NAV";;
esac
echo "$v_file_name"
echo ${p_source_directory}
bad_file=${p_source_directory}/../log/${v_file_name}_${DATE}.bad
log_file=${p_source_directory}/../log/${v_file_name}_${DATE}.log
ctl_file=${p_source_directory}/${v_file_name}_${DATE}.ctl
v_seq_num=`sqlplus -s $p_login << EOF
set head off
select XXLOAD_TEMP_TBL_SEQ.nextval
from dual;
EOF`
echo $v_seq_num
echo "Creating Control File:$ctl_file"
cat > $ctl_file <<!
OPTIONS (errors=5000,SKIP=1)
LOAD DATA
CHARACTERSET ZHS16GBK
INFILE '$i'
REPLACE INTO TABLE '$v_table_name'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
plant "LTRIM(RTRIM(
lant))",
build_date "LTRIM(RTRIM(:build_date))",
reg "LTRIM(RTRIM(:reg))",
job "LTRIM(RTRIM(:job))",
order_num "LTRIM(RTRIM(
rder_num))",
qty "LTRIM(RTRIM(:qty))",
step "LTRIM(RTRIM(:step))",
customer "LTRIM(RTRIM(:customer))",
sfx "LTRIM(RTRIM(:sfx))",
mdl "LTRIM(RTRIM(:mdl))",
feature "LTRIM(RTRIM(:feature))",
cust_req "LTRIM(RTRIM(:cust_req))",
Orddte "LTRIM(RTRIM(:Orddte))",
acct "LTRIM(RTRIM(:acct))",
loading_date "to_char(SYSDATE,'YYYYMMDD')",
created_by "fnd_global.login_id",
creation_date "SYSDATE",
last_update_date "SYSDATE",
last_updated_by "fnd_global.login_id",
last_update_login "USERENV('SESSIONID')"
SCHEDULE_NUM "replace(:customer,' ','_')||'-'||to_char(SYSDATE,'MONYY')||'-'||$v_seq_num"
)
!
echo Created control file:$ctl_file
echo calling sql loader
sqlldr userid=$p_login control=$ctl_file data=$v_data_file log=$log_file bad=$bad_file
if [ $? -gt 2 ]
then
echo ERROR!! Fail to run sqlldr program. Check whether the data file $p_data_file exist.
exit 1
fi
echo '**********************************************************************'
echo '************************ Bad file ************************************'
cat $bad_file
echo '**********************************************************************'
echo '************************ Log file *******************************'
cat $log_file
#mv $v_data_file $archieve_dir
done
03-15-2010
