SQL*Plus
User's Guide and Reference
Release 8.1.6 A75664-01 |
|
Manipulating Commands, 3 of 7
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.
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:
Because SQL*Plus commands are not stored in the buffer, you
must use one of the latter two methods to save SQL*Plus commands.
To save the current SQL command or PL/SQL block for later use, enter the SAVE command. Follow the command with a file name:
SQL> SAVE file_name
SQL*Plus adds the extension SQL 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 you wish to use
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.
First, LIST the buffer contents to see your current command:
SQL> LIST 1 SELECT DEPTNO, ENAME, SAL 2 FROM EMP 3 WHERE DEPTNO = 10 4* ORDER BY SAL DESC
If the query shown is not in your buffer, re-enter the query now. Next, enter the SAVE command followed by the filename DEPTINFO:
SQL> SAVE DEPTINFO Created file DEPTINFO
You can verify that the command file DEPTINFO exists by entering the SQL*Plus HOST command followed by your host operating system's file listing command:
SQL> HOST your_host's_file_listing_command
You can use the same method to save a PL/SQL block currently
stored in the buffer.
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.
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:
SQL> CLEAR BUFFER
Next, use INPUT to enter the command (be sure not to type a semicolon at the end of the command):
SQL> INPUT 1 COLUMN ENAME HEADING SALESMAN 2 COLUMN SAL HEADING SALARY FORMAT $99,999 3 COLUMN COMM HEADING COMMISSION FORMAT $99,990 4 SELECT EMPNO, ENAME, SAL, COMM 5 FROM EMP 6 WHERE JOB = 'SALESMAN' 7
The zero at the end of the format model for the column COMM
tells SQL*Plus to display a zero instead of a blank when the value of COMM
is zero for a given row. Format models and the COLUMN command are described
in more detail in Chapter 4 and in the Oracle
8i SQL Reference Manual.
Now use the SAVE command to store your query in a file called SALES with the extension SQL:
SQL> SAVE SALES Created file SALES
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 /
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:
SQL> 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.
You can enter comments in a command file in one of three ways:
Anything that is identified in one of these ways as a comment is not parsed or executed by SQL*Plus.
Note: You cannot enter a comment on the same line after a semicolon. |
Use the REMARK command on a line by itself in the 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 Commissions report REMARK to be run monthly. COLUMN ENAME HEADING SALESMAN COLUMN SAL HEADING SALARY FORMAT $99,999 COLUMN COMM HEADING COMMISSION FORMAT $99,990 REMARK Includes only salesmen. SELECT EMPNO, ENAME, SAL, COMM FROM EMP WHERE JOB = 'SALESMAN'
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.
The comments can span multiple lines, but cannot be nested within one another:
/* Commissions report to be run monthly. */ COLUMN ENAME HEADING SALESMAN COLUMN SAL HEADING SALARY FORMAT $99,999 COLUMN COMM HEADING COMMISSION FORMAT $99,990 SELECT EMPNO, ENAME, SAL, COMM FROM EMP WHERE JOB = 'SALESMAN' /* Includes only salesmen. */
If you enter a SQL comment directly at the command prompt,
SQL*Plus does not store the comment in the buffer.
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 -- set the heading for ENAME to be SALESMAN COLUMN ENAME HEADING SALESMAN
These comments are illegal:
SET LONG 777 -- set maximum width for LONG to 777 SET -- set maximum width for LONG to 777 LONG 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
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:
SQL> 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.
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
SQL> GET SALES 1 COLUMN ENAME HEADING SALESMAN 2 COLUMN SAL HEADING SALARY FORMAT $99,999 3 COLUMN COMM HEADING COMMISSION FORMAT $99,990 4 SELECT EMPNO, ENAME, SAL, COMM 5 FROM EMP 6* WHERE JOB = 'SALESMAN' 7
SQL*Plus retrieves the contents of the file SALES with the
extension SQL into the SQL buffer and lists it on the screen. Then you
can edit the command further. If the file did not contain SQL*Plus commands,
you could also execute it with the RUN command.
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/or SQL*Plus commands. 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.
To retrieve and run the command stored in SALES.SQL, enter
SQL> 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:
EMPNO SALESMAN SALARY COMMISSION ----- --------- -------- ----------- 7499 ALLEN $1,600 $300 7521 WARD $1,250 $500 7654 MARTIN $1,250 $1,400 7844 TURNER $1,500 $0
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:
SQL> @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.
To run a command file as you start SQL*Plus, use one of the following four options:
SQLPLUS SCOTT/TIGER @SALES
SQL*Plus starts and runs the command file.
SQLPLUS SCOTT @SALES
SQL*Plus prompts you for your password, starts, and runs the command file.
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 @@ command in Chapter 8 for more information. |
You can modify an existing command file in two ways:
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:
SQL> 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:
SQL> GET MYREPORT 1* SELECT * FROM EMP SQL> C/*/ENAME, JOB 1* SELECT ENAME, JOB FROM EMP SQL> 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:
SQL> SAVE file_name APPEND
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.
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 manual(s) 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. See for more information on the relationship of
Site and User Profiles.)
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:
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.
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 run a report that alters system
variables and you want to reset their values after the report has finished.
To store the current setting of all system variables, enter
SQL> 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.
To restore the stored system variables, enter
SQL> START file_name
If the file has the default extension (as specified by the
SET
SUFFIXcommand), 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.
To store the current values of the SQL*Plus system variables in a new command file "plusenv.sql":
SQL> STORE SET plusenv Created file plusenv
Now the value of any system variable can be changed:
SQL> SHOW PAGESIZE pagesize 24 SQL> SET PAGESIZE 60 SQL> SHOW PAGESIZE pagesize 60
The original values of the system variables can then be restored from the command file:
SQL> START plusenv SQL> SHOW PAGESIZE pagesize 24
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|