| Lesson 5 | Working with Files |
| Objective | Save a query to a file, replace a file, and edit a file. |
Working with Oracle Query Files
There are a set of file handling commands within SQL*Plus. The following table spells them out for you.| Command | Description |
| GET filename[.ext] [LIS[T]|NOL[IST]] |
Put the contents of filename into the SQL*Plus buffer. If no parameter is used, the contents is listed. You can specify NOLIST to prevent listing the contents when it is loaded. Default for .ext is .sql. |
| REM[ARK] text Or -- text Or /* text */ |
Add a remark into a file. REM marks an entire line as a comment. “--” marks everything from that point to the end of the line as a comment (useful for comments on the same line as a short command.) The last option allows you to place multiple lines between the beginning and ending marks (useful for documentation). |
RUN filename[.ext] |
Execute the SQL command in filename. Use RUN only when there is a single SQL command in the file and no SQL*Plus commands (such as COLUMN or TTITLE). If the file contains multiple commands or contains SQL*Plus commands, use the START command to execute the file.
|
| SAV[E] filename[.ext] [CRE[ATE]|REP[LACE] |APP[END]] |
Write the SQL*Plus buffer to filename. If no parameter is used, the file is created. |
| STA[RT] filename[.ext] Or @filename[.ext] Or @@filename[.ext] |
Executes the commands in filename inside SQL*Plus. The last command is stored in the buffer. |
Using the SQL Buffer
SQL*Plus stores your most recent SQL command in an area called the SQL buffer. The SQL buffer is an important SQL*Plus concept. You can display the contents of the
SQL buffer using a SQL*Plus command called LIST, as shown in Figure 2.
Figure 2: The SQL*Plus LIST Command
The ability to retrieve the last SQL statement from the SQL buffer is often very useful when you need to correct errors and re-execute the SQL statement. You will see how to do this in the subsequent sections, where we will also discuss some other SQL*Plus commands related to the SQL buffer. If you enter a second SQL command, the SQL buffer is overwritten, and you lose the previous SQL command. Invoking the external editor starts a subprocess, which means that you cannot return to SQL*Plus until you have closed the external editor window. Alternatively, you may want to start a separate editor session from the operating system (that is, not from SQL*Plus) so you can switch between two windows. In that case, you must make sure to save the changes in your editor window before executing the changed SQL command in SQL*Plus.
Figure 2: The SQL*Plus LIST Command
SQL> L 1 select * 2* from employees SQL>
The ability to retrieve the last SQL statement from the SQL buffer is often very useful when you need to correct errors and re-execute the SQL statement. You will see how to do this in the subsequent sections, where we will also discuss some other SQL*Plus commands related to the SQL buffer. If you enter a second SQL command, the SQL buffer is overwritten, and you lose the previous SQL command. Invoking the external editor starts a subprocess, which means that you cannot return to SQL*Plus until you have closed the external editor window. Alternatively, you may want to start a separate editor session from the operating system (that is, not from SQL*Plus) so you can switch between two windows. In that case, you must make sure to save the changes in your editor window before executing the changed SQL command in SQL*Plus.
Files are saved in the current directory unless you specify a directory path in the save command.
Read the text below to practice saving and running SQL*Plus script files.
Saving and Starting SQL*Plus a Script File
- You have created the query you see in the SQL*Plus window here. Now you want to save it in a file named PR_CUST.sql. To do this, type SAVE PR_CUST at the SQL> prompt and press Enter. SQL*Plus automatically adds the .SQL suffix to the file.
- SQL*Plus tells you that it has created the file. Next, go to the operating system to verify that the file was created. Type HOST and press Enter to open a new window where you can type operating system commands.
- SQL*Plus opens a window. In this example, you are now in an MS-DOS window, so you will type the MS-DOS directory listing command. Type dir PR* and press Enter to list all files that begin with PR.
- As you can see, the file PR_CUST.sql was created and is listed in the current directory. Close the MS-DOS window now by typing EXIT and then pressing Enter.
- You are now returned to the SQL*Plus window. Try executing the query that you saved in the file by typing START PR_CUST and pressing Enter. SQL*Plus assumes that the suffix will be .sql because you did not specify any other suffix.
- The file is read and executed by SQL*Plus. Notice that the query text is not displayed but the query results are displayed. This is the default behavior for SQL*Plus. This is the end of the simulation. Click Exit.
Saving Commands
As explained earlier in the module, the SQL buffer is overwritten with every new SQL command you enter in SQL*Plus. If you want to save the contents of the SQL buffer, you can use the SQL*Plus SAVE command. The SAVE command creates a script file containing the contents of the SQL buffer.
PMT Process modeling techniques DSG 1 RSD Relational system design DSG 2 PRO Prototyping DSG 5 GEN System generation DSG 4 10 rows selected. SQL> save result1 SP2-0540: File "result1.sql" already exists. Use "SAVE filename[.ext] REPLACE". SQL> save result1 replace Created file result1.sql
Note the error message after the second SAVE RESULT1 attempt; REPLACE (or APPEND) is mandatory if a file already exists.
Since the SQL buffer is overwritten with each new SQL command issued, using the SAVE APPEND syntax when creating a script file can be useful if you would like any new SQL commands you issue to be added to your script file. We have created two script files.
If you prefer to use a different file name extension, you can change it with the SQL*Plus SUFFIX setting. The next lesson begins our study of SQL*Plus environment setting commands.
These script files are assigned the extension .SQL by default.
If you prefer to use a different file name extension, you can change it with the SQL*Plus SUFFIX setting. The next lesson begins our study of SQL*Plus environment setting commands.