Ok, so I’ve been on a streak to NOT talk about Oracle for a little while now. Actually I tried to avoid it, but as this software is more.. hmm… complex than MS-SQL we must talk about it a lot more, to spread more knowledge about it. Yeah thats it, lets talk more about Oracle.
First tip today is how to save the output of your select statement if you are just making an ad-hoc query to check a few stuff on your server. You know, you will not always have a workstation available to install a lot of clients, configure some environment variables, and type in your server IP on an obscure TNSNAMES.ORA file, for example. There are times when all you have is a terminal connection and SQLPLUS on the other side. And if your select will return more than a couple of lines, you can’t just “select and copy” the text. So do these steps:
- open the terminal client you like (I use Putty);
- log-in to linux and then oracle as usual and open SQLPLUS;
- issue the command SPOOL and give it a file name. at linux “spool /tmp/test.txt” or at windows “spool c:\temp\test.txt“;
- do your select statements and when you are done, type “spool off“;
- save the file using windows explorer/winSCP or whatever.
Yeah, that’s a pretty long instruction list to just save a few lines. And as everything in Linux/Oracle world, you have a dozen other options to do the same thing, this is just the one I believe is the easiest. Thanks to the guys at stackoverflow for the hints.
PS: if the resulting text file looks ugly on notepad, open it in SSMS and then copy-paste it to where you want…
UPDATE: according to this link, from 10g and above it is possible to append the content of the spool file, just add “app” (without quotes) at the end of command.