Manipulating Commands, 3 of 6

Saving Commands for Later Use

Through SQL*Plus, you can store one or more commands in a file called a command file. After you create a command file, you can retrieve, edit, and run it. Use command files to save commands for use over time, especially complex commands or PL/SQL blocks.

Storing Commands in Command Files

You can store one or more SQL commands, PL/SQL blocks, and SQL*Plus commands in command files. You create a command file within SQL*Plus in one of three ways:

enter a command and save the contents of the buffer
use INPUT to enter commands and then save the buffer contents
use EDIT to create the file from scratch using a host system text editor

Because SQL*Plus commands are not stored in the buffer, you must use one of the latter two methods to save SQL*Plus commands.

Creating a Command File by Saving the Buffer Contents

To save the current SQL command or PL/SQL block for later use, enter the SAVE command. Follow the command with a file name:

SAVE file_name

SQL*Plus adds the .SQL extension to the filename to identify it as a SQL query file. If you wish to save the command or block under a name with a different file extension, type a period at the end of the filename, followed by the extension.

Note:

.sql is the file extension used by default for files saved from SQL*Plus, You can use the SQL*Plus command, SET SUFFIX extension, to set the file extension you want to use.

Note that within SQL*Plus, you separate the extension from the filename with a period. Your operating system may use a different character or a space to separate the filename and the extension.

Example 3-6 Saving the Current Command

First, enter LIST:


LIST

Which lists the command currently in your buffer:


  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMP_DETAILS_VIEW
  3  WHERE JOB_ID='SA_MAN'

If the query shown is not in your buffer, re-enter the query now. Next, enter the SAVE command followed by the filename EMPLINFO:


SAVE EMPLINFO


Created file EMPLINFO.sql

Verify that the command file EMPLINFO.SQL exists by entering the SQL*Plus HOST command followed by your host operating system's file listing command:

HOST your_host's_file_listing_command

You can use the same method to save a PL/SQL block currently stored in the buffer.

Creating a Command File by Using INPUT and SAVE

If you use INPUT to enter your commands, you can enter SQL*Plus commands (as well as one or more SQL commands or PL/SQL blocks) into the buffer. You must enter the SQL*Plus commands first, and the SQL command(s) or PL/SQL block(s) last--just as you would if you were entering the commands directly to the command prompt.

You can also store a set of SQL*Plus commands you plan to use with many different queries by themselves in a command file.

Example 3-7 Saving Commands Using INPUT and SAVE

Suppose you have composed a query to display a list of salespeople and their commissions. You plan to run it once a month to keep track of how well each employee is doing.

To compose and save the query using INPUT, you must first clear the buffer:


CLEAR BUFFER

Next, use INPUT to enter the command (be sure not to type a semicolon at the end of the command):


INPUT

You are then prompted to enter each line of the script. Do not enter a semicolon at the end of any statement, otherwise SQL*Plus will unsuccessfully attempt to execute the script. SQL*Plus only expects to find SQL or PL/SQL statements in the buffer.


COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN'

The zero in the format model for the column COMMISSION_PCT tells SQL*Plus to display an initial zero for decimal values, and a zero instead of a blank when the value of COMMISSION_PCT is zero for a given row. Format models and the COLUMN command are described in more detail in Chapter 4, "Formatting Query Results" and in the Oracle9i SQL Reference.

Now use the SAVE command to store your query in a file called SALES with the extension SQL:


SAVE SALES


Created file SALES.SQL

Note that you do not type a semicolon at the end of the query; if you did include a semicolon, SQL*Plus would attempt to run the buffer contents. The SQL*Plus commands in the buffer would produce an error because SQL*Plus expects to find only SQL commands in the buffer. You will learn how to run a command file later in this chapter.

To input more than one SQL command, leave out the semicolons on all the SQL commands. Then, use APPEND to add a semicolon to all but the last command. (SAVE appends a slash to the end of the file automatically; this slash tells SQL*Plus to run the last command when you run the command file.)

To input more than one PL/SQL block, enter the blocks one after another without including a period or a slash on a line between blocks. Then, for each block except the last, list the last line of the block to make it current and use INPUT in the following form to insert a slash on a line by itself:


INPUT /

Creating Command Files with a System Editor

You can also create a command file with a host operating system text editor by entering EDIT followed by the name of the file, for example:


EDIT SALES

Like the SAVE command, EDIT adds the filename extension SQL to the name unless you type a period and a different extension at the end of the filename. When you save the command file with the text editor, it is saved back into the same file.

