sqlldr with DSExecute

[admin]

Its important to note that sqlldr only ever passes back a successful return code. Whether the load is successful or not. I suggest you call a script that calls sqlldr and then greps the log file
for errors.
The script can then pass back an appropriate return code.

Try something like:


#!/bin/sh
# Author: Altis Consulting
# Date Written: April, 2001
# Description:
# - execute sqlldr
# - check for successfull execution
# - check log file for any errors as sqlldr returns success
# even on a failed load.
# - return 0 if no errors, else 1
#
# Modification Log
# ===================================================================
#

#------------------------------------------------------------------------------
# define USAGE for script
USAGE="Usage:nt$0 nn"
#------------------------------------------------------------------------------
#
#------------------------------------------------------------------------------
# check parameter count for correct usage
#------------------------------------------------------------------------------

case "$#" in
4) ;;
*) echo $USAGE
exit 1
esac

#------------------------------------------------------------------------------
# set parameters (and do any further checks required)
#------------------------------------------------------------------------------

ROOT_DIR=/taurusftp
DATABASE="$1"
TABLE="$2"
CONTROL_FILE="$ROOT_DIR/ctl/$TABLE.ctl"
DATA_FILE="$ROOT_DIR/data/$TABLE.dat"
USER="$3"
PASS="$4"

#------------------------------------------------------------------------------
# set constants (and do any further checks required)
#------------------------------------------------------------------------------

JOB_LOG=${ROOT_DIR}/log/AQsqlldr.log

# OPTIONS="log=$ROOT_DIR/log/$TABLE.log bad=$ROOT_DIR/bad/$TABLE.bad direct=true errors=0 rows=50000 silent=all" OPTIONS="log=$ROOT_DIR/log/$TABLE.log bad=$ROOT_DIR/bad/$TABLE.bad direct=true errors=0 rows=50000"

PATH=/taurusftp/script:$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/ccs/bin:/usr/ucb:/opt/bin:/opt/local/bin:usr/local/gnu/bin:.

ORACLE_HOME=/db00/app/oracle/product/8.1.6

PATH=$PATH:$ORACLE_HOME/bin

ORACLE_SID=$DATABASE
export ORACLE_SID
ORAENV_ASK=NO
export ORAENV_ASK
. oraenv

TEMP=/tmp
export TEMP

#------------------------------------------------------------------------------
# check for the existence of the root directory and the control file
#------------------------------------------------------------------------------

cd "${ROOT_DIR}"
if [ $? -ne 0 ] ; then
echo "ERROR: Could not cd to "${ROOT_DIR}""
exit 1
fi

if [ ! -f "$CONTROL_FILE" ] ; then
echo "ERROR: control file "$CONTROL_FILE" not found"
exit 1
fi

#------------------------------------------------------------------------------
# execute sqlldr
#------------------------------------------------------------------------------

echo `date + %Y/%m/%d %H:%M:%S` $TABLE Start >> $JOB_LOG

sqlldr $USER/$PASS control=$CONTROL_FILE data=$DATA_FILE $OPTIONS if [ $? -ne 0 ] ; then
echo "ERROR: Execution of sqlldr failed."
echo `date + %Y/%m/%d %H:%M:%S` $TABLE ERROR >> $JOB_LOG
exit 1
fi

#------------------------------------------------------------------------------
# check log file
#------------------------------------------------------------------------------

LOG_FILE="$ROOT_DIR/log/$TABLE.log"
grep ORA- "$LOG_FILE"
if [ $? -eq 0 ] ; then
echo "ERROR: Errors found in sqlldr log file, "$LOG_FILE""
echo `date + %Y/%m/%d %H:%M:%S` $TABLE ERROR >> $JOB_LOG
exit 1
fi
grep "Rows successfully loaded" "$LOG_FILE"

#------------------------------------------------------------------------------
# indicate successfull completion
#------------------------------------------------------------------------------
echo "SUCCESSc"
echo `date + %Y/%m/%d %H:%M:%S` $TABLE Loaded >> $JOB_LOG exit 0