I have wrriten a script to call sql script to do some work in database. However, the script couldn't be executed. The only information was: ksh: ./updt_attrib.ksh cannot execute. Please help me to identify where the problem is. I post script here for your reference. Thanks a lot.


#!/bin/ksh

# Created By: duke0001
# Created On: 9/14/06
# Description: This script update xxx information for xxxx
# attributes tables.

# Check to make sure the correct number of parameters have been enetered.
#if [[ $# -lt 1 ]] then
# echo "Incorrect number of parameters."
# echo "Usage: $0 [ORACLE_SID]"
# exit 1
# fi

#Initialize variables.
ORACLE_SID=$1
ORACLE_HOME=/oracle/product/9.2.0
WORK_DIR=`dirname $0`
MINUTES=0
#FILECOUNT=0
#FILETRANSFERED=1
DBDOWN=1

# Change to working directory.
cd $WORK_DIR

# Set environment variables.
export ORACLE_SID
export ORACLE_HOME
export PATH=$PATH:/oracle/product/9.2.0:/oracle/product/9.2.0/bin

# Check to make sure that the database is available.
while [ $DBDOWN -eq 1 ]
do
sqlplus -s > /dev/null 2>&1 "dbuser/password@$ORACLE_SID" <<EOF
whenever sqlerror exit 1
select * from dual;
exit 0
EOF

if [ $? = 1 ]
then DBDOWN=1
date +"%D %T: Still waiting for $ORACLE_SID to become available." >> updt_attrib.log
sleep 60
else DBDOWN=0
fi
done

date +"%D %T: Database $ORACLE_SID is up and running." >> updt_attrib.log
echo "" >> updt_attrib.log

date +"%D %T: update attributes script started." >> updt_attrib.log
sqlplus -s > temp.log 2>&1 dbuser/passwd@$database @tools/updt_attrib.sql

if [ $? = 0 ]
then date +"%D %T: $scripts successfully executed." >> updt_attrib.log
else date +"%D %T: Error executing script." >> updt_attrib.log
grep "ORA-" temp.log >> updt_attrib.log
date +"%D %T: Exiting script." >> updt_attrib.log
mail -s 'DB Update: Unsuccessful. See Log file for errors.'
duke0001@company.com<updt_attrib.log
exit 1
fi
done

date +"%D %T: End update attributes in xxx data warehouse." >> updt_attrib.log
mail -s 'End update attributes in xxx data warehouse.' duke0001@company.com<updt_attrib.log
exit 0

Check if ksh is in /bin directory.

I think ksh is in /bin directory because there is another script with #!/bin/ksh
over there and it has been executed for years. By the way, what about the script itself? Is there any coding problem? Thanks.

What are the perms on the script? It needs to have execute bit turned on.

chmod +x updt_attrib.ksh

Carl

As far as the script, you might consider indenting the info in loops and if statements to make the more readable.

Most scripts I've seen don't do:

and

Usually it's something like:

That way it's easier to see what code belongs with which part of a statement.

Yea, that while loop. I didn't realize it was a large as it was until I looked a little harder. It initially looked like an infinite loop.

And you're going to need to echo something to the mail statements. At least on the one I just tested it on, mail is looking for a body to pass along. So something like:

Also, $? is a numeric value so use "-eq" vs "=". It'll probably work but it's always good to use the correct syntax.

Carl

Carl:

Thanks a lot for your advice. I am new to shell script and would like to learn more on it. Is there any good book could you recommand? I will check my script with your advice and come back to report the result. Thanks again.

There may be other errors in your script. Those are the ones that jumped out while I was waiting on some patching I was doing.

To learn about the korn shell specifically, I'd suggest Learning the Korn Shell published by O'Reilly.

A nice reference that goes through multiple shells (ksh, bash, csh, sh, etc) would be Shell Scripting by Example.

I used both books when I was creating my scripting class. The nice thing about the second book is it lists a bunch of commonly used utilities. I picked up a little knowledge of some of the more obscure unix tools by running man tool.

Carl