From: Gerard H. Pille on
Jeremy schreef:
> In article<4b5212b2$0$2864$ba620e4c(a)news.skynet.be>, ghp(a)skynet.be
> says...>
>> Jeremy schreef:
>>> Hi, real simple question and one I suspect the answer to which is "no".
>>>
>>> Can you create sqlplus scripts with "conditions" such that if for
>>> example a SQL statement returns a particular value or error condition
>>> then path A or path B is followed?
>>>
>>
>> I start sqlplus from a ksh script as a job, send queries to it and read
>> the answers. ksh, which is also a full programming language, takes the
>> decisions about how to continue.
>
>
> Would this be through multiple invocations of sqlplus?
>

It certainly isn't. You start one sqlplus for each database you want to
connect to. Ksh can do a perfect job control. I'll show you an
example. Don't listen to Gogala's rantings, if a machine considers
activating a program an expensive operation, it's probably on some
�icro$oft non-OS and certainly shouldn't be running a database. Gogala
should brush up his knowledge of ksh, he must be talking about some pre
93 version or bsh, if he doesn't know that nowadays ksh hardly ever
needs sed, ls or grep. And if I'm not mistaken, sqlplus hides the
command line arguments it's been given, for some time now.

Here's an example of how I collect some database information (using a
pre 93 ksh ;-). Watch out for some unwantend line breaks. This script
is run as oracle, so no passwords needed. Otherwise you use environment
variables to pass sensitive information.

all_db_stats.sh

# sleep seconds for measuring current activity
GSWAIT=900

Usage(){
echo "Usage: $0 HOSTNAME"
if [ ! -z "$1" ]
then
echo "$1"
fi
exit $2
}

