Writing a Shell script

Question

I'm trying to write a shell script that will essentially query a given condition. Here's the catch. I want it to repeat the query for a duration of 3 minutes. (probably run the query, and sleep for 2 seconds)

After 1 minute, at any time IF the query returns null, the for loop will break. (the main objective is to DETECT if the query returns the results consistently for a duration of 3 minutes)

How do I incorporate the checking for the BREAK statement in the below code, after 1 minute? (does SPOOL overwrite contents of files or does it append?)

for ((i=90; i > 1 ; i--))
    do
    sqlplus -s username/passwd@SERVICENAME <<EOF
    SET PAGESIZE 50000
    SET TERM OFF
    SET HEAD OFF
    SET LINESIZE 200
    #SET TRIMSPOOL ON
    SET FEEDBACK OFF

    SPOOL /appl/odd/local/bin/notification_pending.txt
    select awb,rcvr_phone,rcvr_email,latest_event,latest_event_dtm,status,contact,notify_method from shipment t,notification t2 where t.id=t2.shp_id
    and t2.status like 'F%'
    and t2.contact not in ('Recipient not in whitelist','Invalid Email Id','Mail Service Down','Invalid Mobile Number');

    SPOOL OFF
    exit
    /
    EOF
    sleep 2
done

why not do this all in a stored package or procedure and call that? — kevinskio, Aug 17 '15 at 16:07
see http://stackoverflow.com/questions/27971833/in-bash-heredoc-inside-function-returns-syntax-error for some ideas. note the `dbname=$(...)` construct. Good luck. — shellter, Aug 17 '15 at 16:29
@kevinsky, I am not using stored procedure because I believe in shell scripting, it will not be able to SPOOL the results out to a file and mail it as a notification. — JCDrew90, Aug 17 '15 at 16:39
@shelter, I'm familiar with the syntax and have no problem with it, but Im just not sure about writing the OTHER For Loop (or while loop) that checks and breaks off if a null result is returned, after X tries. — JCDrew90, Aug 17 '15 at 16:39

chepner · Accepted Answer · 2015-08-18T13:10:34.740

The simplest thing to do is capture the output of sqlplus, then test if the resulting string is empty or not. For readability, I'm putting the call to sqlplus in a function. Given the form of the for statement you are using, I'm also assuming that you are using bash.

run_query () {
sqlplus -s username/passwd@SERVICENAME <<EOF
# [deleted]
EOF
}

# SECONDS is incremented each second, so can be used as
# a simple timer.
SECONDS=0

# For the first minute, just run the query
while (( SECONDS <= 60 )); do
    output=$(run_query)
    sleep 2
done

# After the first minute, continue running the query for the
# next two minutes, but quit if the query produces no output.
while (( SECONDS <= 180 )); do
    output=$(run_query)
    if [[ -z $output ]]; then
        break
    fi
    sleep 2
done

Or, you can combine the two loops and use a slightly more complicated conditional inside:

while (( SECONDS <= 180 )); do
    output=$(run_query)
    # Don't break for any reason during the first 60 seconds
    if ((SECONDS > 60)) && [[ -z $output ]]; then
        break
    fi
    sleep 2
done

If you aren't using bash, you can simulate the timer with calls to date:

start=$(date +%s)
while now=$(date +%s); SECONDS=$(( now - start)); [ "$SECONDS" -le 180 ]; do
    output=$(run_query)
    if [ "$SECONDS" -gt 60 ] || [ -n "$output" ]; then
        break
    fi
    sleep 2
done

Thanks Chepner! Does the -z in the if condition represent if the value is zero? — JCDrew90, Aug 18 '15 at 03:57

Writing a Shell script

1 Answers1