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:AHIGHEST_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.
|
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.
|
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.
