Re: SQLPLUS Question
Date: Sat, 16 Jan 2010 18:50:15 -0500
Message-ID: <uzl4dwqx4.fsf_at_www.yahoo.com>
"Gerard H. Pille" <ghp_at_skynet.be> writes:
> Jeremy schreef:
>> In article<4b5212b2$0$2864$ba620e4c_at_news.skynet.be>, ghp_at_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.
The word start is just not correct. You call sqlplus with sql and when it is done, you then have output. To say you "start one sqlplus" for each database gives the idea that you can use that sqlplus session anywhere in your program, which is just plain not true.
> 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.
So, you don't understand why this is an issue. Hm...
> 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.
What the heck does "Watch out for unwanted line breaks" mean? Where does this problem surface and how come you couldn't make sure you did not have to "warn" people of it?
> This script is run as oracle, so no passwords needed.
Why do you think this is a good idea?
> Otherwise you use environment variables to pass sensitive information.
>
> all_db_stats.sh
>
> # sleep seconds for measuring current activity
> GSWAIT=900
[...]
> 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
You think that is what you should be doing with ksh? What you are writing is written well, formatted nicely, uses some functions, has a case statement, seems to have been thought through, but its not what you should have chosen to do. Its cryptic, it has way too much embedded SQL where you are dynamically putting it together. Bad idea.
What do you do if any of the many SQL statements actually errors? I guess you'll say, "Its just selects so it does not matter", but your argument is that ksh is a GREAT environment. What if your many SQL statements were dml operations? What would you do? How would you rollback from ksh? You'd have to open up a sqlplus session per block of code that might need to be run back.
Learn the HERE functionality. Your code will be much more maintainable and readable. Then, after you learn that, reread Mladen's words, because they still apply.
Do you think what you wrote is easy to read? Why would you chose to write piles of SQL that needs newlines and quotation marks around it instead of creating a procedure and calling the procedure from KSH which would then have straight SQL in it? Dynamically put together SQL in client code sucks pretty bad when it comes from java and other programming environments, but it really really sucks when it comes from ksh and its ilk.
How is anybody supposed to test your sql without running your entire script? Where is the "don't run this, just show me the sql" switch? That should be there, for sure.
Where is the command-line switch to change the sleep time?
Where is the check whether sqlplus is actually available?
Why do you think ksh is actually a good way to code this kind of thing over perl or php or some actual programming environment? ksh doesn't allow you to create a connection that then can be accessed and used throughout the life of the program, or even way more powerful yet, throughout the life of the server supporting the program. Instead, ksh makes you create a single child sqlplus process and send it sql all at the same time. If you want to do some SQL later on in your programming logic, you have to call sqlplus again. Because of that it forces you to do things like you've done which is put piles and piles of "print -p" statements so that all of the SQL you want to run is sent at one time to the sqlplus session. If you could create a connection and use it, you could call that connection within each of you case statement switches. Your code would be way way way more tight.
Do you honestly believe
ps -ef | grep ora_dbw0 | grep -v 'grep ora_dbw0' \
| awk '{print substr($NF,10)}' | while read ORACLE_SID
is desirable code to have to maintain?
OR this is?
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
-- Galen Boyer --- news://freenews.netfront.net/ - complaints: news_at_netfront.net ---Received on Sat Jan 16 2010 - 17:50:15 CST