Previous | Table of Contents | Next

Page 119

CHAPTER 8

SQL*Plus for Administrators

In this chapter

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).

Administering SQL*Plus

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.

Using SQL*Plus Environment Variables

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 directory—the 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:

  1. Select Run from the Start menu.
  2. Enter regedit.exe/regedit32.exe (for Windows 95/NT, respectively).
  3. Click OK.
  4. Double-click HKEY_LOCAL_MACHINE.
  5. Double-click SOFTWARE.
  6. Double-click ORACLE.
  7. Double-click SQLPATH.
  8. The Edit String dialog box appears. In the Value Data field, enter the new value for SQLPATH.
  9. Click OK.
  10. From the Registry menu, select Exit.
  11. Reboot your machine for the new value to take effect (or log out and log back in
    Windows NT 4.0).

NOTE
SQLPATH is also used by SQL*Plus to identify the location of SQL scripts that you run from SQL*Plus.

There is another environment variable that can be set in glogin.sql or login.sql. This environment variable is called _editor. It defines the editor you can use to edit SQL*Plus commands.

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

Invoking/Accessing SQL*Plus

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.

Editing SQL Commands

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.

Previous | Table of Contents | Next