Re: Retrieving a Table count from sqlplus back into kornshell

From: Leonard F Clark <lfc_at_zoom.co.uk>
Date: Sat, 21 Jul 2001 14:49:27 GMT
Message-ID: <3b599384.12600478@192.168.0.1>

Rinkan,

If I understand your requirement properly, you can do it something like this:

---

#!/bin/ksh

# Comment script
# .
# .
# .

LOG_FILE=/..../logs

    .
    .   other environment variables
    .

# Ensure your user password is set

if [ $# -lt 1 ]
then
  # You haven't passed a password as a parameter   echo "Enter the password for fred:"
  stty -echo > /dev/null # Turn off character echoing   read USER_PWD
  stty echo > /dev/null # Turn it back on else
  USER_PWD=$1
fi

# You could check that works separately
# Now do the script

sqlplus << EOS
fred/${USER_PWD}

   spool ${LOG_FILE}

   select '+ ' || count(*) from YOUR_TABLE;

   delete from YOUR_TABLE where ....;

# The delete tells how many rows are deleted but, if you want:

  select '- ' || count(*) from YOUR_TABLE;

# If you want to here:

rollback;

# You don't have to issue an explicit commit because it will
# automatically commit when you finish - assuming no errors.

EOS
# Now you can select, for example:

BEFORE = `echo $LOG_FILE} | grep "+" | awk '{ print $2 }'` AFTER = `echo ${LOG_FILE} | grep "-" | awk '{ print $2 }'` if [ ${BEFORE} -eq ${AFTER} ]
then
  echo "No rows were removed"
fi

---

Etc. Don't take my word for any of this: it's all off the top of my head.

Alternatively, you can send the before and after results to a table and read everything from there.

>Hi:
> 
>I am new to scripting in the UNIX environment and I am having a
>problem figuring out a way that I can compare counts from a Oracle
>table, before a purge and after a purge.  I want to capture the count
>from sqlplus in a UNIX variable, where I can do the compare on the
>UNIX side.  Is this Possible and if so how?
>Also, Question 2: If I return to UNIX while connected with a session
>open will my session on Oracle end, because I have to either COMMIT or
>ROLLBACK the transaction depending on the count comparison??  Is there
>a better way to do this, please let me know.
>
>Thank you,
>Rinkan Patel
>rinkan786_at_yahoo.com

Received on Sat Jul 21 2001 - 09:49:27 CDT