checking sqlldr for data errors and aborting script.

So even if a single error comes during sqlldr may it be data error, when clause failed or null fields sqlldr will return $? = 1

if $? is not 0, then the previous command failed.

if [[ $? != 0 ]];then echo Failed;else echo Success;fi

Select allOpen in new window

There is an option in SQL*Loader to specify the termination of the load process when 'n' no. of errors have occured. If u set  this n=1, then the load will terminate at the first error itself. However, note that the processed records will already be COMMITed in the target table before termination (i think even this can be controlled though). Post ur control file and we can help u more.

LOAD DATA
APPEND
INTO TABLE abc
(
col1 position (01:16),
col2 position (17:1082)
)

LOAD DATA
APPEND
INTO TABLE def(
col1 position (01:02),
col2 position (03:11),
col3 position (12:1390)
)

I have the following script.

This script is loading data into table; checking if loading succeded.
and also checking for duplicates.

The problems is that some echo statements are not logged. Those statements are marked by me.

This is because $callhist_result is null. I really do not understand why those statements are not logged


The statements are:
if [ $callhist_result -eq '0' ]; then
      echo 'duplicates exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log --Not logged
else
      echo 'duplicates do not exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log  --Not logged
fi

if [ $lothist_result -eq '0' ]; then
      echo 'duplicates exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log --Not logged
else
      echo 'duplicates do not exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log --Not logged
fi


*****************************************************************************************************
#!/bin/ksh
 set -x # Uncomment to debug this script
# set -n # Uncomment to check syntax without any execution


db_login=$1
cur_dir=$2
spool_dir=$3
callhist_data=$4
lothist_data=$5

echo "Loading abc..." > $spool_dir/sqlldr.log

sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/loadcallhist.ctl DATA=$cur_dir/$4 LOG=$cur_dir/CALLHIST_LOAD.log BAD=$cur_dir/CALLHIST.bad DISCARD=$cur_dir/CALLHIST.dsc >/dev/null

echo "Checking for data loaded into abc..." >> $spool_dir/sqlldr.log

if [[ $? != 0 ]]; then
      echo 'Error during loading data into abc, aborting script..' >> $spool_dir/sqlldr.log
      exit 1
else
      echo 'All rows successfully loaded into table abc..' >> $spool_dir/sqlldr.log
fi

echo "loading def..." >> $spool_dir/sqlldr.log

sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/loadlothist.ctl DATA=$cur_dir/$5 LOG=$cur_dir/LOTHIST_LOAD.log BAD=$cur_dir/LOTHIST.bad DISCARD=$cur_dir/LOTHIST.dsc  >/dev/null

echo "Checking for data loaded into def..." >> $spool_dir/sqlldr.log

if [[ $? != 0 ]]; then
      echo 'Error during loading data into def, aborting script..' >> $spool_dir/sqlldr.log
      exit 1
else
      echo 'All rows successfully loaded into table def..' >> $spool_dir/sqlldr.log
fi

echo "checking for duplicates..." >> $spool_dir/sqlldr.log

sqlplus -s << EOF >> /dev/null
$db_login

spool $spool_dir/callhist_dups.log

SELECT starter_control_number,count(1)
FROM abc
WHERE starter_control_number IS NOT NULL
GROUP BY starter_control_number
HAVING count(1)>1;

spool off;

spool $spool_dir/lothist_dups.log

SELECT call_transaction_id,count(1)
FROM def
GROUP BY call_transaction_id
HAVING count(1)>1;

spool off;
EOF

callhist_result=`grep -c 'no rows selected' callhist_dups.log`
lothist_result=`grep -c 'no rows selected' lothist_dups.log`

if [ $callhist_result -eq '0' ]; then
      echo 'duplicates exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log --Not logged
else
      echo 'duplicates do not exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log  --Not logged
fi

if [ $lothist_result -eq '0' ]; then
      echo 'duplicates exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log --Not logged
else
      echo 'duplicates do not exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log --Not logged
fi

