Dear Forum,

I'm trying to write a script that will check to see if a new file has been written to our server, and if so, execute a procedure in Oracle using sql*plus to upload the new file.

It's important that the script not execute if the file has already been uploaded. This might happen if the same file gets written to the directory. My thought was to check the log first to see if the file name already exists (has been written to the log before).

Please see below for what I've got. Let me know if there's anything I can clarify for you.

Thanks so much in advance,
Alice

#!/bin/ksh

# If not set, then:
# ORACLE_HOME=/oracle/app/oracle/product/9.2.0; export ORACLE_HOME

# --------------
# Database strings
# --------------

CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxx
OWNER=xxxxx
PROCEDURE=get_gfi_rail
LOGFILE=logs/gfiupload.log

# --------------
# Check gfiupload dir for new file
# --------------

#?? test to see if new file exists in
#?? /oracle/app/oracle/product/9.2.0/gfiupload
#?? File format is GMMDDYY, example: "G010205"
#??

FILE=
#?? Make sure to strip off the "G" in front!

# --------------
# Check for logs dir
# --------------

if [ ! -d logs ]; then
mkdir logs
fi

print "`date`: starting job..." >$LOGFILE

# --------------
# Sanity check before uploading data twice!
# --------------

#?? Add file name to the log
#?? Check to make sure file not already loaded (exists in log?)
#?? If already loaded, write to log, exit
#?? Else

# --------------
# execute the procedure
# --------------

print "Connecting to SQL*Plus..." >>$LOGFILE

/oracle/app/oracle/product/9.2.0/bin/sqlplus -s <<EOF $CONNECTION
set echo off termout off feedback off pages 0
exec $OWNER.$PROCEDURE('$FILE');
exit;
EOF

print "SQL*Plus connection closing..." >>$LOGFILE
print "`date`: done!" >>$LOGFILE

standard way to handle this classic problem is,
when sqlplus processing is complete:

mv thisfile thisfile.done
and, of course, make sure the script ignores files with done suffixes.

OR
mv thisfile DoneDir/

Hi Dlonra,

Thanks for the input. There is still a problem if another file with the same name as the first is writeen to the directory. Even though the old file is renamed something like "G010205.done", another file with "G010205" will be uploaded.

I'm still struggling with how to detect new files added to the directory (exploring some uses of the -newer command). Also need help with renaming the file (stripping off the preceding "G"), and checking to make sure another file of the same name has not yet been processed.

Thanks again, Dlonra. I'm trying to piece the script together by reading about its different parts on this forum. Any help you or anyone else can offer is greatly appreciated.

Alice

I would use exec (as below) to redirect all stdout and stderr to a log file. That single command eliminates redirecting on many individual commands, and it redirects ALL stdout and stderr, even the unexpected stuff.  And with all output redirected to the log file, you still have the option on individual commands to redirect to a separate location (including to the screen).

#!/bin/ksh

LOGFILE=logs/gfiupload.log

exec > $LOGFILE 2>&1

if [ ! "$ORACLE_HOME" ] ; then
   ORACLE_HOME=/oracle/app/oracle/product/9.2.0
   ORACLE_SID=PROD
   export ORACLE_HOME ORACLE_SID
   PATH=$PATH:$ORACLE_HOME/bin
fi

Also included above is the test if $ORACLE_HOME is set.  If ORACLE_HOME is not set, then ORACLE_SID also needs to be set, and PATH appended.

And regarding "Add file name to the log", I would not do that until AFTER successful processing of the file.  Your sqlplus needs to be sensitive to errors and exit with a failure code if errors encountered.  To demonstrate, execute the script below with and without the whenever sqlerror statement:

# !/bin/sh

sqlplus -s scott/tiger << EOF
whenever sqlerror exit failure
select count(*) from badtable;
exit
EOF

statcode=$?

if [ $statcode -ne 0 ] ; then
   echo 'SQLPlus failure!'
   exit $statcode
fi

echo 'Script is continuing on its merry way ...'

To log each successful (or unsuccessful) run, you can just append one line to a log file:

echo `date +%m%d%Y_%H:%M` $FILE $ROWCOUNT success >> $RUNLOG

