Shell Script

vasuvv · November 3, 2017, 10:15am

Hi All,

I am newbie to Unix and Shell script. Can you please help me to write a script.

Below is my requirement.

1.Send in email every 15 minutes from 12 to 3:30 PM. E-mail should only be sent ONLY when below Query have data return.

Email format:

 To: xyz 
 From: abc
 Email Subject: 
 Email content: <data return from below query>

Query : SELECT * from EMP where DEPT = 87;

Thanks
Vasu

bakunin · November 3, 2017, 10:44am

You do not need to put this into the script as it can be done by cron . I suggest you read the man page of cron and crontab to make yourself acquainted with it.

I suggest you try to first do the query and then find out if its empty or not. As i do not know your database and your client i don't know what your output will look like in this case.

Anyway, after knowing this you should try to put all the output (in case there is one) into a file. Once you have this, report back here and we can show you how to put it into a mail isntead of a file.

I hope this helps.

bakunin

vasuvv · November 6, 2017, 1:39am

Hi,

The table is having data. But need to check for every 15 mins whether its having data or not. I have written the script as follow. But giving error invalid number of arguments/file is not avaialeble. Please have a look and correct it.

FILE="EMP_DETAILS.txt"
#FILE="EMP_DETAILS.$(date +%F_%R).txt"
#touch $FILE

echo $ORACLE_HOME/bin/sqlplus
echo "======== START ============="

#$ORACLE_HOME/bin/sqlplus -s scott/tiger@XE  <<EOF
RESULT=`$ORACLE_HOME/bin/sqlplus -silent ${DB_USER}/${DB_DECRYPTED_PASSWORD}@${DB_CONNECTION_STRING} <<ENDA

set pagesize 0 feedback off verify off heading off echo off
column last_business_date format date
SELECT * from EMP where DEPT = 87; 

exit;
ENDA`

echo $RESULT > $FILE
#sed -i 's/#^/\n/g' $FILE
sed '/#^/G' $FILE
echo "======== END ============="


## READ AND WRITE THE FILE AND TOOK THE BACKUP
#
#FILE1="EMP_DETAILS.$(date +%F_%R).txt"
#{
# while IFS= read -r line
#do
  #  shnfix "$line"
#  done < "$FILE1"
#} > "emp.backup.$(date +%F_%R).txt"

##this will fetch the number of lines in the file
#wc -l  $FILE1
#if [wc -gt 0]; then
mail -s "!!! WARNING File" << $EMP_DETAILS.txt
echo "The IDL  processing has been completed for Business date $BUSINESS_DATE." | mail -s "IDL Processing Status $env" "robin@gmail.com" -- -r "robin@gmail";
#fi

MadeInGermany · November 6, 2017, 2:58am

This is risky, a variable in a command argument that is not in "quotes".
A fix is

echo "$RESULT" > $FILE

You do not use the variable elsewhere, so try to directly produce the file

$ORACLE_HOME/bin/sqlplus -silent ${DB_USER}/${DB_DECRYPTED_PASSWORD}@${DB_CONNECTION_STRING} <<ENDA >$FILE
...
ENDA

bakunin · November 6, 2017, 4:33am

I know this is commented out, but in case you want to activate it once. This:

will not work, you need spaces around the "[" and "]" and if you want to use a command in that place you need process substitution:

if [ $(wc -l $FILE) -gt 0 ] ; then

I hope this helps.

bakunin

Don_Cragun · November 6, 2017, 4:51am

bakunin:

I know this is commented out, but in case you want to activate it once. This:

will not work, you need spaces around the "[" and "]" and if you want to use a command in that place you need process substitution:
if [ $(wc -l $FILE) -gt 0 ] ; then
I hope this helps.

bakunin

Note that wc -l "$FILE" will give you output similar to:

   number_of_lines_in_filename filename

which test will not accept as a numeric value. To get just the number of lines in a file in the output from wc you need to make wc read from standard input instead of from a named file:

if [ $(wc -l < "$FILE") -gt 0 ]; then

vasuvv · November 6, 2017, 6:55am

Bakunin,

Here confused with giving FILE1 and "File" can you help me with MAIL command, do we need to give FILE1 or FILE??? getting errors at mailing part too.. pleasr correct me posted code and advise..

---------- Post updated at 05:25 PM ---------- Previous update was at 03:31 PM ----------

Hi,

