Sqlplus - Shell Programming and Scripting

LiquidChild · #1 (**permalink**) 06-01-2007

I am looking to loop round a load of files and execute each in sqlplus, I have looked at the forum to see what was posted in the past, but most of the examples seem to use the sql being passed in through the script, which is not really what I am looking for, can someone tell me if the code below is wrong, as it does not seem to work correctly:

Code:

for arrays in packageSpecs packageBodies procedures triggers functions views sqlScripts
      do
         eval count=\${#${arrays}[*]}

         writeHeaderToLog "Executing contents of ${arrays}"

         if [ $count -eq 0 ]
         then
            writeToLog "Nothing to be executed"
         else
            counter=0

            while [ $counter -lt $count ]
            do
               writeToLog

               eval fileToExecute=\${${arrays}[${counter}]}

               sqlLogFile=$fileToExecute.log

               # Log into sql plus, we log in for each script incase
               # the script contains an exit statement

               writeToLogFile "Executing $fileToExecute"
               sqlplus $databaseUser/$databasePassword@$databaseSID @$fileToExecute whenever sqlerror exit sql.sqlcode >> $LogDir/$sqlLogFile 2>&1

               errorCode=$?

               if [ $errorCode != 0 ]
               then
                   writeErrorToLog "SQLPlus failed for $fileToExecute with errorcode: $errorCode"
               else
                   writeToLog "$fileToExecute: EXECUTED SUCCESSFULLY"
                   exit
               fi

               counter=$counter+1
           done
fi
      done

      writeHeaderToLog "Execution Complete"
   fi

Shell_Life · #2 (**permalink**) 06-01-2007

LiquidChild,
Your main problem is that you think you are loading an array in the
"for" statement -- not true, "array" is just a variable.
Here is one way of doing:

Code:

mList="packageSpecs packageBodies procedures triggers functions views sqlScripts"
for fileToExecute in ${mList}
do
   sqlLogFile=${fileToExecute}".log"
   # Log into sql plus, we log in for each script incase
   # the script contains an exit statement
   writeToLogFile "Executing "$fileToExecute
   sqlplus $databaseUser/$databasePassword@$databaseSID @$fileToExecute whenever sqlerror exit sql.sqlcode >> $LogDir/$sqlLogFile 2>&1
   errorCode=$?
   if [ $errorCode != 0 ]
   then
      writeErrorToLog "SQLPlus failed for $fileToExecute with errorcode: $errorCode"
   else
      writeToLog "$fileToExecute: EXECUTED SUCCESSFULLY"
      exit
   fi
done

LiquidChild · #3 (**permalink**) 06-01-2007

I'm not sure that is the problem shell_life, the problem i seem to be encountering more is that it does not seem to pipe the output to $LogDir/$sqlLogFile , if i run it without this part then the sql file executes correctly although I see the output on the screen.

Shell_Life · #4 (**permalink**) 06-01-2007

LiquidChild,
Your "count" is being always zero, as "array" is not an array.
Thus whenever this shell runs, it will write "Nothing to be executed"
in your log.
Did you at least try to run the shell that I changed?

LiquidChild · #5 (**permalink**) 06-01-2007

How are they not arrays?

They have been defined as such at the top of the code:

set -A database
set -A databaseUser
set -A databasePassword
set -A packageSpecs
set -A packageBodies
set -A procedures
set -A triggers
set -A functions
set -A views
set -A sqlScripts

Its not that its writing the wrong things to the log, its that its not writing anything to the log, I expected to see the output of the sql, which is this case is a simple select statement. It seems to output to the screen as expected when I run it without the '>>'

Shell_Life · #6 (**permalink**) 06-01-2007

LiquidChild,
Whenever asking for help on a specific shell, please display the entire shell.
Anyone can name variables as they wish.
Just because it is named as "arrays" it does not mean it is an array.
Whithout the "set -A" statements that you later disclosed, your "for"
statement leads people to believe that you are looping through a group
of strings.

Code:

for arrays in packageSpecs packageBodies procedures triggers functions views sqlScripts

LiquidChild · #7 (**permalink**) 06-01-2007

Quote:

Originally Posted by Shell_Life

LiquidChild,
Whenever asking for help on a specific shell, please display the entire shell.
Anyone can name variables as they wish.
Just because it is named as "arrays" it does not mean it is an array.
Whithout the "set -A" statements that you later disclosed, your "for"
statement leads people to believe that you are looping through a group
of strings.

Code:

for arrays in packageSpecs packageBodies procedures triggers functions views sqlScripts

Shell_life thanks for the help, but believe I have the problem sorted. I didn't display the entire script as it was over 500 lines long, and thought that if people were unsure they would have asked.

Again though thanks for the help

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
sqlplus call	JohnZ1385	UNIX for Dummies Questions & Answers	1	06-05-2008 07:48 AM
Sqlplus	antkiu	Shell Programming and Scripting	4	09-13-2006 09:36 PM
SQLPLUS in script	renichols	Shell Programming and Scripting	4	03-06-2006 08:43 AM
sqlplus invocation	new2ss	Shell Programming and Scripting	4	02-07-2006 12:41 AM
returning value from sqlplus	malaymaru	Shell Programming and Scripting	1	01-31-2006 12:03 AM

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode