Difference between revisions of "Ora201:Saving Queries and Results"
| (7 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
[[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|Workbook]] >> Saving Queries and Results | [[Main Page]] >> [[Oracle|Oracle and SQL]] >> [[Oracle_Workbook|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. | At some point you will want to save the results of a query in a file, as well as display them on screen. | ||
| Line 9: | Line 9: | ||
<code> | <code> | ||
| − | SPOOL filename | + | SPOOL [drive_name:\]filename |
</code> | </code> | ||
| + | |||
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. | 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. | ||
| Line 20: | Line 21: | ||
</code> | </code> | ||
| − | For example, type in the following (press return after each line): | + | For example, if running from a University PC, type in the following (press return after each line): |
| − | SPOOL emplist.txt | + | SPOOL u:\emplist.txt |
SELECT * FROM EMP; | SELECT * FROM EMP; | ||
SPOOL OFF | 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 == | == Exercise 2.1 == | ||
| − | To do | + | To do: |
| − | * Write an SQL command that lists all the departments, saving the the output to a file called: | + | * 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 | + | **Do not forget to use SPOOL OFF at the end |
| − | * Look on | + | * Look on your computer for a file called: output.txt |
| − | * Use | + | * Use a text editor, such as Notepad, Notepad++ or vi, to look at the contents of output.txt. |
| + | |||
== Next Step == | == Next Step == | ||
[[Ora2:Saving_and_Retrieving_Queries|Saving and Retrieving]] queries. | [[Ora2:Saving_and_Retrieving_Queries|Saving and Retrieving]] queries. | ||
Latest revision as of 08:40, 29 September 2020
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.
