CODEWITHSUNDEEP
linuxoracle-databaseshellconditional-statements
JCDrew90
JCDrew90

Reputation: 59

Writing a Shell script

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

Upvotes: 0

Views: 74

Answers (1)

chepner
chepner

Reputation: 531948

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

Upvotes: 2

Related Questions