It is connecting to the database and creating the .txt files but not getting mails. Pleasr help me with this.

Thanks

RudiC · November 6, 2017, 7:31am

If you're running the script in post#3, you should receive a message like

bash: warning: here-document at line 1 delimited by end-of-file (wanted `$EMP_DETAILS.txt')

You should

add the here document delimiter
define the EMP_DETAILS variable
IF IT IS THAT what you want: mail the contents of the here document. If not, reconsider your construct. Right now, it is eating away the second mail command, which, BTW, would not send the resulting text file.

Don_Cragun · November 6, 2017, 8:10am

Assuming that you have made some changes to the code in post #3 in this thread (based on the comments you have received so far), please show us your current code (in CODE tags). It is hard to guess at what might be wrong if we don't know exactly what your code is doing.

vasuvv · November 6, 2017, 11:58pm

please find below code which am running and Error message and advise.

FILE="scripts/EMP_FILE.txt"
#FILE="EMP_FILE.$(date +%F_%R).txt"
#touch $FILE

echo $ORACLE_HOME/bin/sqlplus
echo "======== START ============="
 
#$ORACLE_HOME/bin/sqlplus -s scott/tiger@XE  <<EOF
RESULT=`$ORACLE_HOME/bin/sqlplus -silent ${DB_USER}/${DB_DECRYPTED_PASSWORD}@${DB_CONNECTION_STRING} <<ENDA

set pagesize 0 feedback off verify off heading off echo off
column last_business_date format date
SELECT * FROM EMP WHERE DEPTNO IN(10,20);
exit;
ENDA`

echo "$RESULT" > $FILE
#sed -i 's/#^/\n/g' $FILE
sed '/#^/G' $FILE
echo "======== END ============="


## READ AND WRITE THE FILE AND TOOK THE BACKUP
#
FILE1="EMP_FILE.$(date +%F_%R).txt"
{
 while IFS= read -r line
 do
    shnfix "$line"
  done < "$FILE"
} > "emp.backup.$(date +%F_%R).txt"

##this will fetch the number of lines in the file
if [ $(wc -l < "$FILE") -gt 0 ]; then
uuencode EMP_FILE.txt EMP_FILE.txt|mailx -s  "EMPLOYEE DATA FOR DEPT 10 AND 20" "ROBIN@GMAIL.COM" 
fi

 shnfix: not found [No such file or directory]
 uuencode: not found
 mailx: not found [No such file or directory]

Don_Cragun · November 7, 2017, 2:35am

We would hope that you understand how to set the PATH environment variable so that your script can find the mailx , shnfix , and uuencode utilities on your system. I have never heard of shnfix , but the other two should be available on most UNIX and UNIX-like systems. The directories in which they are located, however, varies from system to system.

If is strange that you define a variable named FILE1 but never use it. It is strange that you create a file with a name similar (but sometimes not identical to the pathname assigned to FILE1 ), but never reference that file after it is created.

We have no idea when you should be using $FILE and when you should be using $FILE1 since we have no idea what shnfix does nor what you want to do with the contents of either of those files in the remainder of your script.

If we knew what operating system and shell you were using, we knew what shnfix does, we knew what you were trying to accomplish with your script, and we knew where the utilities that your script can't find were located; then we might be able to help you fix your script. Without all of this information, we have a MUCH lower chance of being able to fix your script than you do. We presume that you know what the utilities you are invoking are intended to do and that you know what your script is trying to do. Since we don't know those things, how do you expect us to advise you on the proper changes that would make your script do what you want it to do?

vasuvv · November 7, 2017, 4:42am

As the the query is going to run for every 15 mins, I would like to create a backup file and would like to name it as "EMP_FILE_bkp.txt"

For this it has to read the data from "EMP_FILE.txt" and Write it into "EMP_FILE_bkp.txt".

For this I was trying to write a Loop. I am not sure the command

SHNFIX

Can you help me to resove Read and write process and mail sending process.

Thanks

---------- Post updated at 03:12 PM ---------- Previous update was at 02:29 PM ----------

Don,

Please ignore my script and suggest me a new code for the below

Below is my requirement.

1.Send in email every 15 minutes from 12 to 3:30 PM. E-mail should only be sent ONLY when below Query have data return ELSE DISPLAY A MESSGAGE NO DATA EXISTED.

Email format:

To: xyz 
 From: abc
 Email Subject: 
 Email content: <data return from below query>

Query :  SELECT * from EMP where DEPT = 87;

RudiC · November 7, 2017, 4:55am

I'd propose you get your act together and present a concise and consistent picture of your problem, answering the questions raised by the various contributors, and make your comments fit the code. How about stripping down your code to the essential steps and test each one independently for errors?

there's no file called EMP_FILE_bkp.txt in your code
the "emp.backup.$(date +%F_%R).txt" is written but never used.
as case IS relevant in *nix code, SHNFIX in not shnfix . Please be careful when quoting.
What is the purpose of that sed command? Take it out for testing.

bakunin · November 7, 2017, 5:34am

You are right, that was a typo on my part. Thanks for the correction.

uuencode and mailx are both standard UNIX utilities. They should reside in /bin or /usr/bin on most systems (btw.: care to tell us on which OS you are?) If you have /bin in your PATH (which you should have anyways) that means they are not installed. Get them installed in this case.

In general "executable: not found" can have three reasons and you will have to investigate them yourself because we can't look onto your system:

1) the executable is installed but not in the PATH:
If you have some program /foo/bar/program and you call it with

program

(that is: without the full path) you need to its respective path included in the PATH statement (actually this is what PATH is for), like this:

PATH="${PATH}:/foo/bar"

2) the executable is not installed at all
You obviously need to install it, in this case. To find out if it is installed you can run the following command (note that it might run for some while)

find / -type f -name "your_executable" -print 2>/dev/null

3) the executable is installed but filemodes are wrong
The operating system identifies (potentially) executable files by the filemode, i.e.:

# ls -l /bin/mailx
-rwxr-xr-x    3 bin      mail         159650 Jan 22 2016  /bin/mailx

The first "x" means that the owner of the file (bin) may execute it. The second "x" means that every member of the group "mail" is allowed to execute it. The third "x" means that every other user is also allowed to execute it.

What you see here for mailx is the default, but some security aficionados tamper with these filemodes and set it to

# ls -l /bin/mailx
----------    3 bin      mail         159650 Jan 22 2016  /bin/mailx

That means nobody is allowed to do anything (read, write or execute) with this file, not even its owner. It might be that someone deemed mailx or uuecode so dangerous that the only way to deal with this threat was to flag it that way. I have seen such things.

To make it work again you simply need (as root ) to reinstate its original filemodes:

# chmod 755 /bin/mailx
# ls -l /bin/mailx
-rwxr-xr-x    3 bin      mail         159650 Jan 22 2016  /bin/mailx

I hope this helps.

bakunin

rbatte1 · November 7, 2017, 5:51am

Would a neater way be to test if the file is null or not? Does it really depend if you file has data or not? If you file does not have separate records and/or one at the end, you might still get the record count of zero, for example:-

$ printf "Hello unix.com" | wc -l
0
$

If you just want to test for the file size being zero or not, you could try:-

if [ -s "$FILE" ] ; then .......

This statement is to check "If $FILE exists and has a size greater than zero, then ....."

You might also want to check if the file can be read first with a similar statement, but using the -r test operator. If you need to update the file when your processing is complete, you can check if you can write to it with the -w operator. Have a look and man test for the other options.

Just my thoughts. I hope that this helps,
Robin

vasuvv · November 8, 2017, 2:53am

Can you guide me how to schedule the script which will run between 12 to 3:30?

then only it has to count the data from the file.. please advise.

bakunin · November 8, 2017, 8:45am

I already told you in post #2: use a crontab entry. You can easily find out how to do that by reading the man page of cron and/or crontab respectively. I already told you so in post #2 too.

Could you - instead of asking repeatedly the same question - actually do what is suggested?

I hope this helps.

bakunin

vasuvv · November 9, 2017, 1:48am


#!/bin/ksh


to = "test@gmail.com"
from = "robin@gmail.com" 
subject= "mesg is mailing" 


DB_DECRYPTED_PASSWORD='xyts'; --Need advise to hide the password
DB_CONNECTION_STRING="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=$DB_PORT))(CONNECT_DATA=(SERVICE_NAME=$DB_SID)))"
RERUN=$5
PATH=$PATH:$ORACLE_HOME/bin

echo "---------------------------------------------------"
echo  ENVIRONMENT = $ENV;
echo  DB_HOST =  $DB_HOST
echo  DB_PORT =  $DB_PORT  
echo  DB_USER =  $DB_USER
#echo  DB_PASSWORD =  $DB_PASSWORD
#echo  DB_DECRYPTED_PASSWORD =  $DB_DECRYPTED_PASSWORD
echo  DB_SID =  $DB_SID
echo  DB_CONNECTION_STRING = $DB_CONNECTION_STRING
echo "---------------------------------------------------"  


##################################################


FILE="/usr/jobs/emp_details.txt"
#FILE="emp_details.$(date +%F_%R).txt"
#touch $FILE

echo $ORACLE_HOME/bin/sqlplus
echo "======== START ============="

#$ORACLE_HOME/bin/sqlplus -s scott/tiger@ABC  <<EOF
RESULT=`$ORACLE_HOME/bin/sqlplus  -silent ${DB_USER}/${DB_DECRYPTED_PASSWORD}@${DB_CONNECTION_STRING} <<ENDA
 
 while true
  do
  15 12 * * * /usr/jobs/emp_details.txt
  30 15 * * * /usr/jobs/emp_details.txt
  sleep 900
  done
  
 set pagesize 0 feedback off verify off heading off echo off
column last_business_date format date
<SELECT QUERY> ;
exit;
ENDA`

echo "$RESULT" > $FILE
sed '/#^/G' $FILE
echo "======== END ============="

## READ AND WRITE THE FILE AND TOOK THE BACKUP
 while IFS= read -r line
 do
echo "$line" >> "emp_details.backup.$(date +%F_%R).txt"
done < "$FILE"

##this will fetch the number of lines in the file

if [ $(wc -l < "$FILE") -gt 0 ]; then

cat <<EOF; mailx -s "$subject" -r "$from"  "$to" < "/usr/jobs/emp_details.txt"

fi

suggest to hide the password and correct the code.

Thanks.

Don_Cragun · November 9, 2017, 3:51am

Your code does not match your stated requirements.

Since we do not have your environment, your databases, and still( after asking multiple times) do not even know what operating system you're using, it is difficult to correct your code when we can't see the errors you're getting.

All that we can do to help you is to ask questions that might help us help you fix your code. But, since you refuse to answer most of our questions, I have no confidence there is much we can do to help you:

What operating system are you using?
What errors are you getting when you run the code shown in post #18?
In post #12 you say you want a backup file named EMP_FILE_bkp.txt , your code in post #18 produces a backup filename of the form emp_details.backup.YYYY-MM-DD_HH:MM.txt . Why the discrepancy in your requirements?
Why do you have a cat in your script reading from an unterminated here-document?
You need to submit crontab entries to cron 's scheduler using the crontab utility. The Korn shell can't execute crontab entries!
Please explain what you are trying to do with the command: sed '/#^/G' $FILE . The BRE in that address can't match any lines???
Why are you still using wc instead of a test -s to determine whether or not you have data to send in a mail message?

You can choose to answer the above questions in your next post, or we can choose to close this thread. Please help us help you by answering the questions above.

vasuvv · November 9, 2017, 4:35am

What operating system are you using?

 LINUX

What errors are you getting when you run the code shown in post #18?

 not getting any errors but Unable to send Mail with attachement and unable to schedule the job to run in given time intervals.Tried with CRON as am new to these written code wrongly.

In post #12 you say you want a backup file named EMP_FILE_bkp.txt, your code in post #18 produces a backup filename of the form emp_details.backup.YYYY-MM-DD_HH:MM.txt. Why the discrepancy in your requirements?

 have modified the code to create a backup file the the given format and the file is fetting generated without any issues'

Why do you have a cat in your script reading from an unterminated here-document?

as gone through the google search gave to see the content of the file. Am sure Its not required.

You need to submit crontab entries to cron's scheduler using the crontab utility. The Korn shell can't execute crontab entries!

as I have lack of knowledge after googling I gave this nut am not sure how it will work. it would be great if I get from you to write the script properly.

Please explain what you are trying to do with the command: sed '/#^/G'
$FILE. The BRE in that address can't match any lines???

I am not sure what this command will do..

Why are you still using wc instead of a test -s to determine whether or not you have data to send in a mail message?

Here Again posting my requirement.

[code]

Have SQL query. The query should run every day between 12 pm - 3PM for every 15 minutes.
within thise duration it has to create backup files.
If the query returns any data then need to send a mail with that output file as attachment else no need to send a mail.

If would be great if I get complete script as I tried but failing in writing the script.

Thanks,
Vasu