A good place to start, since you’ve just seen how to start SQL*Plus, might be with the EXIT command. The EXIT command terminates your SQL*Plus session, and either closes the SQL*Plus window (GUI version) or returns you to the operating system prompt. Used in its simplest form, the EXIT command looks like this:

SQL> EXIT
Disconnected from Personal Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.3.0.0 - Production

There are some optional arguments to the EXIT command, which may be used to return success or failure values to the operating system. These are useful when running scripts in batch mode, and are described fully in Chapter 7.

The PASSWORD command allows you to change your database password.

The syntax for the PASSWORD command is:

PASSWORD [username]

where:

Here is an example showing how the PASSWORD command is used:

SQL> PASSWORD
Changing password for JONATHAN
Old password: *******
New password: *******
Retype new password: *******
Password changed

If you are running a version of SQL*Plus prior to version 8, the PASSWORD command will not be available. Instead, use the ALTER USER command to change your password. Here’s how:

SQL> ALTER USER jonathan IDENTIFIED BY wonker;

As you can see, the PASSWORD command has the advantage of not showing the new password on the screen.

The HELP command is used to get help on SQL statements, SQL*Plus commands, and PL/SQL commands.

The syntax for HELP is:

HELP [topic]

where:

Help is not available on all implementations. The Windows versions of SQL*Plus simply don’t implement the feature. Under Unix, the HELP command will work only if your DBA has installed that feature in the database. You will know SQL*Plus help has not been installed if you enter the HELP command and get the results shown in this example:

SQL> HELP
HELP not accessible.

SQL*Plus reads help text from a database table named HELP, owned by the user SYSTEM. You will get the “HELP not accessible” message if that table does not exist, or if SQL*Plus cannot select from it because of some other problem. Assuming the help feature has been installed, you can get a list of the various help topics available by using the HELP MENU command.

Here’s an example of how HELP MENU can be used to get a list of help topics:

SQL> HELP MENU

                                     Menu

 SQL TOPICS         PL/SQL TOPICS      FUNCTIONS           SQL*PLUS TOPICS
 ---------------    ----------------   ----------------    ---------------
 alias              attributes         1 row number        help
 comments           blocks             1 row char          commands
 conditions         commands           conversion          old commands
 data dictionary    cursors            date functions      overview
...

After identifying a topic of interest, you can get further help by using that topic name as an argument to the HELP command. Here is the information HELP gives you about the DESCRIBE command:

SQL> HELP DESCRIBE

                                  DESCRIBE

 DESC[RIBE] {[user.]table[@database_link_name] [column] |
 [user.] object[.subobject]}

 DESCRIBE lists the column definitions for a table, view, or synonym,
 or the specifications for a function, procedure, package, or
 package contents.
...

Entering HELP without an argument will get you help on using HELP.

If you are running under Windows NT or 95, Oracle prefers to point you to the manual set included on the distribution CD-ROM. The HELP command itself will still work; however, you will get the dialog box shown in Figure 2.6, which points you to the documentation on the CD-ROM.

Figure 2-6. The “No Help” dialog

On most Oracle distribution CD-ROMs, you can get to the HTML documentation by opening a file named INDEX.HTM in the DOC directory.

The DESCRIBE command lists the column definitions for a database table. It can also be used to view procedure, function, package, and object definitions. If you have created and loaded the sample tables described in Chapter 1, you can use the DESCRIBE command to view their column definitions. The following example shows how DESCRIBE is used to list the columns in the EMPLOYEE table:

SQL> DESCRIBE employee
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER
 EMPLOYEE_NAME                            VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_TERMINATION_DATE                DATE
 EMPLOYEE_BILLING_RATE                    NUMBER

As you can see, the command lists three things for each column in the table:

See Chapter 6, for more information about DESCRIBE, including examples of its use against procedures, functions, packages, synonyms, and object types.

The CONNECT command lets you log into your database as a different user, or log into a completely different database. It’s useful if you develop against, or manage, more than one database, because you can quickly switch between them when you need to. It’s also quite common for a developer or DBA to have multiple usernames on one database, with each being used for a different purpose. A DBA might log in as SYSTEM in order to create users and manage tablespaces, but might choose to log in with a less privileged username when running reports.

The complete syntax for CONNECT is:

CONNECT [username[/password][@connect]|/|] [AS {SYSOPER|SYSDBA}]|[INTERNAL]

where:

Everything beginning with the keyword AS applies when you are using SQL*Plus for database administration tasks. Chapter 10 is devoted to that subject, and explains the use of the SYSOPER, SYSDBA, and INTERNAL keywords.

The simplest way to use the CONNECT command is to use it by itself, with no arguments, as shown here:

SQL> CONNECT
Enter user-name: jonathan
Enter password: *******
Connected.

In the case above, SQL*Plus prompted for both a username and a password. Notice, too, that the password characters were echoed back as asterisks. This prevents anyone from looking over your shoulder as you type and stealing your password. SQL*Plus did not prompt for a connect string, and won’t, either, so using the above method only allows you to connect to your default database.

Another form of the CONNECT command allows you to specify the username, password, and connect string all on one line. This is the version I use most frequently as I switch back and forth between databases that I manage. For example:

SQL> CONNECT system/driveway@plum
Connected.

If you are security conscious (you should be) and happen to have someone looking over your shoulder, you may omit the password and let SQL*Plus prompt you for it. The advantage here is that the password characters will be echoed to the screen as asterisks. For example:

SQL> CONNECT system@oak
Enter password:*******
Connected.

Go ahead and try the CONNECT command a few times, trying the variations shown above. If you only have one username you can use, try reconnecting as yourself just to get the hang of it.

The DISCONNECT command is one I rarely use. It’s the analog of the CONNECT command, and simply disconnects you from the Oracle database while leaving you in SQL*Plus. The syntax is very simple, and looks like this:

DISCONNECT

where:

Here is an example of the DISCONNECT command being used:

SQL> DISCONNECT
Disconnected from Personal Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.3.0.0 - Production
SQL>

Any pending transactions are committed before you are disconnected from Oracle. At this point you have three choices:

DISCONNECT is useful if you want to leave a SQL*Plus session open for a long period of time, but do not wish to tie up a database connection.