Regarding the already-been-processed check, the script could check $RUNLOG as you suggested, or if the processed files are being relocated to some processed directory, the script could look for a duplicate file in there.

Report Offensive Follow Up For Removal

Jim, thanks alot for your help. I've gone ahead and incorporated your code into what I've got, and made a little headway with the whole "detect a new file" part. Please take a look if you can and let me know if you think this thing will run. One thing that it's got to do is fall back asleep if no new file is detected, AND after detecting and processing a new file, so that it might wake up and start the process all over again.

Thanks so much,
Alice

#!/bin/ksh

------
# log setup
------

LOGFILE=logs/gfiupload.log
exec > $LOGFILE 2>&1

if [ ! -d logs ]; then
mkdir logs
fi

------
# check database
------

if [ ! "$ORACLE_HOME" ] ; then
ORACLE_HOME=/oracle/app/oracle/product/9.2.0
ORACLE_SID=PROD
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
fi

------
# db connect strings
------

CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxx
OWNER=xxxxx
PROCEDURE=get_gfi_rail

------
# check for new file
------

touch newtime
sleep 300

NEW=`find . -type f -name "G*" -newer newtime`

if [ -z $NEW ]; then
exit 1

else
FILE=`echo $NEW | sed -e 's/G//g'`
mv newtime oldtime
fi

------
# sql*plus
------

echo "`date +%m%d%Y_%H:%M`: starting job..." >$LOGFILE
echo "Connecting to SQL*Plus..." >>$LOGFILE

$ORACLE_HOME/sqlplus -s <<EOF $CONNECTION
whenever sqlerror exit failure
exec $OWNER.$PROCEDURE('$FILE');

exit
EOF

statcode=$?

if [ $statcode -ne 0 ] ; then
echo "SQLPlus failure!" >>$LOGFILE
exit $statcode
fi

echo "Script is executing..." >>$LOGFILE
echo $FILE $ROWCOUNT success >>$LOGFILE
echo "SQL*Plus connection closing..." >>$LOGFILE
echo "`date +%m%d%Y_%H:%M`: Done!" >>$LOGFILE

For repetitive executions, you need to choose either to go with cron or to make your script a sleeper script.

You can schedule it in cron to run as frequently as every minute.

A sleeper script would not be scheduled in cron. Rather, it would be launched just once manually (and maybe automatically at system boot-up), and it would wake up from a sleep command every n minutes and execute its code. This is done with a simple while-loop.

cron scheduling is safer. If a sleeper script aborts or gets killed, it will not run again until someone realizes that it is not running, and someone launches it again (or maybe by a system reboot).

I prefer cron, but when I want the work cycle to occur very frequently, such as every 5 seconds, I will use a sleeper script (or a combination of cron and sleeper works well, and is safer).

Regarding LOGFILE, if you use that exec command, you do not want to redirect all of your output to $LOGFILE. That is what the exec command does, so remove all references to $LOGFILE below the exec command.

I'm guessing that $PROCEDURE is expecting a single filename in the $FILE parameter. You are loading $FILE with the results of a find command that could be multiple filenames. You will need a for-loop here to process each qualifying filename one by one.

Also, your sed command is removing all occurences of G. This should be OK due to your filename format of G010205, but when we get that for-loop in place, we will remove just the leading G.

As you process the files, you want them to remain where they are after processing? And there is a chance that the exact same file will get dumped into this directory repeatedly - thus the need to verify filenames that have already been processed successfully?

Thanks Jim, I do need to move the files after processing them, to a directory called "done," was hoping to get the script working before tackling that issue (also the verification part next time the script wakes up). Any help in that direction is sure welcome.

