Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sqlplus

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sqlplus
# 1  
Old 06-01-2007
Sqlplus
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

# 2  
Old 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

# 3  
Old 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.
# 4  
Old 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?
# 5  
Old 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 '>>'
# 6  
Old 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

# 7  
Old 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 Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX and Linux Applications

Problem on SQLplus command ""bash: sqlplus: command not found""

Hi all, i face an error related to my server ""it's running server"" when i use sqlplus command $ sqlplus bash: sqlplus: command not found the data base is up and running i just need to access the sqlplus to import the dump file as a daily backup. i already check the directory... (4 Replies)
Discussion started by: clerck
4 Replies

2. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

3. Shell Programming and Scripting

Sqlplus Help

When i run the following script am getiing the output correct but i want to get it in the form of a table, could any one help me please. the script is a s follows count=`sqlplus -s $ORACLE_ACCOUNT << EOF set heading off set wrap on set feedback off column ChangeNumber format a12 column... (9 Replies)
Discussion started by: jhon1257
9 Replies

4. Shell Programming and Scripting

sqlplus: cannot execute

Hi, This is the content in my .profile on a unix server, MAIL=/usr/mail/${LOGNAME:?} umask 027 #added by enRole Agent PATH=${PATH}:/opt/app/p1crm1c3/informatica/oracle/product/10.2.0 export PATH PATH=$PATH:/opt/app/p1crm1c3/informatica/oracle/product/10.2.0/network/admin/sqlnet.ora;export... (2 Replies)
Discussion started by: yohasini
2 Replies

5. AIX

SQLPLUS problem

Hi guys, Here is the error i get by running a "sqlplus -v" after installing an oracle client 10.2.0.5 on an AIX 5.3.9 server. Could not load program sqlplus: Symbol resolution failed for sqlplus because: Symbol __pthread (number 307) is not exported from dependent ... (3 Replies)
Discussion started by: Chapel
3 Replies

6. Shell Programming and Scripting

sqlplus @ not working sqlplus \@ working..

Hi All, I am facing a strange problem on one of my unix servers. When i try to login using the standard method: it fails with below message >sqlplus REF1SSTDBO1/REF1SSTDBO1@TKS3N10G > TKS3N10G ksh: TKS3N10G: not found But it works perfectly when i escape with ;\ >sqlplus... (3 Replies)
Discussion started by: kunwar
3 Replies

7. Shell Programming and Scripting

SQLPLUS error

I am running a script that invokes SQLPLUS. During the execution I get the following: SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 11 16:12:50 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 -... (3 Replies)
Discussion started by: ssmith001
3 Replies

8. HP-UX

how can i call sqlplus?

i am new working with hp-ux but i had to because of my job. so i want to execute some sql scripts but when i call sqlplus to run them it responds /sbin/sh: sqlplus not found. i have oracle 8.1.7 installed. what should i do sorry if this sounds too easy but i am now learning. The same... (13 Replies)
Discussion started by: theodore
13 Replies

9. Shell Programming and Scripting

Using a variable in sqlplus

Hello, I'm trying to write a script that will loop a sql statement through an external list. Basically, the script enters a loop and runs the sql statement for each entry in the list file. Currently, the script will stop at a cursor where I can then manually enter the SQL statment. This is... (11 Replies)
Discussion started by: MadHatter
11 Replies

10. Shell Programming and Scripting

Sqlplus

Hi all, I am new to SQLPLUS, can anyone tell me what is the following codes doing? DECLARE cursor c1 is select bts_int_id, max(ave_busy_tch/res_av_denom14) maxBusyTch from p_nbsc_res_avail where to_char(period_start_time,'yyyymmdd')=to_char((sysdate-1),'yyyymmdd') group by... (4 Replies)
Discussion started by: antkiu
4 Replies
Login or Register to Ask a Question