checking sqlldr for data errors and aborting script.

I have a script that loads data from a flat file into an oracle table through sqlldr.

The log file of sqlldr contains summary on rows that were successfully loaded, and rejected rows due to:
data errors;
rejected data due to WHEN clause failed;
and rows not loaded as fields were null.

Table abc:
  25006 Rows successfully loaded.
  0 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 use the following statement to load the data into oracle table.
**************************************
#!/bin/ksh

echo "loading abc..."

sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/abc.ctl DATA=$cur_dir/abc.dat LOG=$cur_dir/abc.log BAD=$cur_dir/abc.bad DISCARD=$cur_dir/abc.dsc >/dev/null
**************************************
I want to check the log file for errors during data load.

If the log file contains the number of rows not loaded:

  25006 Rows successfully loaded.
  10 Rows not loaded due to data errors.
  3 Rows not loaded because all WHEN clauses were failed.
  1 Rows not loaded because all fields were null.

I need to abort the script.

Please help me this script.
gram77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
After runing the sqlldr command, check $?, the return status.  If it is not 0, then an error occurred during the load.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pétur Ingi EgilssonSoftware Engineer -- ConsultantCommented:
There is no way i know of to take action on a failure during a load.

You can check if the load was OK or not after the command has executed.

Make sure there is a "correct" ammount of whitespace (" ") in the string
"  0 Rows not loaded due to data errors." before the char '0'

I put one whitespace in there, as shown in:
  25006 Rows successfully loaded.
  10 Rows not loaded due to data errors.
  3 Rows not loaded because all WHEN clauses were failed.
  1 Rows not loaded because all fields were null.

Basicly the script checks if all lines have 0 Row errors.
#!/bin/ksh
 
echo "loading abc..."
 
sqlldr $db_login DIRECT=TRUE CONTROL=$cur_dir/abc.ctl DATA=$cur_dir/abc.dat LOG=$cur_dir/abc.log BAD=$cur_dir/abc.bad DISCARD=$cur_dir/abc.dsc >/dev/null
 
logFilePath = "/home/user/logfile.log";
 
echo -n "abc load: ";if [[ `cat $logFilePath | grep -x " 0 Rows not loaded due to data errors."` != "" && `cat $logFilePath | grep -x " 0 Rows not loaded because all WHEN clauses were failed."` != "" && `cat $logFilePath | grep -x " 0 Rows not loaded because all fields were null."` != "" ]];then echo "OK";else echo "FAILED";fi;

Open in new window

0
gram77Author Commented:
So even if a single error comes during sqlldr may it be data error, when clause failed or null fields sqlldr will return $? = 1
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Pétur Ingi EgilssonSoftware Engineer -- ConsultantCommented:
if $? is not 0, then the previous command failed.
if [[ $? != 0 ]];then echo Failed;else echo Success;fi

Open in new window

0
johnsoneSenior Oracle DBACommented:
Here is the meaning of the SQL*Loader exit codes:

All rows loaded successfully
 EX_SUCC
 
All or some rows rejected
 EX_WARN
 
All or some rows discarded
 EX_WARN
 
Discontinued load
 EX_WARN
 
Command-line or syntax errors
 EX_FAIL
 
Oracle errors nonrecoverable for SQL*Loader
 EX_FAIL
 
Operating system errors (such as file open/close and malloc)
 EX_FAIL
 

For UNIX, the exit codes are as follows:

EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL  3

It does not copy and paste well.  Here is the original doc:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm#1005020
0
Jinesh KamdarCommented:
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.
0
gram77Author Commented:
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)
)
0
gram77Author Commented:
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

0
gram77Author Commented:
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.

0
gram77Author Commented:
I think a better way is to check the log file for errors.
0
gram77Author Commented:
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;
0
gram77Author Commented:
I guess  by using grep -x you want to user regular expression serarch? this sort of a thing is not in my shell
0
Jinesh KamdarCommented:
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
0
johnsoneSenior Oracle DBACommented:
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

Open in new window

0
johnsoneSenior Oracle DBACommented:
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.
0
Jinesh KamdarCommented:
@johnsone: Ohh, I didn't know that, thx for the insight :)

@gram77: Try this.

OPTIONS (ERRORS=1)
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)
)
0
johnsoneSenior Oracle DBACommented:
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
0
gram77Author Commented:
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
0
gram77Author Commented:
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
0
Jinesh KamdarCommented:
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.
0
gram77Author Commented:
Thanks Jindesh, Johnsone and PeturingiEgilsson for your contributions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.