Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Retrieving a Table count from sqlplus back into kornshell

Re: Retrieving a Table count from sqlplus back into kornshell

From: Kirt Thomas <kremovethisspamthingthomas_at_gfsiinc.com>
Date: Mon, 23 Jul 2001 07:34:46 -0500
Message-ID: <g86olts96oqj9vjdfd7ov6ih6t9qrg0lfr@4ax.com>

I do it this way, it's easier, and there's no pesky log file hanging around...

#!/bin/ksh
somevar="`sqlplus -s<<EOF
sys/$1
set heading off
set feedback off
set verify off
set echo off
select count(1)
from somedamntable
where somecolumn like 'SOMETHING%';
exit
EOF`" echo $somevar

#end of snippet

This get's you in and out with the result of the sql and can be used in many different ways to get things into a script.

On Sat, 21 Jul 2001 14:49:27 GMT, lfc_at_zoom.co.uk (Leonard F Clark) wrote:

>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 Mon Jul 23 2001 - 07:34:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US