Sqlplus

Shell Programming and Scripting
1

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:

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
2

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:

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
3

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.

4

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?

5

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 '>>'

6

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.

for arrays in packageSpecs packageBodies procedures triggers functions views sqlScripts
7

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 :slight_smile:

8

However off the back of this i do still have a problem, the problem is that my session does not seem to exit out of sqlplus after executing the .sql statement.

Any suggestions?

Actually is it possible to know if the sqlplus process is still running? I am thinking that if someone puts in an 'exit' statment in there .sql file and then I run another one it will kill the shell, which i don't want. I only want to exit the sql session if its still active, should I be using processID's?

9

LiquidChild,
I do not use "sqlplus" -- I use Informix.
I was just trying to help you with the shell.
Maybe someone who uses "sqlplus" can help you with this specific
issue of exiting.
Wish you good luck.

10

I am not sure that the whenever clause can be specidied in the sqlplus command line.
Try the following syntax :

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

Jean-Pierre.