I am using "Oracle Solaris 11.4", trying to achieve a task of getting file name and their timestamps from a specific location located on different SSH server.
Desired output after inserting the values into the Oracle database table -
Below is my shell script conn_trs_dropbox_and_list.sh which is being executed in my local unix directory using the command "ksh conn_trs_dropbox_and_list.sh".
When I execute this script, I have noticed couple of things -
The script shows an error at the line where the variable "source_files" is being defined.. that entire line shows as an error
conn_trs_dropbox_and_list[67]: get_file_date_time[19]: \`stat -c"%y %n" /interface/gt/os/trs/out_sent/*.*\`: not found [No such file or directory]
The echo shown prior to defining and initializing the variable "source_files" - echo "\`stat -c"%y %n" * | awk '{print \$1"*"\$2"*"\$4}'\` " - works and lists the files in that location as expected. But when the same is being assigned to variable "source_files" with appropriate escape characters in the next line, it won't work. I don't see any difference between the value that was provided in the echo statement and the value that was assigned in the variable "source_files" looking at the error message.
The INSERT into the table happens with all NULL values.
Though this line - echo "inside the loop $i" - is not being printed, and though the $source_files does not hold any value, the INSERT still gets executed and inserts a null record. The last line shown in the output is - "Loop the list of files to pick the relevant ones along with timestamp".
I added the required escape characters ("") at the required places to make sure the script variables have correct values.. Still no luck.
Any inputs on how to rectify the above shown error and list the files appropriately , and make the insert work inserting the appropriate file details, is highly appreciated.
-----------------------------------------------------------------------------------
#!/bin/ksh
# -*- ENCODING: UTF-8 -*-
set -x
#function get_file_date_time provides the list of files changed in the last 2 days along with the date, timestamps
function get_file_date_time
{
quotes="\""
# Remove FCP_LOGIN=" from $fcp_login
fcp_login=${fcp_login#FCP_LOGIN=$quotes}
fcp_login=${fcp_login%$quotes}
# use hard-coded username for testing, comment below line later
fcp_login="kkesari/PWD@DB"
today=`date +%Y-%m-%d`
yesterday=`gdate -d "1 day ago" +"%Y-%m-%d"`
echo $today
echo $yesterday
whoami
pwd
ssh MYSERVERNAME /bin/bash <<EOT
#ssh -T MYSERVERNAME <<EOT
pwd
cd /interface/gt/os/trs/out_sent
pwd
ls -ltr
echo "Get file names along with the timestamps"
echo "\`stat -c"%y %n" * | awk '{print \$1"*"\$2"*"\$4}'\` "
# this above like echo "\`stat -c"%y %n" * | awk '{print \$1"*"\$2"*"\$4}'\` " works well
source_files="$("\\\`stat -c\"%y %n\" /interface/gt/os/trs/out_sent/*.*\\\`")"
echo $source_files
echo "after the source_files $source_files"
last_changed_date=" "
last_changed_time=" "
file_name=" "
# Loop the files and capture the file name, date and time stamps and insert into MY Table
echo "Loop the list of files to pick the relevant ones along with timestamp"
for i in $source_files
do
echo "inside the loop $i"
last_changed_date=`echo \$i | cut -d "*" -f1`
last_changed_time=`echo \$i | cut -d "*" -f2`
#echo "last changed is $last_changed_time"
file_name=`echo \$i | cut -d "*" -f3`
#echo $fcp_login
INSRT=`sqlplus -s $fcp_login <<END_SQL
WHENEVER SQLERROR EXIT 1
SET HEADING OFF
SET TAB OFF
SET PAGESIZE 0
SET LINESIZE 10000
SET FEEDBACK OFF
INSERT INTO XXX_GET_TRS_FILE_DTLS VALUES('$file_name','$last_changed_date','$last_changed_time');
EXIT;
END_SQL`
echo "Inserting the data into XXX_GET_TRS_FILE_DTLS table $INSRT"
if [ $today == $last_changed_date ]
then
echo "file has been changed today - $last_changed_date at $last_changed_time - $file_name"
echo "Inserting the data into XXX_GET_TRS_FILE_DTLS table $INSRT"
else
if [ $yesterday == $last_changed_date ]
then
echo "file has been changed yesterday - $last_changed_date at $last_changed_time - $file_name"
echo "Inserting the data into XXX_GET_TRS_FILE_DTLS table $INSRT"
else
echo "file has been changed earlier, not today or yesterday - $last_changed_date at $last_changed_time - $file_name"
fi
fi
done
EOT
}
get_file_date_time
Thank you so much for your reply. After making the changes you have suggested, replacing the variable initialization for source_files, I have noticed that the script is partially working.
So, removing the backticks and using the $() notation, the below statement is actually inserting the files and their timestamps perfectly as expected.
But the only problem I noticed is its actually getting the file list from the current working directory. My requirement is to pull the file list from the directory location on the SSH server.
Then when I change the source_file variable to the one shown below, the script results in error.
stat: cannot stat '/interface/gt/os/trs/out_sent/*.*': No such file or directory
conn_trs_dropbox_and_list.sh[72]: get_file_date_time[19]: [43]: : not found [No such file or directory]
awk: syntax error near line 1
awk: bailing out near line 1
Suspecting the stat command, I tried manually (not in the script) executing the following command and it works well.
stat -c"%y %n" /interface/gt/os/trs/out_sent/*.*
Can you please suggest me what is going on here and how to fix this one?
Also, I needed those "*" symbols in the awk command because I am using them later on in my script to differentiate or identify the delimiter in my "cut" command for inserting.
Any help is greatly appreciated. Thank you so much for your inputs earlier.
pay attention to using ' (not `), the path to the directory to stat and the placement of the trailing )
Obviously it assumes there're files under /interface/gt/os/trs/out_sent directory with at least one .
I'd recommend adding set -x to the top of your script (after the #!/bin/ksh) to enable debugging - I'm sure there're other gremlins in this code.
Thank you so much for your reply.
I tried doing the same as you listed but still has the same error shown up. My hunch is in spite of me providing the set of commands to connect to the SSH server and change directory to a specific location on the SSH server, this particular command is still looking in the local directory and since it is not able to find the directory within the local unix box, it is failing.
If you can refer to my entire shell script listed in my first question, you see that there are steps mentioning -
ssh MYSERVERNAME /bin/bash <<EOT
#ssh -T MYSERVERNAME <<EOT
pwd
cd /interface/gt/os/trs/out_sent
pwd
ls -ltr
Then the place where the source_files are declared, I changed to what you mentioned -
stat: cannot stat '/interface/gt/os/trs/out_sent/*.*': No such file or directory
conn_trs_dropbox_and_list.sh[72]: get_file_date_time[19]: [43]: : not found [No such file or directory]
awk: syntax error near line 1
awk: bailing out near line 1
Is there a way to mention the "stat" command to look not in the local unix box but look in the SSH server where it was connected to and the directory path within that server ?
Not so sure.. Any suggestions are greatly appreciated.
First of all, please start using markdown code tags when posting data/code samples.
If you're to use the code tags and payed attention to the exact things to pay attention to, you'd see that
See if you can "see" the difference.
Regarding running commands remotely and resolving variable remotely (not locally)... Take a look at one of the threads on the subject.
In essence, you might want to:
try creating a test harness with the minimum set of commands to be executed remotely to ease the debugging - it's impossible to follow up everything you're trying to do when debugging.
Consider using 'EOT' vs plain EOT for the heredoc to prevent variable expansion locally
consider using \$var vs plain $var when referencing shell variables to be referenced
remotely.
Furthermore, read the mentioned link (one of many) on the heredoc and the remote execution and try to reduce the complexity of your script to get the basics of the remote execution correctly.
Thank you so much! I am surely progressing towards the completion. Just few questions though..
With the new changes you have suggested, I am able to list the files from the SSH server location and grab those into my variable "source_files". Perfectly what I wanted it to be.
The script then loops through the variable, uses few cut commands to store each value and uses those in the insert script invoking sqlplus.
The script now fails with the error as "bash: line 40: sqlplus: command not found", I think this is because the sqlplus command does not exist on the SSH server. How to invoke the sqlplus command while I am on the SSH server?
Also, the code shown below lists the files in the SSH server directory location. Is there a way to restrict to list only last 2 days files ?
source_files=$(stat -c"%y %n" * | awk '{print $1"*"$2"*"$4}')
for i in $source_files
do
INSRT=$(sqlplus -s $fcp_login<<'END_SQL'
WHENEVER SQLERROR EXIT 1
SET HEADING OFF
SET TAB OFF
SET PAGESIZE 0
SET LINESIZE 10000
SET FEEDBACK OFF
INSERT INTO XXX_GET_TRS_FILE_DTLS VALUES('$file_name','$last_changed_date','$last_changed_time');
EXIT
END_SQL
)
done
Block Code
As mentioned earlier, I will be thankful for all your suggestions. Thank you!
In this case (if Oracle/sqlplus) doesn't exist on the remote system(s), then I would bring all the necessary extracted variables (for the sqlplus execution) to the local/source machines where you're ssh-ing from to the remote systems AND execute sqlplus locally (obviously assuming that Oracle/sqlplus is setup properly on the local server.
Thank you so much for your reply. Not sure if I am not capturing the values or thats the functionality..but when I have an exit statement after capturing the value in the variable 'source_files' and just prior to the for loop, it seems the variable value also is being cleared.
This time SQLPLUS works well, but the variable $source_files seems to have empty values.
Do you recommend pushing the values into a temporary text file prior to exit and read the file later on down below ?
source_files=$(stat -c"%y %n" * | awk '{print $1"*"$2"*"$4}')
echo "source files are "$source_files
exit
echo "source files after exit are "$source_files
for i in $source_files
do
INSRT=$(sqlplus -s $fcp_login<<'END_SQL'
WHENEVER SQLERROR EXIT 1
SET HEADING OFF
SET TAB OFF
SET PAGESIZE 0
SET LINESIZE 10000
SET FEEDBACK OFF
INSERT INTO XXX_GET_TRS_FILE_DTLS VALUES('$file_name','$last_changed_date','$last_changed_time');
EXIT
END_SQL
)
done
Or can I use another SSH command in front of the SQLPLUS something like the one shown below to invoke SQLPLUS from the calling server ? will this approach work ?
you probably have more to this script where you have nested loops reading/writing stuff from stdin/stdout - just a guess as I don't see small enough of your simplified test harness script.
Below is the mnemonic that I think you're trying to implement. See if it helps and/or try to implement a very basic test harness and troubleshoot on 1 or 2 host without the rest of your script.
#!/bin/bash
# read the list of hosts to be ssh-ed to from a file (hostListFile)
# if you're providing the list of hosts somehow differently - change it here
while read host junk <&3
do
ssh "${host}" <<'EOF' |
doYourDataCollectionParsingHere and output with your * as a field separator
EOF
# read output of the previous ssh for each host and populate the needed vars for sqlplus
while IFS='*' read var1 var2 varN
do
doYourSqlPlusHere with read var1/var2/varN vars
done
done 3< hostListFile
