Oracle: save output to file

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:

  1. open the terminal client you like (I use Putty);
  2. log-in to linux and then oracle as usual and open SQLPLUS;
  3. issue the command SPOOL and give it a file name. at linux “spool /tmp/test.txt” or at windows “spool c:\temp\test.txt“;
  4. do your select statements and when you are done, type “spool off“;
  5. 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…

cya!

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.

Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in Linux, Oracle and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s