johnsone:
sqlldr has given an error but it still returned 0 So the script succedes.

+ sqlldr user/pass@dblink CONTROL=abc.ctl DATA=abc.dat LOG=abc.log BAD=abc.bad DISCARD=abc.dsc
+ echo Checking for data loaded into abc...
+ [[ 0 != 0 ]]
run1.ksh: [[: not found
+ echo All rows successfully loaded into table abc..


Table abc:
  0 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

I think a better way is to check the log file for errors.

grep -E or grep -x is not recogonized by my shell

Here are the details for my shell
iaprod@moppgpiaisst1/export/home/iaprod/aman> echo $SHELL
/bin/ksh
iaprod@moppgpiaisst1/export/home/iaprod/aman> uname -a
SunOS moppgpiaisst1 5.8 Generic_117350-46 sun4u sparc SUNW,Sun-Fire-15000


+ cat /export/home/iaprod/aman/LOTHIST_LOAD.log
grep: illegal option -- E or
grep: illegal option -- x

Usage: grep -hblcnsviw pattern file . . .
+ [[ !=
run.ksh: [[: not found


if [[ `cat $cur_dir/LOTHIST_LOAD.log | grep -E " 0 Rows not loaded due to data errors."` != "" && `cat $cur_dir/LOTHIST_LOAD.log | grep -E " 0 Rows not loaded because all WHEN clauses were failed."` != "" && `cat $cur_dir/LOTHIST_LOAD.log | grep -E " 0 Rows not loaded because all fields were null."` != "" ]];then
      echo 'All rows successfully loaded into table TMP_TK_PDMA_LOT_HIST..' >> $spool_dir/sqlldr.log
else
      echo 'Error during loading data into TMP_TK_PDMA_LOT_HIST, aborting script..' >> $spool_dir/sqlldr.log
      exit 1
fi;

I guess  by using grep -x you want to user regular expression serarch? this sort of a thing is not in my shell

I see a potential error in ur script (I might be wrong). All ur IF conditions are followed by the ; which means it would do nothing if the condition is true. Also ur checking the result against '0' which is different than checking against 0. Try this.

if [ $callhist_result -eq 0 ] then
      echo 'duplicates exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log --Not logged
else
      echo 'duplicates do not exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log  --Not logged
fi

if [ $lothist_result -eq 0 ] then
      echo 'duplicates exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log --Not logged
else
      echo 'duplicates do not exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log --Not logged
fi

Your problem is that there is an echo statement between the sqlldr command and checking the return status.  You are actually checking the return status of the echo command.  You need to move your errror checking or save the return status of the sqlldr command before doing the echo statement.

For example:

sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/loadcallhist.ctl DATA=$cur_dir/$4 LOG=$cur_dir/CALLHIST_LOAD.log BAD=$cur_dir/CALLHIST.bad DISCARD=$cur_dir/CALLHIST.dsc >/dev/null
 
sqlldr_status=$?
 
echo "Checking for data loaded into abc..." >> $spool_dir/sqlldr.log
 
if [[ $sqlldr_status != 0 ]]; then 
      echo 'Error during loading data into abc, aborting script..' >> $spool_dir/sqlldr.log
      exit 1
else 
      echo 'All rows successfully loaded into table abc..' >> $spool_dir/sqlldr.log
fi

Select allOpen in new window

jinesh, the ; is required before the then.  Shells actually want the then on a separate line and if you want to put it on the same line, the ; is required.

Now that I look at it, my post was horribly formatted.  It didn't look like that when I posted it.  This should look better.


sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/loadcallhist.ctl DATA=$cur_dir/$4 LOG=$cur_dir/CALLHIST_LOAD.log BAD=$cur_dir/CALLHIST.bad DISCARD=$cur_dir/CALLHIST.dsc >/dev/null

sqlldr_status=$?

echo "Checking for data loaded into abc..." >> $spool_dir/sqlldr.log

if [[ $sqlldr_status != 0 ]]; then
      echo 'Error during loading data into abc, aborting script..' >> $spool_dir/sqlldr.log
      exit 1
else
      echo 'All rows successfully loaded into table abc..' >> $spool_dir/sqlldr.log
fi

Experts, I have had a fresh look at the above program.

This program appends data into oracle table using sqlldr and then checks for duplicates.

The program is running ok without errors, Please let me know if any improvements are required.

#!/bin/ksh

# set -x # Uncomment to debug this script
# set -n # Uncomment to check syntax without any execution


db_login=$1
cur_dir=$2
spool_dir=$3
callhist_datafile=$4
lothist_datafile=$5

echo "loading abc..." >$spool_dir/sqlldr.log
sqlldr $db_login CONTROL=$cur_dir/loadcallhist.ctl DATA=$cur_dir/$callhist_datafile LOG=$cur_dir/CALLHIST_LOAD.log BAD=$cur_dir/CALLHIST.bad DISCARD=$cur_dir/CALLHIST.dsc >/dev/null
if [ $? -ne 0 ]; then
      echo "Error while loading data into abc table; aborting script" >>$spool_dir/sqlldr.log
      exit 1
else
      echo "Loading into abc table successful" >>$spool_dir/sqlldr.log
fi

echo "loading def..." >>$spool_dir/sqlldr.log

sqlldr $db_login CONTROL=$cur_dir/loadlothist.ctl DATA=$cur_dir/$lothist_datafile LOG=$cur_dir/LOTHIST_LOAD.log BAD=$cur_dir/LOTHIST.bad DISCARD=$cur_dir/LOTHIST.dsc  >/dev/null
if [ $? -ne 0 ]; then
      echo "Error while loading data into def table; aborting script" >>$spool_dir/sqlldr.log
      exit 1
else
      echo "Loading into def table successful" >>$spool_dir/sqlldr.log
fi

echo "checking for duplicates..." >>$spool_dir/sqlldr.log

cat /dev/null > $spool_dir/callhist_dups.log

sqlplus -s << EOF >> /dev/null
$db_login

spool $spool_dir/callhist_dups.log

#delete data more than 2 days old
delete from def where dt < sysdate -2;

delete from abc where dt < sysdate -2;

SELECT starter_control_number,count(1)
FROM abc
WHERE starter_control_number IS NOT NULL
GROUP BY starter_control_number
HAVING count(1)>1;

spool off;

spool $spool_dir/lothist_dups.log

SELECT call_transaction_id,count(1)
FROM def
GROUP BY call_transaction_id
HAVING count(1)>1;

spool off;
EOF

callhist_result=`grep -c 'no rows selected' callhist_dups.log`
lothist_result=`grep -c 'no rows selected' lothist_dups.log`

if [ $callhist_result -eq '0' ]; then
      echo 'duplicates exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log
else
      echo 'duplicates do not exist in CALLHISTORY table..' >> $spool_dir/callhist_dups.log
fi

if [ $lothist_result -eq '0' ]; then
      echo 'duplicates exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log
else
      echo 'duplicates do not exist in LOTHISTORY table..' >> $spool_dir/lothist_dups.log
fi

echo "################################################################################"  >>$spool_dir/lothist_dups.log
echo "Loader Logs--> $cur_dir/CALLHIST_LOAD.log and $cur_dir/LOTHIST_LOAD.log"           >>$spool_dir/lothist_dups.log
echo "Duplicacy Checks--> $spool_dir/callhist_dups.log and $spool_dir/lothist_dups.log"  >>$spool_dir/lothist_dups.log
echo "################################################################################"  >>$spool_dir/lothist_dups.log

db_login=$1
cur_dir=$2
spool_dir=$3
callhist_datafile=$4
lothist_datafile=$5

One modification I did was

if [ -z $db_login ]; then
 echo " pass parameter db_login "
 exit 1
fi

Glad u got it working. Make sure u test it with all possibilities (like bad data, duplicates, etc.) before moving it to a live env.

Thanks Jindesh, Johnsone and PeturingiEgilsson for your contributions.