while loop

rajsp217 · 10-19-2011, 08:55 AM

Hi,
OS: Solaris Sparc 10.
===================
VARIABLE=grep "no rows selected" file_name
while (VARIABLE='desired_value')
do
repeat this test
else
email file_name
===================
In the above scenario, how can I take value in to VARIABLE and how while loop can be implemented to above mentioned requirement.

you need suggestions.

Thanks,
Raj

tronayne · 10-19-2011, 09:58 AM

Assuming you're using KornShell,

Code:

#!/bin/ksh
#       let's just use a for...
for file in $(grep -l "no rows selected" path_to_files)
do
        print "no rows selected from ${file}" | mailx -s "No Rows in ${file}" who@where.what
done

Hope this helps some.

rajsp217 · 10-20-2011, 04:39 AM

Quote:

Originally Posted by tronayne

Assuming you're using KornShell,

Code:

#!/bin/ksh
#       let's just use a for...
for file in $(grep -l "no rows selected" path_to_files)
do
        print "no rows selected from ${file}" | mailx -s "No Rows in ${file}" who@where.what
done

Hope this helps some.

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

When I run given below script, its giving syntex error. any idea why ?

========================================================================================

echo "Script To check request id status"

sqlplus -s apps/<password> << ENDOFSQL
define reqid=$REQID;
spool reqidstatus_log.txt
select REQUEST_ID,PHASE_CODE,STATUS_CODE
from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL

for reqidstatus_log.txt in (grep -l "no rows selected" reqidstatus_log.txt)
do
print "no rows selected from reqidstatus_log.txt" | mailx -s "No Rows in ${file}" who@where.what
done
echo "emailing spoolfiles"
uuencode reqidstatus_log.txt reqidstatus_log.txt | mailx -s "reqidstatus_log.txt" who@where.what
echo "Finished request id status check"
exit
========================================================================================

following is the execution log:

Script To check request id status
DEFINE requires a value following equal sign

no rows selected

reqidstat.sh: syntax error at line 10: `$' unexpected

colucix · 10-20-2011, 05:40 AM

Which is your (default) shell? Here is a /bin/tcsh script:

Code:

#!/bin/tcsh
sqlplus -s apps/<password> << ENDOFSQL
  define reqid=$REQID;
  spool reqidstatus_log.txt
  select REQUEST_ID,PHASE_CODE,STATUS_CODE
  from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL

while ( `grep "no rows selected" reqidstatus_log.txt` != "" )
  sleep 30
  sqlplus -s apps/<password> << ENDOFSQL
    define reqid=$REQID;
    spool reqidstatus_log.txt
    select REQUEST_ID,PHASE_CODE,STATUS_CODE
    from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL
end

# 
#  The sqlplus command is succesfully terminated: send mail notification now!
#
<mail command here>

rajsp217 · 10-20-2011, 06:08 AM

Quote:

Originally Posted by colucix

Which is your (default) shell? Here is a /bin/tcsh script:

Code:

#!/bin/tcsh
sqlplus -s apps/<password> << ENDOFSQL
  define reqid=$REQID;
  spool reqidstatus_log.txt
  select REQUEST_ID,PHASE_CODE,STATUS_CODE
  from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL

while ( `grep "no rows selected" reqidstatus_log.txt` != "" )
  sleep 30
  sqlplus -s apps/<password> << ENDOFSQL
    define reqid=$REQID;
    spool reqidstatus_log.txt
    select REQUEST_ID,PHASE_CODE,STATUS_CODE
    from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL
end

# 
#  The sqlplus command is succesfully terminated: send mail notification now!
#
<mail command here>

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

Default sheel is "ksh"

Thx,
Raju

colucix · 10-20-2011, 06:26 AM

ksh version is almost the same (differences in the while loop syntax):

Code:

#!/bin/ksh
sqlplus -s apps/<password> << ENDOFSQL
  define reqid=$REQID;
  spool reqidstatus_log.txt
  select REQUEST_ID,PHASE_CODE,STATUS_CODE
  from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL

while grep -q "no rows selected" reqidstatus_log.txt
do
  sleep 30
  sqlplus -s apps/<password> << ENDOFSQL
    define reqid=$REQID;
    spool reqidstatus_log.txt
    select REQUEST_ID,PHASE_CODE,STATUS_CODE
    from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL
done

# 
#  The sqlplus command is succesfully terminated: send mail notification now!
#
<mail command here>

David the H. · 10-20-2011, 01:04 PM

Please use [code][/code] tags around your code, to preserve formatting and to improve readability. Don't use quote tags around code either.

Without them, any extra spacing in your scripts is removed, and those long unbroken lines fore the page to side-scroll, making it hard to read and debug. So go back and edit your posts to fix them.

tronayne · 10-20-2011, 03:39 PM

The reason your shell program did not work was this line:

Code:

for reqidstatus_log.txt in (grep -l "no rows selected" reqidstatus_log.txt)

What the for does is assign what grep -l returns (a file name) to a variable, in this case, you said the variable was named reqidstatus_log.txt; oops. It should have been something like file, FILE, FILENAME, FILE_NAME or whatever for later use (I used file in the example). After the assignment, it's referred to as ${file} (or whatever).

Looking at your shell program, I would do this:

Code:

#!/bin/ksh
print "Script To check request id status"
#       execute query with a here-is
sqlplus -s apps/<password> << ENDOFSQL
        define reqid=$REQID;
        spool reqidstatus_log.txt
        select REQUEST_ID,PHASE_CODE,STATUS_CODE
        from fnd_conc_req_summary_v where request_id='106994181' and phase_code='C';
ENDOFSQL
#       query has completed, now check the log
for file in (grep -l "no rows selected" reqidstatus_log.txt)
do
        print "no rows selected from reqidstatus_log.txt" \
                | mailx -s "No Rows in ${file}" who@where.what
done
print "emailing spoolfiles"
uuencode reqidstatus_log.txt reqidstatus_log.txt |\
        mailx -s "reqidstatus_log.txt" who@where.what
print "Finished request id status check"
exit

This presupposes that you're only going to execute this once and that the SQL query is not in a loop; i.e., it's a single-pass, query followed by the e-mailing of the result; it simply looks like that's what you're trying to accomplish. Of course you'd want to replace "who@where.what" with a real e-mail address, but this ought to work for you.

Hope this helps some.