FlushPipe(){
#print -p "select 'AMEHOELA' from dual;"
print -p "prompt AMEHOELA"
while read -p
do
if [ "$REPLY" = "AMEHOELA" ]
then
break
else
if [ "$1" = "SAVE" ]
then
GSFEEDBACK[${#GSFEEDBACK[*]}]="$REPLY"
fi
fi
done
}

GetStats(){
GSHOST="$1"
GSDB="$2"
echo "Connecting to $GSDB as sysdba"

sqlplus -s /nolog |&
print -p "spool /tmp/gastat_${GSHOST}_${GSDB}"
print -p "connect / as sysdba"
print -p "set echo off"
print -p "set pages 0"

# newer versions of sqlplus no longer report "Connected."
unset GSFEEDBACK
set -A GSFEEDBACK
FlushPipe SAVE

if [ "$GSFEEDBACK" = "Connected." -o -z "$GSFEEDBACK" ]
then
print -p "set feedback off"
print -p "set lines 80"
print -p "set numwidth 17"
print -p "set termout off"
print -p "set trimout on"
print -p "column R format 999999999999999"
print -p "column W format 999999999999999"
print -p "column D format a20"
print -p "spool /tmp/gastat_stats_${GSHOST}_${GSDB}"

print -p "select version from v\$instance;"
unset GSFEEDBACK
FlushPipe SAVE
GSVERSION=${GSFEEDBACK}

# try to estimate database activity
case "$GSVERSION" in
10.2.*)
print -p "select" \
" (select value from v\$sysstat" \
" where name = 'physical read bytes') R," \
" (select value from v\$sysstat" \
" where name = 'physical write bytes') W," \
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from dual;"
sleep $GSWAIT
print -p "select" \
" (select value from v\$sysstat" \
" where name = 'physical read bytes') R," \
" (select value from v\$sysstat" \
" where name = 'physical write bytes') W," \
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from dual;"
;;
*)
print -p "select" \
" sum(s.PHYBLKRD * f.block_size) R, "\
" sum(s.PHYBLKWRT * f.block_size) W, "\
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from v\$datafile f, v\$filestat s" \
" where s.file# = f.file#;"
sleep $GSWAIT
print -p "select" \
" sum(s.PHYBLKRD * f.block_size) R, "\
" sum(s.PHYBLKWRT * f.block_size) W, "\
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from v\$datafile f, v\$filestat s" \
" where s.file# = f.file#;"



print -p "SELECT" \
" Avg(BYTES) AVG#," \
" Count(1) Count#," \
" Max(BYTES) Max_Bytes," \
" Min(BYTES) Min_Bytes" \
" FROM" \
" v\$log;"

# how much logging the previous five working days?
print -p "SELECT A.Count#, Round(A.Count#*B.AVG#/1024/1024/5)" \
" Daily_Avg_Mb FROM (" \
" SELECT count(*) Count#" \
" FROM v\$log_history" \
" where first_time between" \
" next_day(trunc(sysdate),'MONDAY') - 14" \
" and next_day(trunc(sysdate),'MONDAY') - 9" \
" ) A," \
" ( SELECT Avg(BYTES) AVG# FROM v\$log) B;"

unset GSFEEDBACK
FlushPipe SAVE
X=0
while [ $X -lt ${#GSFEEDBACK[*]} ]
do
echo "Stats $X : ${GSFEEDBACK[$X]}"
(( X += 1 ))
done
GSREADBYTES1=$(expr "${GSFEEDBACK[0]}" : "\([0-9]*\)")
GSREADBYTES2=$(expr "${GSFEEDBACK[1]}" : "\([0-9]*\)")
GSWRITEBYTES1=$(expr "${GSFEEDBACK[0]}" : "[0-9]*[ ]*\([0-9]*\)")
GSWRITEBYTES2=$(expr "${GSFEEDBACK[1]}" : "[0-9]*[ ]*\([0-9]*\)")
GSLOGMEGS=$(expr "${GSFEEDBACK[3]}" : "[0-9]*[ ]*\([0-9]*\)")
if [ $GSREADBYTES1 -eq $GSREADBYTES2 -a $GSWRITEBYTES1 -eq
$GSWRITEBYTES2 ]
then
GSNOCALC="YES"
else
GSNOCALC="NO"
fi


print -p "select" \
" instance_name" \
" || chr(10) || host_name" \
" || chr(10) || '$(uname -a)'" \
" || chr(10) || 'Oracle'" \
" || chr(10) || version" \
" || chr(10) || 'New'" \
" || chr(10) || 'No'" \
" || chr(10) || 'No'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || " \
" (select round(sum(bytes)/1000000000)" \
" from (" \
" select bytes from dba_data_files" \
" union all" \
" select bytes from dba_temp_files" \
" )" \
" )" \
" || chr(10) || " \
" (select round(sum(bytes)/1000000)" \
" from v\$log" \
" )" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'No'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || " \
" decode(" \
" '$GSNOCALC','YES',0," \
" round( ($GSREADBYTES2-$GSREADBYTES1) * 100" \
" / ( ($GSREADBYTES2-$GSREADBYTES1)" \
" + ($GSWRITEBYTES2-$GSWRITEBYTES1) ) ) )" \
" || chr(10) || 'N/A Random I/O (%)'" \
" || chr(10) || " \
" ($GSREADBYTES2-$GSREADBYTES1)/$GSWAIT/1048576" \
" || chr(10) || " \
" ($GSWRITEBYTES2-$GSWRITEBYTES1)/$GSWAIT/1048576" \
" || chr(10) || $GSLOGMEGS" \
" from v\$instance;"

FlushPipe

else
X=0
while [ $X -lt ${#GSFEEDBACK[*]} ]
do
echo "${GSFEEDBACK[$X]}"
(( X += 1 ))
done
fi

print -p "exit;"
wait
}



# Gather info from each running Oracle instance on this system
export ORACLE_SID PATH

if [ -z "$1" ]
then
Usage "No hostname given (needed for file names)" 1
fi

ps -ef | grep ora_dbw0 | grep -v 'grep ora_dbw0' \
| awk '{print substr($NF,10)}' | while read ORACLE_SID
do
PATH=/usr/lbin:/usr/bin:/usr/sbin:/softw/app/oracle/bin:/usr/local/bin
ORAENV_ASK=NO . oraenv
echo $ORACLE_HOME
GetStats $1 $ORACLE_SID &
done
wait
From: Gerard H. Pille on
Jeremy schreef:
> In article<hit6ej$phc$4(a)solani.org>, gogala.mladen(a)gmail.com says...>
>> On Sat, 16 Jan 2010 18:37:24 +0000, Jeremy wrote:
>>
>>
>>> Can you create sqlplus scripts with "conditions" such that if for
>>> example a SQL statement returns a particular value or error condition
>>> then path A or path B is followed?
>>
>> Yes. It's called PL/SQL and is available as of the version 6.
>
> Is that intended to be serious answer?
>
>

Ah, sorry to have underestimated you, Jeremy, but you must be brighter
than you look, to see through him.
From: Galen Boyer on
Mladen Gogala <gogala.mladen(a)gmail.com> writes:

> On Sat, 16 Jan 2010 22:39:24 +0100, Gerard H. Pille wrote:
>
>
>> Please, Mladen Gogala, why don't you shut up about things you know
>> nothing about?
>
> Because I've written many scripts using ksh, bash and DCL as well Perl
> and PHP and because I actually know a bit about Oracle.

What Mladen stated is true.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Gerard H. Pille on
Mladen Gogala schreef:
> On Sat, 16 Jan 2010 22:39:24 +0100, Gerard H. Pille wrote:
>
>
>> Please, Mladen Gogala, why don't you shut up about things you know
>> nothing about?
>
> Because I've written many scripts using ksh, bash and DCL as well Perl
> and PHP and because I actually know a bit about Oracle.
>
>
>

Everyone knows you know a bit about it (Oracle). You know near to
nothing about ksh.
From: Gerard H. Pille on
Gerard H. Pille schreef:
> Jeremy schreef:
>> In article<hit6ej$phc$4(a)solani.org>, gogala.mladen(a)gmail.com says...>
>>> On Sat, 16 Jan 2010 18:37:24 +0000, Jeremy wrote:
>>>
>>>
>>>> Can you create sqlplus scripts with "conditions" such that if for
>>>> example a SQL statement returns a particular value or error condition
>>>> then path A or path B is followed?
>>>
>>> Yes. It's called PL/SQL and is available as of the version 6.
>>
>> Is that intended to be serious answer?
>>
>>
>
> Ah, sorry to have underestimated you, Jeremy, but you must be brighter
> than you look, to see through him.

I have to correct myself, Mladen is right that you can use PL/SQL inside
an sqlplus script. It could very well be that that is enough to solve
your problem.