Oracle2:Saving Queries and Results

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

Storing 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 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 type in the following (press return after each line):

SPOOL emplist.txt
SELECT * FROM EMP;
SPOOL OFF

The file emplist.txt will be saved in your Documents folder, see if you can find it.

Exercise 2.1

To do (assuming the results are saved on a Windows machine):

• Write an SQL command that lists all the departments
• Amend the command so that the output is stored in a file called 'Output.txt'
• Look on the U: drive for a file called 'Output.txt'
• Use Wordpad or Notepad to look at the contents of 'Output.txt'