SQLPLUS Question
in [Oracle]
|
From: Mladen Gogala on 16 Jan 2010 18:15 On Sat, 16 Jan 2010 23:08:18 +0100, Gerard H. Pille wrote: > Everyone knows you know a bit about it (Oracle). You know near to > nothing about ksh. OK. You write me a script doing something related to Oracle in ksh and I will write the same script in Perl. Let's compare the execution times and the memory footprint. Also, as a minimum security measure, let's pack the passwords in hexadecimal form, so that they're not visible from the scripts at first glance. This is what I have in mind: [mgogala(a)medo ~]$ perl -e 'print unpack("H*","scott/tiger"),"\n";' 73636f74742f7469676572 [mgogala(a)medo ~]$ If your script is faster and more secure than mine, I will publicly apologize. If, on the other hand, my script performs better than yours, you will publicly apologize to me in this forum. That's a fair bet, will you take it? We both will have the fullest freedom of expression in writing the script. I will accept Jonathan Lewis, Tanel Poder, Nial Litchfield or Nuno Suoto as judges in this matter. Let me specify things a bit further: You create a data model and the script to populate the tables. You define the task and create a ksh script to do the task. I will write the same thing using Perl and will not change the data model by adding indexes or triggers, clustering tables or doing anything that would be incompatible with the existing data model. I will have the full freedom to add PL/SQL objects, specifically, functions, procedures and packages but not triggers. Username and password combination must not be hard coded as an ASCII text in the script. In other words, things like "connect scott/tiger(a)local" are not allowed. What say you? -- http://mgogala.freehostia.com
From: Mladen Gogala on 16 Jan 2010 18:17 On Sat, 16 Jan 2010 20:37:15 +0000, Jeremy wrote: > All of the above may well be true, however if you are just trying to > automate a simple task which involves running a specific set (depending > on some conditions in the database for example) of .sql files on demand > by the DBA then this may well be the simplest way of achieving the > objective. Running sql files on demmand? That's usually done by sqlplus. I understood that we're talking about scripting. -- http://mgogala.freehostia.com
From: Mladen Gogala on 16 Jan 2010 18:19 On Sat, 16 Jan 2010 20:24:36 +0000, Jeremy wrote: > 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? Yes. That is precisely what PL/SQL is intended for: you run SQL and follow some logic path depending on the outcome. -- http://mgogala.freehostia.com
From: Galen Boyer on 16 Jan 2010 18:50 "Gerard H. Pille" <ghp(a)skynet.be> writes: > 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. 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(a)netfront.net ---
From: Mladen Gogala on 16 Jan 2010 18:53
On Sat, 16 Jan 2010 18:50:15 -0500, Galen Boyer wrote: >> This script is run as oracle, so no passwords needed. > > Why do you think this is a good idea? I confess to have missed this pearl of wisdom. -- http://mgogala.freehostia.com |