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?

--
jeremy

"Jeremy" <jeremy0505 (AT) gmail (DOT) com> a écrit dans le message de news: MPG.25bc17bc8c44da459897ca (AT) News...Individual.NET...
Yes but not so simply that a "if then else".

Regards
Michel

Jeremy schreef:
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.

On Sat, 16 Jan 2010 18:37:24 +0000, Jeremy wrote:


Yes. It's called PL/SQL and is available as of the version 6.



--
http://mgogala.freehostia.com

In article <hit6ej$phc$4 (AT) solani (DOT) org>, gogala.mladen (AT) gmail (DOT) com says...>
Is that intended to be serious answer?


--
jeremy

In article <4b5212b2$0$2864$ba620e4c (AT) news (DOT) skynet.be>, ghp (AT) skynet (DOT) be
says...>

Would this be through multiple invocations of sqlplus?

--
jeremy

Mladen Gogala schreef:
Please, Mladen Gogala, why don't you shut up about things you know
nothing about?

On Sat, 16 Jan 2010 22:39:24 +0100, Gerard H. Pille wrote:


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.



--
http://mgogala.freehostia.com

Jeremy schreef:
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' \
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

Mladen Gogala <gogala.mladen (AT) gmail (DOT) com> writes:

What Mladen stated is true.

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---