Script to rename, move then execute sql plus for upload

[TekiePrincess]

I am working on the following script. The original requirement was to just rename the files then move them to a seperate directory. I have the following as my base script and would like to get feedback on how I can make this better or any mistakes I may have overseen as a new shell scripter.

# Description : This script will rename and move files coming in from the 3PL to the appropriate directories then run the sql script to upload the data into the staging tables. Then run a concurrent program. Finally the files that were successful will be moved to an archive directory and those there weren't successful will be moved to an error directory.
# ========
# History
# ========
#
#**********************************************************************
# SDN file rename and move to appropriate directory for processing
#***Variables used throughout the shell script
#Set the environment
#ORACLE_HOME=$ORACLE_BASE/product/10.2.0
#CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxxx
#OWNER=xxxxx
#Script=xxxupload
#SCRIPT_PATH=`whence $0` >/dev/null 2>&1
#SCRIPT_HOME=`dirname $SCRIPT_PATH
#datadir="/u04/XXX/XXX_Interface/XXXFILES"
#archdir="path/archive"
#filename=${fn##*/}
#ORACLEID=xxxxx/xxxxxxx@xxxxx
#RespAppSN = SYSADMIN
#Respname='System Administrator'
#User_Name = XXXX
#[WAIT=N|Y|
#CONCURRENT = CONCURRENT
#ProgAppSN= FND
#ProgName= FNDSCURS
#PROGNAMDESC="Active Users"
#*********************************************************************
# Check Oracle Environment
#*********************************************************************
if [ -z "${ORACLE_HOME}" ]
then
echo ""
echo "ORACLE_HOME is not set."
echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo ""
echo "Can not find sqlplus."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi
#*********************************************************************
# rename and move each file in upload directory
# move duplicate files (listed in $SUCCESS_LOG) to $ERROR_DIR
# upon completion move files to the archive directory
#*********************************************************************

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

#cd $datadir
for f in *.txt; do
fn="${f%.*}"
l=$(sed -n '3 {s/UserFilename="//;s/.txt"$//;p;q;}' "$f")
case $l in
SHIP_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXXX/$l.dat";;
PICK_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXXX/$l.dat";;
SHIP_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXX/$l.dat";;
PICK_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXX/$l.dat";;
LOCATOR_*) mv "${fn##*/}.dat" "$datadir/LOCATOR/$l.dat";;
*) echo "error, unknown file type: $l" > "$ERROR_LOG
continue
fi

filename=$l


#*********************************************************************
# Run sql*plus upload script for each file
#*********************************************************************

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

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

echo $l $ROWCOUNT success

exit
EOF

#*********************************************************************
# Execute Concurrent Program for Oracle Applications
#*********************************************************************
# Concurrent Program will be called to upload the data into the staging tables
CONCSUB xxxxx/xxxxxxxx SYSADMIN 'System Administrator' xxx4 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
continue

#*********************************************************************
# move successfully uploaded files to $archdir
# 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

 

[Annihilannic]

Looks good to me. There are a couple of continue statements in there which don't appear to serve any purpose.

Please post your code in [ignore]

 ...

[/ignore] tags for readability.

Annihilannic.

 

[TekiePrincess]

Thank you Annihilannic for the feedback I will be sure to do that. I do have one question for my case statement do I need to end the case and if so where would that happen, in the loop or outside the loop?

 

[PHV]

...
*) echo "error, unknown file type: $l" > "$ERROR_LOG
continue
[!] esac[/!]
fi
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feherke]

Hi

*) echo "error, unknown file type: $l" > "$ERROR_LOG
continue
[red]esac[/red]
fi

Well, that was my first thought too.

But then came the second : "who's [tt]fi[/tt] is that ?".

Then the third : "the control structure immediately before [tt]case[/tt] is a [tt]for[/tt] loop, the next [tt]done[/tt] is the last word in the script - is that loop really that huge ?".

