Ora201:Saving Queries and Results

Main Page >> Oracle and SQL >> Workbook >> Saving Queries and Results

Saving Query Output

At some point you will want to save the results of a query in a file, as well as display them on screen.

To send the results of a query to a file use the SPOOL command.

The format is:

SPOOL [drive_name:\]filename

All information that is displayed on the screen after issuing the SPOOL command will also be written to the specified file. The suffix .lst will be added to the specified filename to identify it as a listing file. You can add a different suffix if required, e.g., filename.txt to denote a text file. This can be useful if importing into Word or Excel so the package will automatically know the file type.

SQL*PLUS will continue to spool information to the file until the spooling is turned of by the following command:

SPOOL OFF

For example, if running from a University PC, type in the following (press return after each line):

SPOOL u:\emplist.txt
SELECT * FROM EMP;
SPOOL OFF

The file emplist.txt will be saved in your Documents folder (Windows). See if you can find it.

If using mi-linux, you can leave out the drive_name:

SPOOL deptlist.txt
SELECT * FROM DEPT;
SPOOL OFF

Which will save the file in the current directory in Linux.

Don't forget that filenames are case sensitive in Linux, so deptlist.txt is different to deptList.txt. You can use a Secure FTP client to transfer the file to a PC, such as FileZilla.

Exercise 2.1

To do:

• Write an SQL command that lists all the departments, saving the the output to a file called: output.txt
  • Do not forget to use SPOOL OFF at the end
• Look on your computer for a file called: output.txt
• Use a text editor, such as Notepad, Notepad++ or vi, to look at the contents of output.txt.

Next Step

Saving and Retrieving queries.