script for SQL loader

[David Bell_1]

All,

The following code using a wildcard fails if no file exists. I'm hoping someone can have a look and help me fix this. As long as the file exists, the script works fine. If the file doesn't exist, it fails and aborts the process. We have tried piping it to NULL with no change. Any ideas?

Dave

# Date Created: 04/20/04
# Parameters:
# Data Directory - This is the UNIX folder where the data files will be loaded from.
# Archive Directory - This is the UNIX folder where archived files are created.
# Control File - Complete path to SQL*Loader Control file.
# Apps User - Applications Username.
# Apps Password - Applications Password.
#
#!/bin/sh
#echo 'Program Name :'$0
#echo 'username/pwd :' $1
#echo 'apps user id :' $2
#echo 'apps username:' $3
#echo 'request id :' $4
echo -------------------
echo 'Program arguments:'
echo -------------------
# your shellscript arguments start below :

v_timestamp=`date '+%Y%m%d%H%M%S'`
v_user_pwd=$1
v_data_dir=$5
v_archive_dir=$6
v_control_file=$7
v_file_mask=$8

echo "v_data_dir:" $v_data_dir
echo "v_archive_dir:" $v_archive_dir
echo "v_control_file:" $v_control_file
echo "v_file_mask:" $v_file_mask

cd $v_data_dir
for l_file in `ls -t $v_file_mask` - **This is the line that produces stderr output if there is no file matching the file mask 'MTL_CNV*'
do
echo $l_file
`/u02/oracle/prdora/8.0.6/bin/sqlload userid=$v_user_pwd control=$v_control_file data=$l_file log=$l_file.log bad=$l_file.bad`
echo 'SQL*Load complete'
if [[ -a $l_file.bad ]] then
echo "SQL*Loader produced a .bad file for $l_file"
else
for l_file_1 in `ls -t $l_file*`
do
mv $l_file_1 $v_archive_dir/$l_file_1
echo moved file $l_file_1 to $v_archive_dir
done
fi
done

[Nicolas Dumeige · Accepted solution]

Hello David,

If you want to exit greacfully before entering the loop, just test if there're file in the folder.

Something like :
ls $v_data_dir/$v_file_mask 1>/dev/null 2>&1
[ $? -ne 0 ] && exit 0

Is that want you wanted ?

Cheers

Nicolas

[Fred Ruffet]

...or something like :

Count=`ls $v_data_dir/$v_file_mask 2>/dev/null |wc -l`
[ $Count -ne 0 ] && exit 0

(I'm not sure that ls will return an error if no file in your mask.

Regards,

Fred

[David Bell_1]

Nicholas and Fred,

Thanks for the posts! Excuse my ignorance here but I need some help on the placement of your suggestions.

Would the location of your suggested changes go after the cd $v_data_dir and after the "for"?

I will award points after I have responses.

Thanks,

Dave

[Fred Ruffet]

before the for loop. It will exit wether no file exist and won't continue the script execution.

exit 0 may not be a good thing. You should maybe exit 1, so that unsuccessfull completion can be trapped by the script that launch this one... but a script is never achieved ;)

Regards,

Fred

[David Bell_1]

Fred and Nicholas,

Thanks for the help. It did indeed exit gracefully.

Fred,

I may mess around with the exit code 0 versus 1 but for now, it seems to complete okay. I appreciate the assistance.

Dave