< previous page page_27 next page >
Page 27
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, Advanced
Scripting.
Password
The PASSWORD command allows you to change your database password.
The PASSWORD command was introduced beginning with
SQL*Plus version 8. In prior versions, you need to use the ALTER USER
command to change a password. To change other people's passwords, you need
the ALTER USER system privilege.
The syntax for the PASSWORD command is:
PASSWORD [username]
where:
PASSW[ORD]
May be abbreviated to PASSW.
username
Is the user whose password you want to change. Usually only database administrators (DBAs) can change passwords
for other users. You do not need to supply a username if you are changing your own password.
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.

< previous page page_27 next page >
< previous page page_28 next page >
Page 28
Help
The HELP command is used to get help on SQL statements, SQL*Plus commands, and PL/SQL commands.
SQL*Plus 8.1, released with Oracle8i, provides help only on
SQL*Plus commands. This is because there wasn't time for Oracle to update the
help text to cover all the new functionality in SQL and PL/SQL in time for the
8i release. Subsequent versions of SQL*Plus should reenable SQL and PL/SQL
help.
The syntax for HELP is:
HELP [topic]
where:
Help
May not be abbreviated.
topic
Is the help topic you want to read about. Most SQL commands, SQL*Plus commands and PL/SQL statements are valid
help topics. There are others as well. Entering HELP MENU will get you a complete list of valid topics.
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.
Beginning with version 8.1 of SQL*Plus, you need to use HELP
INDEX instead of HELP MENU.


< previous page page_28 next page >
< previous page page_29 next page >
Page 29
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 data functions overview

The HELP command is not available from the Windows versions of
SQL*Plus. If you are running in a Windows 95 or NT environment, you will need
to refer to the appropriate Oracle manuals for information on SQL*Plus, SQL, and
PL/SQL commands. These manuals are available on the distribution CD-ROM,
and, beginning with version 7.3.3, are in HTML format.
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.
On most Oracle distribution CD-ROMs, you can get to the HTML documentation by opening a file named INDEX.HTM
in the DOC directory.

< previous page page_29 next page >
< previous page page_30 next page >
Page 30
Figure 2-6.
The No Help dialog
Describe
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,
Introduction to SQL*Plus, 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:
The column's name

The column's datatype, and length if applicable
Whether or not the column can be null
See Chapter 6, Exploring Your Database, for more information about DESCRIBE, including examples of its use against
procedures, functions, packages, synonyms, and object types.
Connect
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

< previous page page_30 next page >
< previous page page_31 next page >
Page 31
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 {SYSCOPER¦SYSDBA}] ¦ [INTERNAL]
where:
CONN[ECT]
May be abbreviated CONN.
username
Is your database username.
password
Is your database password.
connect
Is the connect string, or host string, telling SQL*Plus the database to which you want to connect.
/ Use a forward slash instead of your username, password, and connect string when you want to connect to a local
database using operating system authentication.
AS Tells SQL*Plus that you are connecting in an administrative role.

SYSOPER
Tells SQL*Plus that you are connecting as an operator.
SYSDBA
Tells SQL*Plus that you are connecting as a database administrator.
INTERNAL
Tells SQL*Plus that you want to connect internally.
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
pass-

< previous page page_31 next page >
< previous page page_32 next page >
Page 32
word. 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.
In at least one version of SQL*Plus version 8.0.4, there is a bug that
keeps this from working. You can enter CONNECT with the username as an
argument, then enter the password when prompted, but SQL*Plus won't pass
the correct information to the database.
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.
Disconnect
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:
DISC[ONNECT]
May be abbreviated to DISC.
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

< previous page page_32 next page >
< previous page page_33 next page >
Page 33
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:
1. Reconnect to Oracle using the CONNECT command.
2. Exit SQL*Plus.
3. Execute SQL*Plus commands that do not require a database connection. The SET command, for example, does not
require you to be connected.

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.
Running SQL Queries
Using SQL*Plus, you can execute any SQL query or command that you desire. This includes data manipulation
commands such as INSERT, UPDATE, DELETE, and SELECT. This also includes data definition commands such as
CREATE TABLE, CREATE INDEX, CREATE USER, etc. Essentially, you can execute any command listed in the
Oracle SQL reference manual.
Here is an example of a simple SELECT statement against the PROJECT table:
SQL> SELECT * /* All Columns */
2 FROM project;

PROJECT_ID PROJECT_NAME PROJECT_BUDGET

1001 Corporate Web Site 1912000
1002 Year 2000 Fixes 999998000
1003 Accounting System Implementation 897000
1004 Data Warehouse Maintenance 294000
1005 TCP/IP Implementation 415000
Look again at the SELECT query shown above. Notice that the statement spans more than one line. Notice that it
contains an embedded comment. Notice that it ends with a semicolon. All of these things are important because they
illustrate the following rules for entering SQL statements:
SQL statements may span multiple lines.
Line breaks may occur anywhere SQL allows whitespace, but blank lines are not allowed.
Comments, delimited by /* */, may be embedded anywhere whitespace is allowed. A comment entered this way may
span multiple lines.

< previous page page_33 next page >
< previous page page_34 next page >
Page 34
SQL statements must be terminated in one of three ways:

