Saving, Retrieving, and Running Files

SQL*Plus allows you save, retrieve, and run text files containing SQL*Plus commands and SQL statements. You ve already seen one example of running a SQL*Plus script: you saw how to run the store_schema.sql script file in Chapter 1, which created the store schema.

Some of the file commands are listed in the following table.

Command	Description
SAV[E] filename [{ REPLACE APPEND }]	Saves the contents of the SQL*Plus buffer to a file specified by filename . You can append the content of the buffer to an existing file using the APPEND option. You can also overwrite an existing file using the REPLACE option.
GET filename	Retrieves the contents of the file specified by filename into the SQL*Plus buffer.
STA[RT] filename	Retrieves the contents of the file specified by filename into the SQL*Plus buffer, and then attempts to run the contents of the buffer.
@ filename	Same as the START command.
ED[IT]	Copies the contents of the SQL*Plus buffer to a file named afiedt.buf and then starts the default editor for the operating system. When you exit the editor, the contents of your edited file are copied to the SQL*Plus buffer.
ED[IT] filename	Same as the EDIT command, but you can specify a file to start editing. You specify the file to edit using the filename parameter.
SPO[OL] filename	Copies the output from SQL*Plus to the file specified by filename .
SPO[OL] OFF	Stops the copying of output from SQL*Plus to the file, and closes that file.

Let s take a look at some examples of using these SQL*Plus commands. First, enter the following SQL statement into SQL*Plus:

 SQL>  SELECT customer_id, first_name, last_name  2  FROM customers  3  WHERE customer_id = 1;  

Save the contents of the SQL*Plus buffer to a file named cust_query.sql using the SAVE command:

 SQL>  SAVE cust_query.sql  Created file cust_query.sql 

Note  

By default the cust_query.sql file is stored in the bin subdirectory where you installed the Oracle software.

Retrieve the contents of the cust_query.sql file using the GET command:

 SQL>  GET cust_query.sql  1 SELECT customer_id, first_name, last_name  2 FROM customers  3* WHERE customer_id = 1 

Run the contents of the cust_query.sql file using the START command:

 SQL>  START cust_query.sql  CUSTOMER_ID FIRST_NAME LAST_NAME ----------- ---------- ----------  1 John Brown 

Edit the contents of the SQL*Plus buffer using the EDIT command:

 SQL>  EDIT  

The EDIT command starts the default editor for your operating system. On Windows the default editor is Notepad, and on Unix or Linux the default editor is vi or emacs. You can set the default editor using the DEFINE command:

 DEFINE _EDITOR = '  editor  ' 

where editor is the name of your preferred editor.

For example, the following command sets the default editor to vi:

 DEFINE _EDITOR = 'vi' 

Figure 5-1 shows the contents of the SQL*Plus buffer in Notepad. Notice that the SQL statement is terminated using a slash character (/) rather than a semicolon.

Figure 5-1: Editing the SQL*Plus buffer contents using Notepad

In your editor, change the WHERE clause to WHERE customer_id = 2 and save and quit from your editor. For example, in Notepad you select File Exit to quit Notepad; click Yes to save your query when prompted by Notepad. SQL*Plus displays the following output containing your modified query. Notice that the WHERE clause has been changed:

 Wrote file afiedt.buf  1 SELECT customer_id, first_name, last_name  2 FROM customers  3* WHERE customer_id = 2 

Run your modified query using the slash character ( / ):

 SQL>  /  CUSTOMER_ID FIRST_NAME LAST_NAME ----------- ---------- ----------  2 Cynthia Green 

Next, use the SPOOL command to copy the output from SQL*Plus to a file named cust_results.txt , run your query again, and then turn spooling off by executing SPOOL OFF :

 SQL>  SPOOL cust_results.txt  SQL>  /  CUSTOMER_ID FIRST_NAME LAST_NAME ----------- ---------- ----------  2 Cynthia Green SQL>  SPOOL OFF  

Feel free to examine the cust_results.txt file; it will contain the previous output between the slash (/) and SPOOL OFF . By default, this file is stored in the directory where the Oracle software is installed under the bin subdirectory. You can specify the full directory path where you want the file to be written using the SPOOL command by adding a directory path to your filename. For example:

 SPOOL C:\my_files\spools\cust_results.txt