Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

2.9 Saving and Retrieving the Buffer

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 later reuse and save
yourself the bother of figuring it all out again. Two commands, SAVE
and GET, are provided for this purpose.

2.9.1 SAVE

Example 2-15 shows the
SAVE
command being used to save the contents of a 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.

Example 2-15. Writing the current buffer contents to a file

SQL> SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6 AND employee_billing_rate IN (
7 SELECT MAX(employee_billing_rate)
8 FROM employee)
9 GROUP BY employee_name, project_name
10
SQL> SAVE highest_billed_emp_projects
Created file highest_billed_emp_projects.sql The SAVE command in Example 2-15 creates a
new file, with the default extension of
.sql, and writes the contents of the buffer to
that file. After writing the buffer contents, SQL*Plus writes a
trailing forward slash on a line by itself, so the resulting output
file looks like this:

SELECT employee_name, project_name
FROM employee JOIN project_hours
ON employee.employee_id = project_hours.employee_id
JOIN project
ON 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, SQL*Plus would have reported an error. You must
use the REPLACE option to overwrite an existing file:

SAVE C:\A\HIGHEST_BILLED_EMP_PROJECTS REPLACE You can use the APPEND option to add 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.

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 in Example 2-15 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 seems appropriate. The length of a filename is governed by what your operating system allows.

2.9.2 GET

The
GET
command is the opposite of SAVE. It retrieves the contents of a file
to the buffer. The file extension defaults to
.sql. For example, to retrieve the statement
saved earlier in Example 2-15, specify the following:

SQL> GET highest_billed_emp_projects
1 SELECT employee_name, project_name
2 FROM employee JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6 AND employee_billing_rate IN (
7 SELECT MAX(employee_billing_rate)
8 FROM employee)
9* GROUP BY employee_name, project_name 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. You can use the NOLIST option, as in
GET highest_billed_emp_projects NOLIST if you
don't want that behavior. Once the statement has
been loaded into the buffer, you may execute it using RUN or /, or
you may use any of the editing commands to change it.

The GET command will load any text file into the buffer, whether that file contains a valid statement or a PL/SQL block. This might be a useful thing to know if you are ever really hard up for an editor. Any file you write back out will include a trailing forward slash.

Although 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.