SQL Extensions   «Prev  Next»

Lesson 5Working with Files
ObjectiveSave a query to a file, replace a file, and edit a file.

Working with Query Files in Oracle


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
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.
Use the simulation below to practice saving and running SQL*Plus script files.
Saving Starting SQL Plus script File
The next lesson begins our study of SQL*Plus environment setting commands.