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.

0

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}

0

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.

0

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!"

0

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.

0

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

0

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;

0

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

0

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?).

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

0

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

0