The statement may end with a trailing semicolon.
The statement may end with a forward slash character, but the forward slash must be on a line by itself and it must
be in column 1 of that line.
The statement may end with a blank line, in which case it will be stored in the SQL buffer rather than be executed
immediately.
Pay close attention to the three ways to terminate a SQL statement. The reason you have to worry about this at all is
because they can span multiple lines, and when you press ENTER for a new line, SQL*Plus needs some way of
knowing whether you are done with the statement or whether you just want to continue it on another line. Until you
enter a semicolon, a forward slash, or a blank line, SQL*Plus assumes that you are continuing your statement from one
line to the next.
SQL*Plus 8.1, which ships with Oracle8i, implements a feature
allowing you to include blank lines in SQL queries. For compatibility reasons,
this feature is not enabled by default. You can turn it on by issuing the
command SET SQLBLANKLINES ON.
I usually recommend terminating SQL statements with semicolons, because I think that's the simplest and cleanest-
looking method. The SELECT statement above shows a semicolon at the end of the line, but if you forget and hit
ENTER too quickly, you can also put it on the next line by itself. For example:
SQL> INSERT INTO project
2 /* all columns */
3 (project_id, project_name, project_budget)
5 VALUES (1006, Mainframe Upgrade, 456789)
6 ;
1 row created.
You may also use the forward slash character to terminate a SQL statement, but remember that it must be on a line by
itself and must be the first and only character on that line. Here is an example:
SQL> UPDATE project
2 SET project_budget = 1000000
3 WHERE project_id = 1006
4 /
1 row updated.


< previous page page_34 next page >
< previous page page_35 next page >
Page 35
You will read more about the forward slash character later in this chapter because it's also used to execute the SQL
statement, or PL/SQL block, currently in the SQL buffer.
The final option for terminating an SQL statement is to simply press ENTER on a blank line. There is a catch to this
method, though. Here's an example:
SQL> DELETE
2 FROM project
3 WHERE project_id = 1006
4
SQL>
Look carefully at the preceding example. Notice that nothing happened! You typed in the DELETE command, pressed
ENTER on a blank line, got back another SQL> prompt, but SQL*Plus said nothing about deleting the row that you just
asked to delete. Why? Because when you terminate an SQL query with a blank line, SQL*Plus stores that command in
an internal buffer but does not execute it. You'll read more about this later in this chapter in the section titled The SQL
Buffer. For now, though, if you haven't entered any other commands after the DELETE statement shown above, just
type a forward slash on a line by itself and press ENTER.
SQL> /

1 row deleted.
Now the DELETE statement has been executed, and the row deleted. The forward slash tells SQL*Plus to execute the
SQL command most recently entered.
If you have been following along with SQL*Plus, and entering the examples while reading this section, you've probably
noticed a couple of things. First, it's a pain when you make a mistake. Second, it's even worse when that mistake is on a
previous line. If you were using the GUI version of SQL*Plus, you may have even tried to arrow up to correct a
mistyped line. Well, don't waste your timeyou can't do that. However, SQL*Plus does have some built-inline-editing
capabilities, and it can also call the text editor of your choice. You'll read about these options in just a bit, after the next
section on executing PL/SQL blocks.

Working with PL/SQL
PL/SQL is a programming language developed by Oracle as an extension to SQL in order to allow procedural logic to
be implemented at the database level. PL/SQL is used to write stored procedures, stored functions, and triggers, and,
beginning with Oracle8, to define object types. It can also be used to simply write a block of procedural code for the
database to execute. SQL*Plus was originally one of the

< previous page page_35 next page >
< previous page page_36 next page >
Page 36
only front-ends that could be used to send PL/SQL code to the database, and even today it is still one of the most widely
used.
This section explains the mechanics of entering and executing PL/SQL code with SQL*Plus. You'll learn what PL/SQL
mode is, and you'll learn the differences between entering a PL/SQL block and a SQL query.
If you are unfamiliar with PL/SQL, you may want to pick up a copy of Steven Feuerstein and Bill Pribyl's book, Oracle
PL/SQL Programming, second edition (O'Reilly & Associates, 1997). PL/SQL opens up a world of possibilities. You'll
want to take advantage of it if you are doing serious work with Oracle.
What Is a PL/SQL Block?
The PL/SQL block is the fundamental unit of PL/SQL programming. The term block refers to a program unit that
contains some or all of the following elements:
Variable and subprogram declarations
Procedural code, which may include nested PL/SQL blocks
An error handler
Here is an example of a reasonably simple, but complete, PL/SQL block:
DECLARE
X VARCHAAR2(12) := Hello World!;
BEGIN
DBMS_OUTPUT. PUT_LINE(X);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(An error occurred.);

END;
This code contains all the elements of a PL/SQL block, and is one implementation of the traditional Hello World!
program. Using SQL*Plus, you can send it to the database for execution.
Executing a PL/SQL Block
To execute a PL/SQL block, you type it into SQL*Plus and terminate it with a forward slash. The forward slash tells
SQL*Plus that you are done entering the block and to send it to the database for execution. Here's how it would look to
enter and execute the code shown previously:
SQL> DECLARE
2 X VARCHAR2(12) := Hello World!;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(X);
5 EXCEPTION
6 WHEN OTHERS THEN

< previous page page_36 next page >