Here's the script so far, the 'sleeper' part doesn't seem to work (I've got it set to 10 for debugging). Can you see what's wrong with it?

The frequency with which files are written to the directory is low, maybe a couple of times per day. I think checking every 15 minutes or so for a new file will suffice (a combination of cron and sleep should do it...).

Thanks again, Jim, for your help and for explaining things to me.

Alice

#!/bin/ksh

# ------
# log setup
# ------

SCRIPTPATH=`whence $0` >/dev/null 2>&1
SCRIPTHOME=`dirname $SCRIPTPATH
LOGFILE=$SCRIPTHOME/logs/gfiupload.log
exec > $LOGFILE 2>&1

if [ ! -d $SCRIPTHOME/logs ]; then
mkdir $SCRIPTHOME/logs
fi

# ------
# check database
# ------

if [ ! "$ORACLE_HOME" ] ; then
ORACLE_HOME=/oracle/app/oracle/product/9.2.0
ORACLE_SID=PROD
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
fi

# ------
# db connect strings
# ------

CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxx
OWNER=xxxxx
PROCEDURE=get_gfi_rail

# ------
# check for new file
# ------

NEW=`find . -type f -name "G*" -newer newtime`

while true; do
if
[ -z $NEW ]; then
echo "bombing..."
touch newtime
sleep 10

else
FILE=`echo $NEW | sed -e 's/G//g'`
echo `date +%m%d%Y_%H:%M`: New file found: $FILE
mv newtime oldtime
touch newtime
fi
sleep 10
done

# ------
# sql*plus
# ------

echo "Starting job..."
echo "Connecting to SQL*Plus..."

$ORACLE_HOME/sqlplus -s <<EOF $CONNECTION
whenever sqlerror exit failure
exec $OWNER.$PROCEDURE('$FILE');

exit
EOF

statcode=$?

if [ $statcode -ne 0 ] ; then
echo "SQLPlus failure!"
exit $statcode
fi

echo "Script is executing..."
echo $FILE $ROWCOUNT success
echo "SQL*Plus connection closing..."
echo "`date +%m%d%Y_%H:%M`: Done!"

For every 15 min, I would just put it in cron (no sleep loop).  Before putting in cron, do your testing by running it manually (a single execution each time you run it).

# min       hour day mon dow command
00,15,30,45 *    *   *   *   /home/jobs/myscript.sh

But your problem was that the find command (and sqlplus) needed to be inside the while-loop.

I wanted to know about moving files out because that impacts how you need to find files.  Since you say that we can move files out, I will assume that we can use this logic:

Each time the script is ran (manually or by cron), it will process all files in the target directory beginning with a G, and upon completion of processing, each file will be relocated to either a success directory or an error directory.

If that is a valid approach, then I see no need for -newer logic.

<your environment set-up stuff>
SUCCESSLOG=<full/path/reference>
SUCCESSDIR=<full/path/reference>
ERRORLOG=<full/path/reference>
ERRORDIR=<full/path/reference>

NOW=$(date "+%Y%m%d_%H:%M")

for FILE in $(find ...)
do

if grep -q $FILE $SUCCESSLOG ; then
   echo $NOW $FILE duplicate >> $ERRORLOG
   mv $FILE $ERRORDIR/${FILE}_$NOW
   continue
fi

FILE2=${FILE#G}

< sqlplus processing of $FILE2 >

if [ $statcode -eq 0 ] ; then
   echo $NOW $FILE >> $SUCCESSLOG
   mv $FILE $SUCCESSDIR
else
   echo $NOW $FILE sqlerror >> $ERRORLOG
   mv $FILE $ERRORDIR/${FILE}_$NOW
fi
done

The script will process all G files in the directory, with
each file being relocated to one directory or another.  A file in error has to be relocated also because leaving it where it is would result in the script continuing to process it (and find it in error again) on each run.  The error files need to be monitored and handled as appropriate.  The script could send an email for each file processed as an error.

Make sure that your find command searches the desired directory tree. find . will search the current directory, and that would be the current directory of the person running the script (not the directory where the script resides).

The moves to $ERRORDIR could overlay files already there with the same filename, so I avoid that by tacking a datestamp onto the filename.  In fact, you might want to consider doing that on the move to $SUCCESSDIR.

Your SCRIPTHOME= command needs terminating with a backquote.

Report Offensive Follow Up For Removal

Jim, it keeps bailing at the loop with this error message:

./gfiupload.sh[38]: syntax error at line 38 : `for' unmatched

What do you think?
Alice

...

NOW=$(date "+%Y%m%d_%H:%M")

for FILE in $(find . -type f -name "G*")
do

if grep -q $FILE $SUCCESSLOG ; then
echo $NOW $FILE duplicate >> $ERRORLOG
mv $FILE $ERRORDIR/${FILE}_$NOW
continue
fi

FILE2=${FILE#G}

The for-statement needs to be followed by a do-done paragraph. I cannot see the remainder of the script, but it obviously cannot see the "done" statement.

If it is actually there, then you may have an unmatched quote that is confusing it.

Jim, thanks again, I wish I had figured that out myself... I'm not able to test the sql*plus part just yet, but everything else is working fine. I'll report back to you on the results and hopefully post a full working script.

By the way, I had to pipe the find command through "cut" in order to lop off the "./" before the file name. Could that be a platform-related thing?

Thank you so much,
Alice

Here's the latest:

#!/bin/ksh

# -------
# setup environment, logs
# -------

SCRIPT_PATH=`whence $0` >/dev/null 2>&1
SCRIPT_HOME=`dirname $SCRIPT_PATH`

if [ ! -d $SCRIPT_HOME/logs ]; then
mkdir $SCRIPT_HOME/logs
fi

if [ ! -d $SCRIPT_HOME/success ]; then
mkdir $SCRIPT_HOME/success
fi

if [ ! -d $SCRIPT_HOME/error ]; then
mkdir $SCRIPT_HOME/error
fi

LOGFILE=$SCRIPT_HOME/logs/gfi_upload.log
exec > $LOGFILE 2>&1

SUCCESS_LOG=$SCRIPT_HOME/logs/gfi_success.log
SUCCESS_DIR=$SCRIPT_HOME/success

ERROR_LOG=$SCRIPT_HOME/logs/gfi_error.log
ERROR_DIR=$SCRIPT_HOME/error

# -------
# process each new file in $SCRIPT_HOME
# move duplicate files (listed in $SUCCESS_LOG) to $ERROR_DIR
# remove "G" from qualifying files
# -------

NOW=$(date "+%Y%m%d_%H:%M")

for FILE in $(find . -type f -name "G*" | cut -c3-)
do

if grep $FILE $SUCCESS_LOG ; then
echo $NOW $FILE duplicate >> $ERROR_LOG
mv $FILE $ERROR_DIR/${FILE}_$NOW
continue
fi

FILE2=`echo $FILE | sed -e 's/G//g'`

done # test

# -------
# check $ORACLE_HOME, set db connect strings
# -------

if [ ! "$ORACLE_HOME" ] ; then
ORACLE_HOME=/oracle/app/oracle/product/9.2.0
ORACLE_SID=PROD
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
fi

CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxxx
OWNER=xxxxx
PROCEDURE=get_gfi_rail

# -------
# run sql*plus procedure for each qualifying file
# -------

echo "Starting job..."
echo "Connecting to SQL*Plus..."

$ORACLE_HOME/sqlplus -s <<EOF $CONNECTION
whenever sqlerror exit failure
exec $OWNER.$PROCEDURE('$FILE2');

exit
EOF

# -------
# move successfully uploaded files to $SUCCESS_DIR
# move failed files to $ERROR_DIR
# update logs
# -------

statcode=$?

if [ $statcode -eq 0 ] ; then
echo $NOW $FILE >> $SUCCESS_LOG
mv $FILE $SUCCESS_DIR/${FILE}_$NOW
else
echo $NOW $FILE sqlerror >> $ERROR_LOG
mv $FILE $ERROR_DIR/${FILE}_$NOW
fi
done

echo $FILE $ROWCOUNT success
echo "SQL*Plus connection closing..."
echo "`date +%m%d%Y_%H:%M`: Done!"

I haven't studied your latest post, but that leading "./" is normal. But you can avoid piping into cut and also avoid the call to sed to remove the G. Instead, use the following:

FILE2=${FILE##*/G}

This is a slight variation from my earlier posting that also takes care of the "./". It removes everything from the beginning of $FILE through the final /G. For example:

FILE=./mydir/Good/G021405
FILE2=${FILE##*/G}
echo $FILE2
021405

This is a built-in ksh function and extremely fast.

Report Offensive Follow Up For Removal

Jim,

Thanks a million for continuing to help me out. I'm noticing some strange behavior in the logs, hopefully you can spot and tell me why...

First off, the $FILE2 parameter which gets inserted into the Oracle procedure has the file name with date and time appended. I can't figure out why. I'm working on that one right now.

From gfi_error.log:

Starting job...
Connecting to SQL*Plus...
BEGIN APK.APK_TEST('102030_20050216_08:22');

Next, under statcode=$?, where the script should execute "mv $FILE $ERROR_DIR/${FILE}_$NOW", it's actually attempting:

mv: cannot rename error/G102030_20050216_08:25 to /export/home/apk/script/gfi/./error/error/G102030_20050216_08:25_20050217_14:13: No such file or directory

Instead it should be moving G102030 to /export/home/apk/script/gfi/error/G102030_20050216_08:25.

I keep going back to $ERROR_DIR to make sure it's set right, but there doesn't seem to be a problem.

I'm going to post the script again (with your latest edits), hopefully I'm not making this thread too long and that it's of interest to other people as well...

Alice

#!/bin/ksh

# -------
# setup environment, logs
# -------

SCRIPT_PATH=`whence $0` >/dev/null 2>&1
SCRIPT_HOME=`dirname $SCRIPT_PATH`

if [ ! -d $SCRIPT_HOME/logs ]; then
mkdir $SCRIPT_HOME/logs
fi

if [ ! -d $SCRIPT_HOME/success ]; then
mkdir $SCRIPT_HOME/success
fi

if [ ! -d $SCRIPT_HOME/error ]; then
mkdir $SCRIPT_HOME/error
fi

LOGFILE=$SCRIPT_HOME/logs/gfi_upload.log
exec > $LOGFILE 2>&1

SUCCESS_LOG=$SCRIPT_HOME/logs/gfi_success.log
SUCCESS_DIR=$SCRIPT_HOME/success

ERROR_LOG=$SCRIPT_HOME/logs/gfi_error.log
ERROR_DIR=$SCRIPT_HOME/error

# -------
# process each new file in $SCRIPT_HOME
# move duplicate files (listed in $SUCCESS_LOG) to $ERROR_DIR
# remove "G" from qualifying files
# -------

NOW=$(date "+%Y%m%d_%H:%M")

for FILE in $(find . -type f -name "G*" | cut -c3-)
do

if grep $FILE $SUCCESS_LOG ; then
echo $NOW $FILE duplicate >> $ERROR_LOG
mv $FILE $ERROR_DIR/${FILE}_$NOW
continue
fi

FILE2=${FILE##*/G}

# -------
# check $ORACLE_HOME, set db connect strings
# -------

if [ ! "$ORACLE_HOME" ] ; then
ORACLE_HOME=/oracle/app/oracle/product/9.2.0
ORACLE_SID=PROD
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
fi

CONNECTION=xxxxxxxxx/xxxxxxx@xxxxx
OWNER=xxxxx
# PROCEDURE=get_gfi_rail
PROCEDURE=APK_TEST

# -------
# run sql*plus procedure for each qualifying file
# -------

echo "Starting job..."
echo "Connecting to SQL*Plus..."

$ORACLE_HOME/bin/sqlplus -s <<EOF $CONNECTION
whenever sqlerror exit failure
exec $OWNER.$PROCEDURE('$FILE2');

exit
EOF

# -------
# move successfully uploaded files to $SUCCESS_DIR
# move failed files to $ERROR_DIR
# update logs
# -------

statcode=$?

if [ $statcode -eq 0 ] ; then
echo $NOW $FILE >> $SUCCESS_LOG
mv $FILE $SUCCESS_DIR/${FILE}_$NOW
else
echo $NOW $FILE sqlerror >> $ERROR_LOG
mv $FILE $ERROR_DIR/${FILE}_$NOW
fi

echo $FILE2 $ROWCOUNT success
echo "SQL*Plus connection closing..."
echo "`date +%m%d%Y_%H:%M`: Done!"

done

Ok, there must be a problem with my loop somewhere, since the first $FILE2 inserted into the proc is correct, but each one following is wrong...

from the gfi_upload.log:

Starting job...
Connecting to SQL*Plus...
BEGIN APK.APK_TEST('G494949'); END;

...

G494949 success
SQL*Plus connection closing...
02172005_15:34: Done!
Starting job...
Connecting to SQL*Plus...
BEGIN APK.APK_TEST('102030_20050216_08:22'); END;

Ok, there's another problem, the "G" is not supposed to be there. I promise no more posts until I am thinking clearly...

Alice

Regarding the G that is still there, you need to remove cut -c3-. The new construct of FILE2=${FILE##*/G} needs to find /G otherwise it will strip nothing.

Regarding the weirdness of the datestamp being there, it looks like your relocation directories $SUCCESSDIR and $ERRORDIR are in the same directory tree in which the find command looks for files. The find command by default is recursive, searching subdirectories as well, so it is finding files that have already been processed and moved/renamed into those directories.

Move the error and success directories somewhere else. Or another option is to tune the find command. These options are not available on HP-UX, but they may be available to you. Do "man find" and look at -prune (to say do not look in error or success directories) or better yet, -level option to keep it at level 1 (or is it level 0?).

Jim, woo-hoo! thanks so much! Aside from a few tweaks on the Oracle side, all appears to be working nicely. I couldn't have done any of this without your help and guidance...

Thanks again,
Alice

...

#!/bin/ksh

# -------
# Title : MOVE GFI DATA TO ORACLE
#
# Description : Each time this script is run (either manually or by cron),
# it will process all files in the target directory
# beginning with a "G", and upon completion of processing,
# relocate each file to either a success directory
# or an error directory.
#
# Instructions : Schedule in cron as follows:
# 00,15,30,45 * * * * /path/to/gfi_upload.sh
# -------

# -------
# setup environment, logs
# -------

SCRIPT_PATH=`whence $0` >/dev/null 2>&1
SCRIPT_HOME=`dirname $SCRIPT_PATH`

if [ ! -d $SCRIPT_HOME/logs ]; then
mkdir $SCRIPT_HOME/logs
fi

if [ ! -d $SCRIPT_HOME/success ]; then
mkdir $SCRIPT_HOME/success
fi

if [ ! -d $SCRIPT_HOME/error ]; then
mkdir $SCRIPT_HOME/error
fi

LOGFILE=$SCRIPT_HOME/logs/gfi_upload.log
exec > $LOGFILE 2>&1

SUCCESS_LOG=$SCRIPT_HOME/logs/gfi_success.log
SUCCESS_DIR=$SCRIPT_HOME/success

ERROR_LOG=$SCRIPT_HOME/logs/gfi_error.log
ERROR_DIR=$SCRIPT_HOME/error

# -------
# process each new file in $SCRIPT_HOME
# move duplicate files (listed in $SUCCESS_LOG) to $ERROR_DIR
# remove "G" from qualifying files
# -------

NOW=$(date "+%Y%m%d_%H:%M")

for FILE in $(find . \( -name success -prune \) -o \( -name error -prune \) -o -name "G*" -print)
do

if grep $FILE $SUCCESS_LOG ; then
echo $NOW $FILE duplicate >> $ERROR_LOG
mv $FILE $ERROR_DIR/${FILE}_$NOW
continue
fi

FILE2=${FILE##*/G}

# -------
# check $ORACLE_HOME, set db connect strings
# -------

if [ ! "$ORACLE_HOME" ] ; then
ORACLE_HOME=/oracle/app/oracle/product/9.2.0
ORACLE_SID=PROD
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
fi

CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxxx
OWNER=xxxxx
PROCEDURE=get_gfi_rail

# -------
# run sql*plus procedure for each qualifying file
# -------

echo "Starting job..."
echo "Connecting to SQL*Plus..."

$ORACLE_HOME/bin/sqlplus -s <<EOF $CONNECTION
whenever sqlerror exit failure
exec $OWNER.$PROCEDURE('$FILE2');

echo $FILE2 $ROWCOUNT success

exit
EOF

# -------
# move successfully uploaded files to $SUCCESS_DIR
# move failed files to $ERROR_DIR
# update logs
# -------

statcode=$?

if [ $statcode -eq 0 ] ; then
echo $NOW $FILE >> $SUCCESS_LOG
mv $FILE $SUCCESS_DIR/${FILE}_$NOW
else
echo $NOW $FILE sqlerror >> $ERROR_LOG
mv $FILE $ERROR_DIR/${FILE}_$NOW
fi

echo "SQL*Plus connection closing..."
echo "`date +%m%d%Y_%H:%M`: Done!"
echo
echo

done

I really like to see those woo-hoo's.

Good job.