You must include a semicolon at the end of each SQL command and a period on a line by itself after each PL/SQL block in the file. (You can include multiple SQL commands and PL/SQL blocks.)

When you create a command file using EDIT, you can also include SQL*Plus commands at the end of the file. You cannot do this when you create a command file using the SAVE command because SAVE appends a slash to the end of the file. This slash would cause SQL*Plus to run the command file twice, once upon reaching the semicolon at the end of the last SQL command (or the slash after the last PL/SQL block) and once upon reaching the slash at the end of the file.

Placing Comments in Command Files

You can enter comments in a command file in three ways:

using the SQL*Plus REMARK command for single line comments.
using the SQL comment delimiters /*... */ for single of multi line comments.
using ANSI/ISO (American National Standards Institute/International Standards Organization) comments - - for single line comments.

For further information about using comments in command files, see "Notes on Placing Comments" later in this chapter.

Using the REMARK Command

Use the REMARK command on a line by itself in a command file, followed by comments on the same line. To continue the comments on additional lines, enter additional REMARK commands. Do not place a REMARK command between different lines of a single SQL command.


REMARK Commission Report;
REMARK to be run monthly.;
COLUMN LAST_NAME HEADING 'LAST_NAME';
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999;
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90;
REMARK Includes only salesmen;
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN'

Using /.../

Enter the SQL comment delimiters, /*...*/, on separate lines in your command file, on the same line as a SQL command, or on a line in a PL/SQL block.

