SQL*Plus allows you to save the contents of the buffer to a file and to read that file back again. If you have built up a long and complicated SQL statement, you can save it for reuse later, and save yourself the bother of figuring it all out again. Two commands, GET and SAVE, are provided for this purpose.
The SAVE command allows you to save the current contents of the buffer to a file. Here is the syntax for SAVE:
SAVE filename [CREATE|REPLACE|APPEND]where:
- SAV[E]
May be abbreviated SAV.
- filename
Is the name of the file to which you want to save the buffer. The default extension is
.SQL, but you may specify another if you like.- CRE[ATE]
Tells SQL*Plus that you want to create a new file. The save will fail if the file you’ve specified already exists. This is the default behavior.
- REPLACE
Tells SQL*Plus to save the buffer to the file specified, regardless of whether or not that file already exists. If the file does exist, it will be overwritten.
- APPEND
Tells SQL*Plus to add the contents of the buffer to an existing file.
The following example shows the SAVE command being used to save the contents of a rather long SQL query to a file. First the query is entered into the buffer without being executed; then the SAVE command is used to write the buffer to a file.
SQL>SELECT employee_name, project_name2FROM employee, project, project_hours3WHERE employee.employee_id = project_hours.employee_id4AND project_hours.project_id = project.project_id5AND employee_billing_rate in (6SELECT MAX(employee_billing_rate)7FROM employee8)9GROUP BY employee_name, project_name10 SQL>SAVE C:AHIGHEST_BILLED_EMP_PROJECTSCreated file C:AHIGHEST_BILLED_EMP_PROJECTS
The SAVE command shown above created a new file,
with the default extension of .SQL, and saved
the contents of the buffer to that file. SQL*Plus also terminated the
statement in the buffer with a trailing forward slash on a line by
itself, so the resulting output file looks like this:
SELECT employee_name, project_name
FROM employee, project, project_hours
WHERE employee.employee_id = project_hours.employee_id
AND project_hours.project_id = project.project_id
AND employee_billing_rate in (
SELECT MAX(employee_billing_rate)
FROM employee
)
GROUP BY employee_name, project_name
/SQL*Plus will not automatically replace an existing file. Had the file already existed, and had we wanted to replace it, the REPLACE option would have been needed on the SAVE command. For example:
SAVE C:AHIGHEST_BILLED_EMP_PROJECTS REPLACE
The APPEND option adds the contents of the buffer onto the end of an existing file. If you append multiple statements to a file, you won’t be able to load that file back into the buffer and execute those commands. However, you will be able to execute the file using the START command.
Tip
Try to use descriptive filenames when saving your SQL statements. You
want the filename to jog your memory later when you need to retrieve
that statement. The query shown above returns a list of projects
worked on by the employee (or employees) with the highest billing
rate; thus the filename of
HIGHEST_BILLED_EMP_PROJECTS seemed appropriate.
The length of a filename is governed by what your operating system
allows.
The GET command is the opposite of SAVE. It retrieves the contents of a file to the buffer. Here is the syntax:
GET filename [LIST|NOLIST]where:
- GET
May not be abbreviated.
- filename
Is the name of the file containing the text you want to load into the buffer. The default extension is
.SQL, but you may specify another if you like.- LIS[T]
May be abbreviated to LIS, and tells SQL*Plus to list the contents of the buffer after loading the file.
- NOL[IST]
May be abbreviated to NOL, and tells SQL*Plus to load the file without listing it for you to see.
The following example shows how to retrieve the SQL statement that was saved in the previous section:
SQL> GET C:AHIGHEST_BILLED_EMP_PROJECTS
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3 WHERE employee.employee_id = project_hours.employee_id
4 AND project_hours.project_id = project.project_id
5 AND employee_billing_rate in (
6 SELECT MAX(employee_billing_rate)
7 FROM employee
8 )
9* GROUP BY employee_name, project_nameNotice that the GET command automatically displays the contents of the retrieved file for you to see. This allows you to confirm that you have loaded the correct statement. Once the statement has been loaded into the buffer, you may execute it using either RUN or /, or you may use any of the editing commands to change it.
Tip
The GET command will load any text file into the
buffer. You can even load your
AUTOEXEC.BAT
file (for Windows users)
into the buffer, edit it, and save it again. This might be a useful
thing to know if you are ever really hard up for an editor.
While SQL*Plus will let you load any text file into the buffer, be aware that you cannot execute the buffer unless it contains exactly one SQL statement or one PL/SQL block. To be safe, the text file should terminate the statement (or block) with a forward slash on a line by itself. See the previous section on the SAVE command for an example of this.