Saving and Retrieving the Buffer

2017-11-03 09:05:07

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.

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.

Executing Unrecognized SQL Statements Redux!

That SQL*Plus lets you GET any arbitrary text into the buffer, which presents another solution to the problem of executing a SQL statement not recognized by your version of SQL*Plus. This time, consider a SELECT statement beginning with a WITH clause, which SQL*Plus 8.1.7 does not recognize:

SQL> WITH dually AS (SELECT * FROM dual) SP2-0734: unknown command beginning "with dually..." - rest of line ignored.

Suppose that you want, nay need, to execute this statement. Executing it from within PL/SQL doesn't work because PL/SQL doesn't handle statements that return a result set in the manner that this one does.

A solution is to place the statement within a text file, and GET that file. Say you place the following statement into a file named dually.sql :

WITH dually AS (SELECT * FROM dual) SELECT * FROM dually /

You can now GET and execute that statement:

SQL> GET dually 1 WITH dually AS (SELECT * FROM dual) 2* SELECT * FROM dually 3 / D - X

This technique works because, while SQL*Plus tests the first keyword from any statement you enter interactively, it doesn't do so for any file that you read into the buffer. GET bypasses the mechanism whereby SQL*Plus attempts to determine whether a given statement is valid.

Thanks to Tom Kyte for suggesting this fascinating use of GET.

Executing Unrecognized SQL Statements Redux!

Категории