While researching about Oracle Command Line parameters, I stumbled on the following question:
The OP is asking how to silence SQL*Plus, so he/she could turn off all the prompts coming out in the log. The simple answer is, using command line option -S, like this:
sqlplus -s user/password@db @script
Of course, there are more commandline options available. See here for the details.
I notice a couple of things in this post. The question is simple and the answer is not so intuitive. SQL*Plus has so many SETs for everything else. Why not SET silent ON or something? Instead, they have a command line option -S to silence the tool. Even HTML mode is allowed both ways -M or SET MARKUP! Talk about being consistent (or not!).
SET ECHO OFF seems to be relevant, but it has a different meaning. It only turns off echoing the SQLs. The output still comes out with SQL prompt. The effect of this is more visible, if you run SQL*Plus in batch mode.
There is another setting called FEEDBACK. This also sounds logical for silencing SQL*Plus. This one actually only turns off the feedback in the result, like “1 row selected”.
So, there is no way to completely silence SQL*Plus using only SETs.
Coming back to the post again, I find a few things worth mentioning here:
See the script has a “/” at the end. This will make the SQL(s) above to run again. In fact, Sybrand Bakker mentions this in his answers, that was completely ignored. OP seems knowledgeable, so it could be a slip. I hope the OP noted and corrected it. Though, it doesn’t do any harm here (imagine this was a DML or a DDL, then the effect may be more profound), the SELECT will be executed twice. What if it was a long running query? Do you see the resources being wasted in the duplicate run?
The other thing that bothered me with this is the suggestion to turn off SQLPROMPT, thus:
set sqlprompt ' '
Though it seems logical, it only removes the prompt “SQL>”!! I hope this poster is not using such to somehow “clean” the output. It definitely won’t leave out the banner, etc. I hope the poster did not write a script to remove those. The reason I am pointing this out, I’ve seen such scripts at work before!! Try to use the language/tool feature as much as possible.
Filed under: CodeProject, Misc, Oracle, Scripting
Tagged: Oracle Database, SQL, SQL*Plus 
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!
Submit an article or tip
