Page 119
Page 120
This chapter discusses SQL*Plus, Oracle's implementation of the Structured Query Language (SQL). SQL*Plus is an interactive program used to access an Oracle database. It is also the database administrator's best friend and an indispensable tool.
In addition to SQL*Plus, a database administrator (DBA) can interface with the database and use SQL*Plus commands (but only a subset of the commands) in two other tools:
Note that Server Manager replaces, beginning with Oracle7 7.3, the SQL*DBA utility that was used in Oracle version 6 and Oracle7 releases prior to 7.3.
Because SQL*Plus is such a vast subject, which cannot be dealt with in a single chapter, I will concentrate mainly on features of interest to DBAs, new features (EXECUTE and AUTOTRACE), and lesser-known and lesser-used features (COPY command, disabling commands).
There are two files, glogin.sql and login.sql, that are used to administer SQL*Plus. The glogin.sql file is the global setup file, and login.sql is intended for individual use. These two files contain SQL*Plus commands and/or SQL statements that are executed every time an Oracle user invokes SQL*Plus. The glogin.sql file is read and executed first, followed by the user's login.sql file.
The glogin.sql file is located in the $ORACLE_HOME/sqlplus/admin directory. The Oracle DBA may customize this file to include SQL*Plus commands, SQL statements, and PL/SQL blocks that will be executed by every SQL*Plus user at the beginning of his or her SQL*Plus session. The glogin.sql file is also known as a Site Profile.
NOTE |
Under Windows 95/NT 4.0, the glogin.sql file is located in the directory %ORACLE_HOME%\PLUSnn, where nn represents the version of SQL*Plus installed on your machine. For example, if SQL*Plus 3.2 is installed, the directory name will be %ORACLE_HOME%\PLUS32. |
There are two environment variables that are used by SQL*Plus:
SQL*Plus uses the environment variable SQLPATH to identify the directory where the login.sql file is located. In other words, SQL*Plus will look in every directory defined in SQLPATH for login.sql, starting with the local directorythe directory you were in when you started SQL*Plus.
Page 121
For example, if you want SQL*Plus to look for login.sql first in the local directory, then in your home directory, and then in another directory, set SQLPATH as follows:
$ SQLPATH=".:$HOME:<other_directory>"; export SQLPATH --(Bourne/Korn shell) $ set SQLPATH=(. $HOME <other_directory>) -- C shell
Under Windows 95/NT 4.0, SQLPATH is defined in the Registry. The default value is $ORACLE_HOME\DBS (set during installation). The value for ORACLE_HOME is C:\ORAWIN95 for Windows 95 and C:\ORANT for Windows NT 4.0 (replace C: with the name of the disk drive where you installed Oracle, if you did not install it on your C: drive).
NOTE |
Under Windows 95/NT 4.0, the login.sql file is located in directory %ORACLE_HOME%\DBS, the default value for SQLPATH. |
To set or change the value of SQLPATH under Windows 95/NT 4.0, follow these steps:
NOTE |
SQLPATH is also used by SQL*Plus to identify the location of SQL scripts that you run from SQL*Plus. |
To set the value of _editor to the vi text editor, enter the following line in glogin.sql or login.sql:
define _editor=vi
If you use any other text editor, replace vi with the appropriate name. For more information on using different editors, see the section "Using Your Operating System Editor in SQL*Plus," later in this chapter.
Page 122
To invoke SQL*Plus from the operating system prompt, use the following command:
$ sqlplus [[-S[ILENT]] [logon] [start]]|-?
The -S[ILENT]] parameter is used when running SQL*Plus from a shell script, because it suppresses all the information that SQL*Plus displays when invoked, such as the SQL*Plus banner, prompt messages, and the command prompt.
The [logon] section requires the following syntax:
username[/password] [@connect_string]|/|/NOLOG
The [start]] clause enables you to start SQL*Plus and run a command file containing any combination of SQL*Plus commands, SQL statements, and/or PL/SQL blocks. In addition, you can pass arguments to the command file. The start clause requires the following syntax:
@file_name[.ext] [arg...]
If you do not enter the username or the password, SQL*Plus prompts you to enter them.
After you successfully access SQL*Plus, you can enter three type of commands at the SQL*Plus prompt (SQL >):
To submit a SQL command to SQL*Plus, enter a semicolon (;) at the end of the command and press Enter. SQL*Plus executes the command, displays the results of the query, and returns you to the prompt.
To end a PL/SQL block, enter a period (.) on a line by itself. To submit a PL/SQL block for execution, terminate it with a slash (/) on a line by itself and press Enter.
If you make a mistake when entering a SQL command and want to correct it, or if you want
to run the last command with only a minor change, you are lucky! SQL*Plus stores the
most recently entered SQL statement in a buffer, appropriately called the
SQL buffer. SQL*Plus provides a set of commands to retrieve and edit SQL statements and PL/SQL blocks stored
in the buffer.
Note that SQL*Plus commands are not saved in the buffer. Therefore, they cannot be retrieved and modified. However, there is a way to store SQL*Plus commands in the buffer. This method is discussed in the section "Entering and Editing SQL*Plus Commands," later in this chapter.
Table 8.1 lists the commands used to view, edit, and run the contents of the SQL*Plus buffer.