2.3 How do I…Save query results and SQL statements to a file?
ProblemI often need to execute SQL statements more than once. When I write a long SQL statement or a SQL statement that I need to run repeatedly, I want to save it to a file so that I can run it later. I also need to write the results of my queries to a file so that I can edit and print their results. How do I save query results and SQL statements to a file?
Technique
One of the most powerful features of SQL*Plus is its capability to write query results, SQL statements, and the feedback from SQL statements to a file. These can be written to files to produce reports, can be imported into spreadsheets, or can run as command files. The results of long-running SQL*Plus scripts can be written to output files and checked later to ensure successful execution. The SPOOL command writes SQL*Plus output to a file. The syntax of the command is
SPO[OL] [file_name[.ext] | OFF | OUT]
If the full path of the output file is not specified, it is written to your working directory. If an extension is not specified, the extension .LST is appended to the filename. The SPOOL OFF command is used to stop the writing of output and close the file. The SPOOL OUT command stops the writing of the output and sends the output directly to the default printer. This is useful when creating reports with SQL*Plus.
The SQL*Plus SAVE command is used to save the SQL statement currently in the SQL buffer to a file. The SQL buffer contains the most recent SQL statement entered into SQL*Plus or retrieved from a file. As soon as SQL*Plus recognizes a statement as a SQL statement, it replaces the current statement in the SQL buffer with the new statement. Think of the SQL buffer as the Windows Clipboard for SQL*Plus. The SQL buffer can be saved to a file with the SAVE command, retrieved from a file with the GET command, and edited with a variety of SQL*Plus commands.
The syntax of the SAVE command is:
SAV[E] filename[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Characters and options shown in brackets ([]) are optional. Options separated with the pipe symbol ( | ) are exclusive. The default action of the SAVE command is to create a new file. If the file exists, the REPLACE or APPEND keyword is required. By default, SQL*Plus will save the file with the .SQL extension to identify it as a SQL*Plus file. If you want it saved with a different extension, you can specify one when you execute the SAVE command.
NOTE - The SUFFIX system variable can be modified to change the default extension from .SQL to another value. For more information on setting SQL*Plus system variables, see How-To 2.4.
Steps
1. CHP2_8.SQL, shown in Figure 2.8, contains a SQL script that includes the SPOOL command to save the results of a query to a file. The query contained in the file references a table created by CHP2_7.SQL. If you have not executed CHP2_7.SQL to create the sample tables (from How- To 2.2), run the file first using the START command.
The first line of the file contains the SPOOL command that writes the output of the subsequent statements to the file C:\TESTFILE.LST. The following three lines contain a simple query of the DEPT2_1 table created by CHP2_7.SQL. The SPOOL OFF command closes the output file. Execute CHP2_8.SQL with the START command.
SQL> START CHP2_8.sql
DNAME -------------------- MARKETING ACCOUNTING SALES When the SQL file is executed, the SPOOL and SPOOL OFF commands don’t generate any output; however, they write the results of the query to the file.
To save the current command in the SQL buffer, don’t end the statement with a semicolon. If the SQL statement is terminated with a semicolon, SQL*Plus will attempt to execute it.
SQL> SELECT EMPNO, ENAME
2 FROM EMP
3 ORDER BY EMPNO
Unlike a SQL*Plus command, SQL statements can span multiple lines without a line continuation character. The line continuation character for SQL*Plus commands is the dash (-). You can begin a new line by pressing [ENTER]. SQL*Plus automatically numbers the lines as they are entered. If [ENTER] is pressed on an empty line, SQL*Plus returns to the SQL prompt. The SQL statement entered remains in the SQL buffer until it is cleared, replaced with another SQL statement, or SQL*Plus is exited.
2. Save the file with the SAVE command. If successful, SQL*Plus will notify you that the file was created.
SQL> SAVE EMPQRY
Created file EMPQRY
The file is automatically created in the current working directory with the .SQL extension. You can specify a complete path including a different extension for the file. If the file already exists, an error message will be returned.
3. Because a SQL*Plus command file can contain multiple SQL statements, the APPEND option enables you to add the contents of the SQL buffer to the end of an existing file. Save the contents of the SQL buffer to the same file with the SAVE command and the APPEND option.
SQL> SAVE EMPQRY APPEND Appended file to empqry
If the file does not exist when an attempt to append to it is made, the file will be created.
How It Works
The SPOOL command is used within SQL*Plus to write the output generated to a file. CHP2_8.SQL demonstrates the process. The first line opens the file C:\TESTFILE.LST and instructs SQL*Plus to write a copy of all output to this file. Until spooling is stopped, all statements entered or displayed on the screen are written to the file. The last statement, SPOOL OFF, stops the spooling of the file and closes the file.
The SQL*Plus SAVE command is a SQL*Plus command (not a SQL statement). Executing it does not replace the contents of the SQL buffer. If the SAVE command is executed against Oracle using a different tool, an error will occur. Step 1 creates a SQL statement but does not execute it. Step 2 saves the contents of the SQL buffer created in Step 1 to a file using the SAVE command. Step 3 appends the contents of the SQL buffer to the end of an existing file by including the APPEND option with the SAVE command.
Comments
Writing the output of a query to a file is the basis for developing reports using SQL*Plus. Using the SPOOL command is an easy way to redirect output from SQL*Plus to a file. Many of the How-To’s throughout the book use the SPOOL command.
When statements are entered into the SQL buffer, SQL*Plus expects only SQL or PL/SQL statements. SQL*Plus commands will not be saved in the SQL buffer. If you want the SQL script to contain formatting statements, use the INPUT command, which places all statements in the SQL buffer and does not analyze the statements as they are entered. Don’t put a semicolon at the end of the SQL statement when using the INPUT command, or an error will occur.