while loop logic

Shell Programming and Scripting
1

Hi,
Here I am trying to query database and check a value, if the value not matches then I wants to re-query the database.Once the value matches, I want to email the reqidstatus_log.txt file. Database query produces a file reqidstatus_log.txt which contains result.

But the query not working as per my expectations. Need your help on this ....

OS: Solaris Sparc 10
Shell Type: ksh
###############

while grep -i "no rows selected" reqidstatus_log.txt
do
  sleep 30
  sqlplus -s apps/<PASSWORD> << ENDOFSQL
    define reqid=107019657;
    spool reqidstatus_log.txt
    select REQUEST_ID,PHASE_CODE,STATUS_CODE
    from fnd_conc_req_summary_v where request_id='1070196571' and phase_code='C';
ENDOFSQL
done
uuencode reqidstatus_log.txt reqidstatus_log.txt | mailx -s "reqidstatus_log.txt" xyz@abc.com

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

Thanks,
Raj

2

Can you explain your issue? It is really not clear to me... ( I understand ""seems to work... but not always ..." )

3

I am trying achive the following:
In the database one request will be running. That request runs for long time.
So instead of keep on checking it manually whether request is completed or not, I want shell script to check the request status (say for example every 1 minute) and if the request is completed then shell script should email me the request status ( that is it should email me the file generated by query, in this case it should email me file .txt file mentioned in the shell script).

The issues are :
1) its unnecessary keep on printing output on Standard output following text until condition is met.

no rows selected

1

2) Even if the request is completed and its phase_code is "C" (C- for completion).
my mentioned shell script keep on print about give ( no rows select and 1) to screen.

So the logic I used in the shell script me may be incorrect and I have to use FOR loop instead of this .... You your help.

Thanks,
Raj

4

I may sound silly, but if all you are interested in is knowing the SQL query has finished, why not let the query tell you?(If its being called in a script...)

About your code:
You test :

while grep -i "no rows selected" reqidstatus_log.txt

will always be true... (You read always the same file that has the condition when you start...
Since we dont know how it is generated (one line every minute?) or his content, it will be difficult to suggest something... but perhaps try to use tail command to read X last lines of file, which may help the condition to change (X one to ?? lines?)