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
Improve this question
6
  • 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? Commented Mar 16, 2017 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. Commented Mar 16, 2017 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 ` Commented Mar 16, 2017 at 7:28
  • I want it to overwrite the original file with PASS/FAIL values. Commented Mar 16, 2017 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? Commented Mar 16, 2017 at 8:09

1 Answer 1

Reset to default
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
2
  • 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. Commented Mar 17, 2017 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. Commented Mar 17, 2017 at 7:15

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.