You must enter a space after the slash-asterisk(/*) beginning a comment, otherwise the comment is treated as a command, and the slash is interpreted as an execute command, executing any command in the SQL*Plus buffer.

The comments can span multiple lines, but cannot be nested within one another:


/* Commission Report
 to be run monthly. */
COLUMN LAST_NAME HEADING 'LAST_NAME';
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999;
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90;
REMARK Includes only salesmen;
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
/* Include only salesmen.*/
WHERE JOB_ID='SA_MAN'

If you enter a SQL comment directly at the command prompt, SQL*Plus does not store the comment in the buffer.

Using - -

You can use ANSI/ISO "- -" style comments within SQL statements, PL/SQL blocks, or SQL*Plus commands. Since there is no ending delimiter, the comment cannot span multiple lines.

For PL/SQL and SQL, enter the comment after a command on a line, or on a line by itself:


-- Commissions report to be run monthly
DECLARE --block for reporting monthly sales

For SQL*Plus commands, you can only include "- -" style comments if they are on a line by themselves. For example, these comments are legal:


-- set maximum width for LONG to 777
SET LONG 777

This comment is illegal:


SET LONG 777 -- set maximum width for LONG to 777

If you enter the following SQL*Plus command, SQL*Plus interprets it as a comment and does not execute the command:


-- SET LONG 777

Notes on Placing Comments

SQL*Plus generally does not parse or execute input it identifies as a comment.

SQL*Plus does not have a SQL or PL/SQL command parser. It scans the first few keywords of each new statement to determine the command type, SQL, PL/SQL or SQL*Plus. Comments in some locations can prevent SQL*Plus from correctly identifying the command type, giving unexpected results. The following usage notes may help you to use SQL*Plus comments more effectively:

Do not put comments within the first few keywords of a statement. For example:
```
SQL> CREATE OR REPLACE
  2  /* HELLO */
  3  PROCEDURE HELLO AS
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE('HELLO');

Warning: Procedure created with compilation errors.
```
The location of the comment prevents SQL*Plus from recognizing the command as a PL/SQL command. SQL*Plus submits the block to the server when it sees the slash "/" at the beginning of the comment, which it interprets as the "/" statement terminator. Move the comment to avoid this error. For example:
```
 CREATE OR REPLACE PROCEDURE
  2  /* HELLO */
  3  HELLO AS
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE('HELLO');
  6  END;
  7  /

Procedure created.
```
Do not put comments after statement terminators (period, semicolon or slash). For example, if you enter:
```
SELECT 'Y' FROM DUAL; -- TESTING
```
You get the following error:
```
SELECT 'Y' FROM DUAL; -- TESTING
                    *
ERROR at line 1:
ORA-00911: invalid character
```
SQL*Plus expects no text after statement terminators on the same line and is unable to recognize the comment.
Do not put statement termination characters at the end of a comment line or after comments in a SQL statement or a PL/SQL block. For example, if you enter:
```
SELECT *
-- COMMENT;
```
You get the following error:
```
-- COMMENT
         *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
```
The semicolon is interpreted as a statement terminator and SQL*Plus submits the partially formed SQL command to the server for processing, resulting in an error.
Do not use ampersand characters '&' in comments in a SQL statement or PL/SQL block. For example, if you enter a script such as:
```
SELECT REGION_NAME, CITY
/* THIS & THAT */
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;
```

It prompts for the value of &that:


Enter value for that: 
old   2: /* THIS & THAT */
new   2: /* THIS  */

REGION_NAME               CITY
------------------------- ------------------------------
Americas                  Seattle
Americas                  Seattle
Americas                  Seattle
Europe                    Oxford
Europe                    Oxford
Americas                  Toronto

6 rows selected.

SQL*Plus interprets text after the ampersand character "&" as a substitution variable and prompts for the value of the variable. You can SET DEFINE OFF to prevent scanning for the substitution character.

For more information on substitution and termination characters, see DEFINE, SQLTERMINATOR and SQLBLANKLINES in the SET command in Chapter 8.

Retrieving Command Files

If you want to place the contents of a command file in the buffer, you must retrieve the command from the file in which it is stored. You can retrieve a command file using the SQL*Plus command GET.

Just as you can save a query from the buffer to a file with the SAVE command, you can retrieve a query from a file to the buffer with the GET command:

GET file_name

When appropriate to the operating system, SQL*Plus adds a period and the extension SQL to the filename unless you type a period at the end of the filename followed by a different extension. For information about setting the file suffix, see SET SUFFIX in Chapter 8, "Command Reference".

Example 3-8 Retrieving a Command File

Suppose you need to retrieve the SALES file in a later session. You can retrieve the file by entering the GET command. To retrieve the file SALES, enter


GET SALES

SQL*Plus retrieves the contents of the file SALES.SQL into the SQL buffer and lists it on the screen:


  1  COLUMN LAST_NAME HEADING 'LAST NAME'
  2  COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999
  3  COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90
  4  SELECT LAST_NAME, SALARY, COMMISSION_PCT
  5  FROM EMP_DETAILS_VIEW
  6* WHERE JOB_ID='SA_MAN'

The file should contain a single SQL statement or PL/SQL block. SQL*Plus commands or multiple statements or blocks will be loaded, but will give errors if run with "/" or RUN.

Running Command Files

The START command retrieves a command file and runs the command(s) it contains. Use START to run a command file containing SQL commands, PL/SQL blocks, and SQL*Plus commands. You can have many commands in the file. Follow the START command with the name of the file:

START file_name

If the file has the extension SQL, you need not add the period and the extension SQL to the filename.

Example 3-9 Running a Command File

To retrieve and run the command stored in SALES.SQL, enter


START SALES

SQL*Plus runs the commands in the file SALES and displays the results of the commands on your screen, formatting the query results according to the SQL*Plus commands in the file:


LAST NAME                 MONTHLY SALARY COMMISSION %
------------------------- -------------- ------------
Russell                          $14,000         0.40
Partners                         $13,500         0.30
Errazuriz                        $12,000         0.30
Cambrault                        $11,000         0.30
Zlotkey                          $10,500         0.20

To see the commands as SQL*Plus "enters" them, you can set the ECHO variable of the SET command to ON. The ECHO variable controls the listing of the commands in command files run with the START, @ and @@ commands. Setting the ECHO variable to OFF suppresses the listing.

You can also use the @ ("at" sign) command to run a command file:


@SALES

The @ command lists and runs the commands in the specified command file in the same manner as START. SET ECHO affects the @ command as it affects the START command.

START, @ and @@ leave the last SQL command or PL/SQL block in the command file in the buffer.

Running a Command File as You Start SQL*Plus

To run a command file as you start SQL*Plus, use one of the following four options:

Follow the SQLPLUS command with your username, a slash, your password, a space, @, and the name of the file:
```
SQLPLUS HR/HR @SALES
```
SQL*Plus starts and runs the command file.
Follow the SQLPLUS command and your username with a space, @, and the name of the file:
```
SQLPLUS HR @SALES
```
SQL*Plus prompts you for your password, starts, and runs the command file.
Include your username as the first line of the file. Follow the SQLPLUS command with @ and the filename. SQL*Plus prompts for your password, starts, and runs the file.
Include your username, a slash (/), and your password as the first line of the file. Follow the SQLPLUS command with @ and the filename. SQL*Plus starts and runs the file. Please consider the security risks of exposing your password in the file before using this technique.

Nesting Command Files

To run a series of command files in sequence, first create a command file containing several START commands, each followed by the name of a command file in the sequence. Then run the command file containing the START commands. For example, you could include the following START commands in a command file named SALESRPT:

START Q1SALES
START Q2SALES
START Q3SALES
START Q4SALES
START YRENDSLS

Note:

The @@ command may be useful in this example. See the @@ (double "at" sign) command in Chapter 8 for more information.

Modifying Command Files

You can modify an existing command file in two ways:

using the EDIT command
using GET, the SQL*Plus editing commands, and SAVE

To edit an existing command file with the EDIT command, follow the word EDIT with the name of the file.

For example, to edit an existing file named PROFIT that has the extension SQL, enter the following command:


EDIT PROFIT

Remember that EDIT assumes the file extension SQL if you do not specify one.

To edit an existing file using GET, the SQL*Plus editing commands, and SAVE, first retrieve the file with GET, then edit the file with the SQL*Plus editing commands, and finally save the file with the SAVE command.

Note that if you want to replace the contents of an existing command file with the command or block in the buffer, you must use the SAVE command and follow the filename with the word REPLACE.

For example:


GET MYREPORT


1* SELECT * FROM EMP


CHANGE/EMP/EMP_DETAILS_VIEW 


1* SELECT * FROM EMP_DETAILS_VIEW


SAVE MYREPORT REPLACE


Wrote file MYREPORT

If you want to append the contents of the buffer to the end of an existing command file, use the SAVE command and follow the filename with the word APPEND:


SAVE file_name APPEND

Exiting from a Command File with a Return Code

If your command file generates a SQL error while running from a batch file on the host operating system, you may want to abort the command file and exit with a return code. Use the SQL*Plus command WHENEVER SQLERROR to do this; see the WHENEVER SQLERROR command in Chapter 8 for more information.

Similarly, the WHENEVER OSERROR command may be used to exit if an operating system error occurs. See the WHENEVER OSERROR command in Chapter 8 for more information.

Setting Up Your SQL*Plus Environment

You may wish to set up your SQL*Plus environment in a particular way (such as showing the current time as part of the SQL*Plus command prompt) and then reuse those settings with each session. You can do this through a host operating system file called LOGIN with the file extension SQL (also called your User Profile). The exact name of this file is system dependent; see the Oracle installation and user's guide provided for your operating system for the precise name.

You can add any SQL commands, PL/SQL blocks, or SQL*Plus commands to this file; when you start SQL*Plus, it automatically searches for your LOGIN file (first in your local directory and then on a system-dependent path) and runs the commands it finds there. (You may also have a Site Profile, for example, GLOGIN.SQL which is run before LOGIN.SQL. See"Setting Up the Site Profile" for more information on the relationship of Site and User Profiles.)

Modifying Your LOGIN File

You can modify your LOGIN file just as you would any other command file. You may wish to add some of the following commands to the LOGIN file:

SET LINESIZE	Followed by a number, sets the number of characters as page width of the query results.
SET NUMFORMAT	Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
SET PAGESIZE	Followed by a number, sets the number of lines per page.
SET PAUSE	Followed by ON, causes SQLPlus to pause at the beginning of each page of output (SQLPlus continues scrolling after you enter Return). Followed by text, sets the text to be displayed each time SQL*Plus pauses (you must also set PAUSE to ON).
SET TIME	Followed by ON, displays the current time before each command prompt.

See the SET command in Chapter 8 for more information on these and other SET command variables you may wish to set in your SQL*Plus LOGIN file.

Storing and Restoring SQL*Plus System Variables

You can store the current SQL*Plus system ("SET") variables in a host operating system file (a command file) with the STORE command. If you alter any variables, this command file can be run to restore the original values. This is useful if you want to reset system variables after running a report that alters them.

To store the current setting of all system variables, enter

STORE SET file_name

By default, SQL*Plus adds the extension "SQL" to the file name. If you want to use a different file extension, type a period at the end of the file name, followed by the extension. Alternatively, you can use the SET SUFFIX command to change the default file extension.

Restoring the System Variables

To restore the stored system variables, enter

START file_name

If the file has the default extension (as specified by the SET SUFFIX command), you do not need to add the period and extension to the file name.

You can also use the @ ("at" sign) or the @@ (double "at" sign) commands to run the command file.

Example 3-10 Storing and Restoring SQLPlus System Variables*

To store the current values of the SQL*Plus system variables in a new command file "plusenv.sql":


STORE SET plusenv


Created file plusenv

Now the value of any system variable can be changed:


SHOW PAGESIZE


PAGESIZE 24


SET PAGESIZE 60
SHOW PAGESIZE


PAGESIZE 60

The original values of system variables can then be restored from the command file:


START plusenv
SHOW PAGESIZE


PAGESIZE 24