0

I am trying to write a script that will connect to Oracle from a jump server (i.e) I will execute my script from a jump server, In my script I will have a config file as below

#USERNAME   PASSWORD    TNSNAMES    SUCCESS/FAIL
ODB        ODB123       ODB1        
CDC        CDC123       CDC1        

So , Now i will pass the config file in a loop and will execute

${ORACLE_HOME}/bin/sqlplus -S ${USER}/${PASSWORD}@${TNSNAMES} 

So by executing the above query for the first time in a loop. It should try logging into the server using the above username and password and it should write into the config file like PASS if the connection succeeded and FAIL if connection failed.

#USERNAME   PASSWORD    TNSNAMES    SUCCESS/FAIL
ODB         ODB123      ODB1        PASS
CDC         CDC123      CDC1        FAIL

And again it should read the updated config file and if it is PASS it should get into the database and do respective sqls we call. And it should ignore the database if FAIL.

I have tried the below script which will write the contents to other file.

#!/bin/sh

. ~/.ODBenv
cat test.txt | grep '^#' > test1.txt
cat test.txt | grep -v '^#' | awk 'NF'|while read i;do
#if [ -z "$i" ]
#then
#break;
#fi
user_name=`echo $i|awk {'print $1'}`
password=`echo $i|awk {'print $2'}`
TNS_NAME=`echo $i|awk {'print $3'}`
echo "exit" | ${ORACLE_HOME}/bin/sqlplus -S  ${user_name}/${password}@${TNS_NAME} |grep -E 'ORA|SP2' > /dev/null
if [ $? -ne 0 ]
then
   echo -e "${user_name}\t      ${password}\t   ${TNS_NAME}\t   PASS">>test1.txt
else
   echo -e "${user_name}\t      ${password}\t   ${TNS_NAME}\t   FAIL">>test1.txt
fi
done
#done < test.txt|grep -v "^#" | awk "NF"

I tried writing the same contents into a new file and the above script worked.

Any idea how to write in the Original file like PASS/FAIL. Original file is as mentioned below.

#USERNAME   PASSWORD    TNSNAMES    SUCCESS/FAIL
ODB        ODB123       ODB1        
CDC        CDC123       CDC1        
  • Same file or new file for the updated PASS/FAIL fields? If same file: Will the script be executed another time with PASS/FAIL field already present? And what did you try yourself so far? – Philippos Mar 16 '17 at 7:11
  • Happy if it is overwritten or appended to the same file. I tried to put those values into a different file, It worked. I used the below script to do so. – sabarish jackson Mar 16 '17 at 7:28
  • ` #!/bin/sh . ~/.ODBenv cat test.txt | grep '^#' > test1.txt cat test.txt | grep -v '^#' | awk 'NF'|while read i;do user_name=echo $i|awk {'print $1'} password=echo $i|awk {'print $2'} TNS_NAME=echo $i|awk {'print $3'} echo "exit" | ${ORACLE_HOME}/bin/sqlplus -S ${user_name}/${password}@${TNS_NAME} |grep -E 'ORA|SP2' > /dev/null if [ $? -ne 0 ] then echo -e "${user_name}\t ${password}\t ${TNS_NAME}\t PASS">>test1.txt else echo -e "${user_name}\t ${password}\t ${TNS_NAME}\t FAIL">>test1.txt fi done ` – sabarish jackson Mar 16 '17 at 7:28
  • I want it to overwrite the original file with PASS/FAIL values. – sabarish jackson Mar 16 '17 at 7:30
  • Better put your script in the question, so it can be formatted (preceed each line of the script with 4 spaces) and clarify the problems you had: Were all variables set correctly? Did the database connection succeed? was the file written other than intended? – Philippos Mar 16 '17 at 8:09
0

So it's just about the editing in place. I prefer sed -i for this:

tail -n+2 test.txt|while read -r line ; do
  user_name=`echo "$line"|cut -d\  -f 1`
  password=`echo $line|cut -d\  -f 2`
  TNS_NAME=`echo $line|cut -d\  -f 3`
  echo "exit" | ${ORACLE_HOME}/bin/sqlplus -S ${user_name}/${password}@${TNS_NAME} |grep -E 'ORA|SP2' > /dev/null
  if [ $? -ne 0 ]; then
    result=PASS
  else
    result=FAIL
  fi
  sed -i -E "/$line/s/\t*FAIL|\t*PASS|$/$result/" test.txt
done

So, for the given $line I remove any trailing PASS or FAIL with it's tab and replace it by the $result. Hope it works for you.

|improve this answer|||||
  • Hi @Philippos I tried executing your script, but it didnt make any changes in the test.txt. Any suggestions here? And my taught is like initially there wont be any PASS or FAIL in the test.txt file. We need to add append PASS or FAIL to the last column of each and every line based on the result. – sabarish jackson Mar 17 '17 at 4:38
  • No, the |$ branch works if there is no PASS or FAIL. I did test it with and without. If it doesn't work with them, they are probably not separated by a tab, so we could use \s* instead of \t, but I can't understand why it should fail for the first run. Maybe try the sed without the script first to know where the problem is. – Philippos Mar 17 '17 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.