Beside that, I would say a closing double quote ( " ) is missing at the end of the line before the [tt]continue[/tt].

Or I am totally wrong ? So I gave up.

TekiePrincess, please indent your code consistently, so if you have syntax errors, at least we have a chance to guess your intentions.

Feherke.

http://free.rootshell.be/~feherke/

 

[TekiePrincess]

Annihilannic, PHV and feherke, Thank you all for the help. I have indented the code so that it is easier to read. To answer your question feherke, the loop I was trying to achieve would be from the start of the for loop until the completion of the execution of sql plus. I am wanting to rename, move and execute sql plus for each individual file that will be present then once that is complete. I will run the concurrent program outside of the loop then move the file to another directory if it is successful.

#ORACLE_HOME=$ORACLE_BASE/product/10.2.0
#CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxxx
#OWNER=xxxxx
#Script=xxxupload
#SCRIPT_PATH=`whence $0` >/dev/null 2>&1
#SCRIPT_HOME=`dirname $SCRIPT_PATH
#datadir="/u04/XXX/XXX_Interface/XXXFILES"
#archdir="path/archive"
#filename=${fn##*/}
#ORACLEID=xxxxx/xxxxxxx@xxxxx
#RespAppSN = SYSADMIN
#Respname='System Administrator'
#User_Name = XXXX
#[WAIT=N|Y|
#CONCURRENT = CONCURRENT
#ProgAppSN= FND
#ProgName= FNDSCURS
#PROGNAMDESC="Active Users"
#*********************************************************************

if [ -z "${ORACLE_HOME}" ]
then
echo ""
echo "ORACLE_HOME is not set."
echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo ""
echo "Can not find sqlplus."
echo ""
echo "Exiting $0"
echo ""
exit 1
fi


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

cd $datadir
for f in *.txt; do
fn="${f%.*}"
l=$(sed -n '3 {s/UserFilename="//;s/.txt"$//;p;q;}' "$f")
case $l in
SHIP_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXXX/$l.dat";;
PICK_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXXX/$l.dat";;
SHIP_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXX/$l.dat";;
PICK_*_XXXX) mv "${fn##*/}.dat" "$datadir/XXXX/$l.dat";;
LOCATOR_*) mv "${fn##*/}.dat" "$datadir/LOCATOR/$l.dat";;
*) echo "error, unknown file type: $l" > "$ERROR_LOG
continue
fi

filename=$l
echo "Starting job..."
echo "Connecting to SQL*Plus..."

$ORACLE_HOME/bin/sqlplus -s <<EOF $CONNECTION
whenever sqlerror exit failure
exec $OWNER.$SCRIPT('$l');
echo $l $ROWCOUNT success

exit
EOF

CONCSUB xxxxx/xxxxxxxx SYSADMIN 'System Administrator' xxx4 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
continue

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

 

[Annihilannic]

This is how I would indent it:

#ORACLE_HOME=$ORACLE_BASE/product/10.2.0
#CONNECTION=xxxxxxxxxxxx/xxxxxxxxxxxx@xxxxxx
#OWNER=xxxxx
#Script=xxxupload
#SCRIPT_PATH=`whence $0` >/dev/null 2>&1
#SCRIPT_HOME=`dirname $SCRIPT_PATH
#datadir="/u04/XXX/XXX_Interface/XXXFILES"
#archdir="path/archive"
#filename=${fn##*/}
#ORACLEID=xxxxx/xxxxxxx@xxxxx
#RespAppSN = SYSADMIN
#Respname='System Administrator'
#User_Name = XXXX
#[WAIT=N|Y|
#CONCURRENT = CONCURRENT
#ProgAppSN= FND
#ProgName= FNDSCURS
#PROGNAMDESC="Active Users"
#*********************************************************************

if [ -z "${ORACLE_HOME}" ]
then
        echo ""
        echo "ORACLE_HOME is not set."
        echo "ORACLE_HOME is needed for SQL*Plus and SQL*LDR."
        echo ""
        echo "Exiting $0"
        echo ""
        exit 1
fi

if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
        echo ""
        echo "Can not find sqlplus."
        echo ""
        echo "Exiting $0"
        echo ""
        exit 1
fi

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

cd $datadir
for f in *.txt; do
        fn="${f%.*}"
        l=$(sed -n '3 {s/UserFilename="//;s/.txt"$//;p;q;}' "$f")
        case $l in
                SHIP_*_XXXX)  mv "${fn##*/}.dat" "$datadir/XXXXX/$l.dat";;
                PICK_*_XXXX)  mv "${fn##*/}.dat" "$datadir/XXXXX/$l.dat";;
                SHIP_*_XXXX)  mv "${fn##*/}.dat" "$datadir/XXXX/$l.dat";;
                PICK_*_XXXX)  mv "${fn##*/}.dat" "$datadir/XXXX/$l.dat";;
                LOCATOR_*)  mv "${fn##*/}.dat" "$datadir/LOCATOR/$l.dat";;
                *)  echo "error, unknown file type: $l" > "$ERROR_LOG
                        [COLOR=red]continue 2 # skip to next iteration of for loop[/color]
        [COLOR=red]esac[/color]

        filename=$l
        echo "Starting job..."
        echo "Connecting to SQL*Plus..."

        # EOF needs to be in left-most column
        $ORACLE_HOME/bin/sqlplus -s <<EOF $CONNECTION
                whenever sqlerror exit failure
                exec $OWNER.$SCRIPT('$l');
                echo $l $ROWCOUNT success

                exit
EOF

        CONCSUB xxxxx/xxxxxxxx SYSADMIN 'System Administrator' xxx4 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
        [COLOR=red]continue # what is this supposed to do?  subsequent code will never run[/color]

        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

Are the first 20 lines or so supposed to be commented out?

I would stick to one format for if statements, i.e. put your then on the same line or the next line consistently (just a style thing).

I would use the same method for inserting the date stamp everywhere, rather than using $NOW in some places, back quotes in others, and $( ... ) in others.

Note that I changed one of the continues to continue 2, this is to make it jump to the next iteration of the for loop rather than just exiting the case statement early.

Annihilannic.