通过shell脚本进行数据上传

#---------------------------------------------------------------------
#  Program     : <PROGRAM NAME>.prog
#  Description   This program used to load data from excel sheet to
#                the respective table in Oracle database.
#   The input data file name should be filename.csv
#   The input file should be copied to the respective directory before running this program.
#  #edit www.jb200.com
#   This program uses the Host Language Concurrent Program execution
#   method.  This requires the program to be named <name>.prog and uses
#   a symbolic link to $FND_TOP/bin/fndcpesr to execute correctly.  Use
#   the following command to create the link:
#
#   ln -s $FND_TOP/bin/fndcpesr MCKLDMAP
#
#
#---------------------------------------------------------------------
#  CHANGES     :
#
#---------------------------------------------------------------------
#  Parameters  :
#
#  None
#---------------------------------------------------------------------
#                      FUNCTION DECLARATIONS
#---------------------------------------------------------------------
#  None
#---------------------------------------------------------------------
#                       VARIABLE DECLARATIONS
#---------------------------------------------------------------------
# Program definition must include the "encrypt" option
USER_PASS=<username>/<passwd>@<database name>
#USERID=${2}
#USERNAME=${3}
#REQID=${4}
#USERNAME_REQ=` echo $USERNAME | cut -b1-8`

INPUT_FILE=<file name> (ie. test.csv)
PROGRAM=<program name>
WORKDIR=/tmp
LOGDIR=/tmp
LOGFILE=$LOGDIR/$PROGRAM$DATE.$$_logfile
INDIR=<directory path> # directory where csv file is stored (say for eg.$MCK_DATA/in )
DATE=`date +%d%b%y`
DATE1=`date +%d-%b-%y`
INFILE=$INDIR/$INPUT_FILE
TABLE=<table_name>

#---------------------------------------------------------------------
#           RE-DIRECT STANDARD OUTPUT and STANDARD ERROR
#---------------------------------------------------------------------
# exec 1>>$LOGFILE
# exec 2>>$LOGFILE

#---------------------------------------------------------------------
#         BEGIN TO WRITE EXECUTION INFORMATION TO LOGFILE
#---------------------------------------------------------------------
echo "---------------------------------------------------------------"
echo "              Executing Program : $PROGRAM"
echo "                    Date & Time : `date`"
echo "---------------------------------------------------------------"
echo
#---------------------------------------------------------------------
#                 VERIFY THAT INPUT FILE EXISTS
#---------------------------------------------------------------------
if [ ! -f $INFILE ]
then
   echo
   echo "NO Data File $INFILE Found in Working Directory  !"
   echo "Program $PROGRAM completed UNSUCCESSFULLY at `date`."
   exit 1
fi
#---------------------------------------------------------------------
#                 CREATE SQLLOADER CONTROL FILE
#---------------------------------------------------------------------
cat<<+>$PROGRAM$$.ctl
LOAD DATA
REPLACE
INTO TABLE $TABLE
FIELDS TERMINATED BY ','
(
coulmn_1 char,
coulmn_2 char,
coulmn_3 char
.
.
.
.
.
)
+
#---------------------------------------------------------------------
#                          RUN SQLLOADER
#---------------------------------------------------------------------
echo
echo "Loading data into temporary table using SQL*Loader . . ."
sqlload userid=$USER_PASS control=$PROGRAM$$.ctl,
                          log=$PROGRAM$$.log,
                          bad=$PROGRAM$$.bad,
                          data=$INFILE
# remove ctl file
rm $PROGRAM$$.ctl

# verify that sqlloader process completed and that at least one
# record was successfully loaded
echo
echo "Verifying successful completion of SQL*Load . . ."
if [ ! -f $PROGRAM$$.log ]
then
   echo
   echo "SQL*Loader did not execute correctly!"
   echo "Program $PROGRAM completed UNSUCCESSFULLY at `date`."
   exit 1
fi

rows_one=`grep "Rows successfully loaded." $PROGRAM$$.log`
rows_two=`echo $rows_one | cut -d" " -f1`
if [ $rows_two = "0" ]
then
   echo
   echo "NO records were loaded from the data file!"
   echo "Program $PROGRAM completed UNSUCCESSFULLY at `date`."
   exit 1
fi

# archive the data file
# echo
# echo "Datafile $INFILE archived to $ARCDIR/$INFILE$DATE$$"
# mv $INDIR/$INFILE $ARCDIR/$INFILE$DATE$$
# display SQL*Loader logfile in program logfile
echo
echo "---------------------------------------------------------------"
echo "                 SQL*Loader Logfile Contents"
echo "---------------------------------------------------------------"
echo
cat $PROGRAM$$.log
echo
echo "---------------------------------------------------------------"
echo "                  End of SQL*Loader Logfile"
echo "---------------------------------------------------------------"

# remove SQL*Loader logfile
rm $PROGRAM$$.log

# display bad records, if they exist
if [ -f $PROGRAM$$.bad ]
then
   echo
   echo "ATTENTION : Bad Records Exist!!"
   echo "---------------------------------------------------------------"
   echo "                  Bad Records Found in Datafile"
   echo "---------------------------------------------------------------"
   echo
   cat $PROGRAM$$.bad
   echo
   echo "---------------------------------------------------------------"
   echo "                       End of Bad Records"
   echo "---------------------------------------------------------------"
   echo
   echo
   echo "Make sure all the records in your datafile are valid and no duplicate records exits to reload the clean file trucate table data first."
   echo "Program $PROGRAM completed UNSUCCESSFULLY at `date`."
   exit 1
   rm $PROGRAM$$.bad
fi
#---------------------------------------------------------------------
#                     RECORD END OF EXECUTION TIME
#---------------------------------------------------------------------
echo
echo "Program $PROGRAM completed successfully at `date`."
exit 0