Learning SQL*Plus Basics
Read this chapter while sitting at your computer and try out the examples
shown. Before beginning, make sure you have access to the sample tables
described in Chapter 1.
Getting Started
To begin using SQL*Plus, you must first become familiar with the functions
of several keys on your keyboard and understand how to start and leave
SQL*Plus.
Using the Keyboard
Several keys on your keyboard have special meaning in SQL*Plus.
Table 2 - 1 lists these keys.
See your Oracle installation and user's manual(s) for your operating
system to learn which physical key performs each function on the keyboard
commonly used with your host computer.
Note: A SQL*Plus key may perform different functions when
pressed in other products or the operating system.
Fill in each blank in Table 2 - 1
with the name of the corresponding keyboard key. Then locate each key on
your keyboard.
| SQL*Plus Key Name |
Keyboard Key Name |
Function |
| [Return] |
___________ |
End a line of input. |
| [Backspace] |
___________ |
Move cursor left one character to correct an
error. |
| [Pause] |
___________ |
Suspend program operation and display of output. |
| [Resume] |
___________ |
Resume program operation and output [Pause]. |
| [Cancel] |
___________ |
Halt program operation; return to the SQL*Plus
command prompt. |
| [Interrupt] |
___________ |
Exit SQL*Plus and return to the host operating
system. |
| Table 2 - 1. SQL*Plus
Special Keys and their Functions |
|
|
Starting SQL*Plus
Now that you have identified important keys on your keyboard, you are ready
to start SQL*Plus.
Example 2-1 Starting SQL*Plus
This example shows you how to start SQL*Plus.
Follow the steps shown.
1. Make sure that Oracle has been installed on your computer.
2. Turn on your computer (if it is off) and log on to the host operating
system (if required). If you are already using your computer, you need
not log off or reset it. Simply exit from the program you are using (if
any).
You should see one or more characters at the left side of the screen.
This is the operating system's command prompt,
which signals that the operating system is ready to accept a command. In
this Guide the operating system's prompt will be represented by a dollar
sign ($). Your computer's operating system prompt may be different.
3. Enter the command SQLPLUS and press [Return].
This is an operating system command that starts SQL*Plus.
Note: Some operating systems expect you to enter commands in lowercase
letters. If your system expects lowercase, enter the SQLPLUS command in
lowercase.
SQL*Plus displays its version number, the date, and copyright information,
and prompts you for your username (the text displayed on your system may
differ slightly):
SQL*Plus: Release 8.0 - on Fri Jun 27 09:39:26 1997
(c) Copyright 1997 Oracle Corporation. All rights reserved.
4. Enter your username
and press [Return]. SQL*Plus displays the prompt "Enter password:".
5. Enter your password and press [Return] again.
For your protection, your password does not appear on the screen.
The process of entering your username and password is called logging
in. SQL*Plus displays the version of Oracle
to which you connected and the versions of available tools such as PL/SQL.
Next, SQL*Plus displays the SQL*Plus command
prompt:
The command prompt indicates that SQL*Plus is ready to accept your
commands.
If SQL*Plus does not start, you should see a message meant to help you
correct the problem. For further information, refer to the Oracle8 Server
Messages manual for Oracle messages, or to your operating system manual
for system messages.
Shortcuts to Starting SQL*Plus
When you start SQL*Plus,
you can enter your username and password, separated by a slash (/), following
the command SQLPLUS. For example, if your username is SCOTT and your password
is TIGER, you can enter
$ SQLPLUS SCOTT/TIGER
and press [Return]. You can also arrange to log in to SQL*Plus automatically
when you log on to your host operating system. See the Oracle installation
and user's manual(s) provided for your operating system for details.
Leaving SQL*Plus
When you are done working with SQL*Plus
and wish to return to the operating system, enter the EXIT
command at the SQL*Plus command prompt.
Example 2-2 Exiting SQL*Plus
To leave SQL*Plus, enter the EXIT command at the SQL*Plus command
prompt:
SQL> EXIT
SQL*Plus displays the version of Oracle from which you disconnected and
the versions of tools available through SQL*Plus. After a moment you will
see the operating system prompt.
Before continuing with this chapter, follow steps 3, 4, and 5
of Example 2-1 to start SQL*Plus again.
Alternatively, log in using the shortcut shown under "Shortcuts
to Starting SQL*Plus" above.
Entering and Executing Commands
Entering Commands
Your computer's cursor, or pointer (typically an underline, a rectangular
block, or a slash), appears after the command prompt. The cursor indicates
the place where the next character you type will appear on your screen.
To tell SQL*Plus what to do, simply type the command you wish
to enter. Usually, you separate the words in a command from each other
by a space or tab. You
can use additional spaces or tabs between words, if you wish, to make your
commands more readable.
Note: You will see examples of spacing and indentation
throughout this Guide. When you enter the commands in the exercises, you
do not have to space them as shown, but you may find them clearer to read
if you do.
You can enter commands in capitals or
lowercase. For the sake of clarity, all table names, column names, and
commands in this Guide appear in capital letters.
You can enter three kinds of commands
at the command prompt:
-
SQL commands, for working with information in the database
-
PL/SQL blocks, also for working with information in the database
-
SQL*Plus commands, for formatting query results, setting options, and editing
and storing SQL commands and PL/SQL blocks
The manner in which you continue a command on additional lines, end a command,
or execute a command differs depending on the type of command you wish
to enter and run. Examples of how to run and execute these types of commands
are found on the following pages.
Getting Help
To get online help for SQL*Plus commands, type HELP at the command prompt
followed by the name of the command.
For example:
SQL>HELP ACCEPT
If you get a response indicating that help is not available, consult your
database administrator. For more details about the help system, see the
HELP command in Chapter 7.
Executing Commands
After you enter the command and direct SQL*Plus to execute it, SQL*Plus
processes the command and re-displays the command prompt, indicating that
you can enter another command.
Running SQL Commands
The SQL command language enables you to manipulate
data in the database. See your Oracle8 Server SQL Reference Manual
for information on individual SQL commands.
Example 2-3 Entering a SQL Command
In this example, you will enter and execute a SQL command to display
the employee number, name, job, and salary of each employee in the sample
table EMP.
1. At the command prompt, enter the first line
of the command:
SQL> SELECT EMPNO, ENAME, JOB, SAL
If you make a mistake, use [Backspace] to erase it and re-enter. When
you are done, press [Return] to move to the next line.
2. SQL*Plus will display a "2", the prompt for the second line.
Enter the second line of the command:
2 FROM EMP WHERE SAL < 2500;
The semicolon (;)
means that this is the end of the command. Press [Return]. SQL*Plus processes
the command and displays the results on the screen:
EMPNO ENAME JOB SAL
---------- ------------ ---------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7782 CLARK MANAGER 2450
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 800
7934 MILLER CLERK 1300
9 rows selected
SQL>
After displaying the results and the number of rows retrieved,
SQL*Plus displays the command prompt again. If you made a mistake and therefore
did not get the results shown above, simply re-enter the command.
The headings may be repeated in your output, depending on the setting
of a system variable called PAGESIZE.
Whether you see the message concerning the number of records retrieved
depends on the setting of a system variable called FEEDBACK. You will learn
more about system variables later in this chapter in the section "Variables
that Affect Running Commands". To save space, the number of records
selected will not be shown in the rest of the examples in this Guide.
Understanding SQL Command Syntax
Just as spoken language has syntax rules that govern
the way we assemble words into sentences, SQL*Plus has syntax rules that
govern how you assemble words into commands. You
must follow these rules if you want SQL*Plus to accept and execute your
commands.
Dividing a SQL Command into Separate Lines You can divide
your SQL command into separate lines at any points
you wish, as long as individual words are not split between lines. Thus,
you can enter the query you entered in Example
2-3 on one line:
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL < 2500;
You can also enter the query on several lines:
SQL> SELECT
2 EMPNO, ENAME, JOB, SAL
3 FROM EMP
4 WHERE SAL < 2500;
In this Guide, you will find most SQL commands divided into clauses, one
clause on each line. In Example 2-3, for
instance, the SELECT and FROM clauses were placed on separate lines. Many
people find this most convenient, but you may choose whatever line division
makes your command most readable to you.
Ending a SQL Command You can end a SQL command
in one of three ways:
-
with a slash (/) on a line by itself
A semicolon (;) tells SQL*Plus that you want to
run the command. Type the semicolon at the end of the last line of the
command, as shown in Example 2-3, and press
[Return]. SQL*Plus will process the command and store it in the SQL buffer
(see "The SQL Buffer" below for details).
If you mistakenly press [Return] before typing the semicolon, SQL*Plus
will prompt you with a line number for the next line of your command. Type
the semicolon and press [Return] again to run the command.
Note: You cannot enter a comment (/* */) on the same line
after a semicolon.
A slash (/) on a line by itself also tells
SQL*Plus that you wish to run the command. Press [Return] at the end of
the last line of the command. SQL*Plus prompts you with another line number.
Type a slash and press [Return] again. SQL*Plus will execute the command
and store it in the buffer (see "The SQL Buffer"
below for details).
A blank line tells SQL*Plus that you have
finished entering the command, but do not want
to run it yet. Press [Return] at the end of the last line of the command.
SQL*Plus prompts you with another line number.
Press [Return] again; SQL*Plus now prompts you with the SQL*Plus
command prompt. SQL*Plus does not execute the command, but stores it in
the SQL buffer (see "The SQL Buffer" below
for details). If you subsequently enter another SQL command, SQL*Plus overwrites
the previous command in the buffer.
Creating Stored Procedures
Stored procedures are PL/SQL functions, packages, or procedures. To
create stored procedures, you use SQL CREATE commands. The following SQL
CREATE commands are used to create stored procedures:
Entering any of these commands places you in PL/SQL mode, where you can
enter your PL/SQL subprogram (see also "Running
PL/SQL Blocks" in this chapter). When you are done typing your PL/SQL
subprogram, enter a period (.) on a line by itself to terminate PL/SQL
mode. To run the SQL command and create the stored procedure, you must
enter RUN or slash (/). A semicolon (;) will not execute these CREATE commands.
When you use CREATE to create a stored procedure, a message appears
if there are compilation errors. To view these errors, you use SHOW ERRORS.
For example:
SQL> SHOW ERRORS PROCEDURE ASSIGNVL
See Chapter 7 for a description of the SHOW
command.
To execute a PL/SQL statement that references a stored procedure,
you can use the EXECUTE command. EXECUTE runs the PL/SQL statement that
you enter immediately after the command. For example:
SQL> EXECUTE :ID := EMP_MANAGEMENT.GET_ID('BLAKE')
See Chapter 7 for a description of the EXECUTE
command.
The SQL Buffer
The area where SQL*Plus stores your most recently entered SQL command or
PL/SQL block is called the SQL buffer. The
command or block remains there until you enter another. Thus, if you want
to edit or re-run the current SQL command or PL/SQL block, you may do so
without re-entering it. See Chapter 3 for details
about editing or re-running a command or block stored in the buffer.
SQL*Plus does not store the semicolon or the slash you type to
execute a command in the SQL buffer.
Note: SQL*Plus commands are not stored in the SQL buffer.
Executing the Current SQL Command or PL/SQL Block from the Command Prompt
You can run (or re-run) the current SQL command
or PL/SQL block by entering the RUN
command or the slash (/) command
at the command prompt. The RUN command lists the
SQL command or PL/SQL block in the buffer before
executing the command or block; the slash (/) command
simply runs the SQL command or PL/SQL block.
Running PL/SQL Blocks
You can also use PL/SQL
subprograms (called blocks) to manipulate
data in the database. See your PL/SQL User's Guide and Reference
for information on individual PL/SQL statements.
To enter a PL/SQL subprogram in SQL*Plus, you need to be in PL/SQL
mode. You are placed in PL/SQL mode when
-
You type DECLARE or BEGIN
at the SQL*Plus command prompt. After you enter PL/SQL mode in this way,
type the remainder of your PL/SQL subprogram.
-
You type a SQL command (such as CREATE FUNCTION) that creates a stored
procedure. After you enter PL/SQL mode in this way, type the stored procedure
you want to create.
SQL*Plus treats PL/SQL
subprograms in the same manner as SQL commands, except that a semicolon
(;) or a blank line does not terminate and
execute a block. Terminate PL/SQL subprograms by entering a period (.)
by itself on a new line.
SQL*Plus stores the subprograms you enter at the SQL*Plus command
prompt in the SQL buffer. Execute the current subprogram by issuing a RUN
or slash (/) command. Likewise, to execute
a SQL CREATE command that creates a stored procedure, you must also enter
RUN or slash (/). A semicolon (;) will not execute these SQL commands as
it does other SQL commands.
SQL*Plus sends the complete PL/SQL subprogram to Oracle for processing
(as it does SQL commands). See your PL/SQL User's Guide and Reference
for more information.
You might enter and execute a PL/SQL subprogram as follows:
SQL> DECLARE
2 x NUMBER := 100;
3 BEGIN
4 FOR i IN 1..10 LOOP
5 IF MOD (i, 2) = 0 THEN --i is even
6 INSERT INTO temp VALUES (i, x, 'i is even');
7 ELSE
8 INSERT INTO temp VALUES (i, x, 'i is odd');
9 END IF;
10 x := x + 100;
11 END LOOP;
12 END;
13 .
SQL> /
PL/SQL procedure successfully completed.
When you run a subprogram, the SQL commands within the subprogram may behave
somewhat differently than they would outside the subprogram. See your PL/SQL
User's Guide and Reference for detailed information on the PL/SQL language.
Running SQL*Plus Commands
You can use SQL*Plus commands to manipulate SQL commands and PL/SQL blocks
and to format and print query results. SQL*Plus treats SQL*Plus commands
differently than SQL commands or PL/SQL blocks. For information on individual
SQL*Plus commands, refer to the Command Reference
in Chapter 7.
To speed up command entry, you can abbreviate many SQL*Plus commands
to one or a few letters. Abbreviations for some SQL*Plus commands are described
along with the commands in Chapters 3, 4,
and 5. For abbreviations of all SQL*Plus commands,
refer to the command descriptions in Chapter 7.
Example 2-4 Entering a SQL*Plus Command
This example shows how you might enter a SQL*Plus command to change
the format used to display the column SAL of the sample table EMP.
1. On the command line, enter this SQL*Plus command:
SQL> COLUMN SAL FORMAT $99,999 HEADING SALARY
If you make a mistake, use [Backspace] to erase it and re-enter. When
you have entered the line, press [Return]. SQL*Plus notes the new format
and displays the SQL*Plus command prompt again, ready for a new command.
2. Enter the RUN command to re-run the most recent query (from Example
2-3). SQL*Plus reprocesses the query and displays the results:
SQL> RUN
1 SELECT EMPNO, ENAME, JOB, SAL
2* FROM EMP WHERE SAL < 2500
EMPNO ENAME JOB SALARY
-------- ------------- ---------- --------
7369 SMITH CLERK $800
7499 ALLEN SALESMAN $1,600
7521 WARD SALESMAN $1,250
7654 MARTIN SALESMAN $1,250
7782 CLARK MANAGER $2,450
7844 TURNER SALESMAN $1,500
7876 ADAMS CLERK $1,100
7900 JAMES CLERK $800
7934 MILLER CLERK $1,300
The COLUMN command formatted the column SAL with a dollar sign ($) and
a comma (,) and gave it a new heading. The RUN command then re-ran the
query of Example 2-3, which was stored in
the buffer. SQL*Plus does not store SQL*Plus commands in the SQL buffer.
Understanding SQL*Plus Command Syntax
SQL*Plus commands have a different syntax from
SQL commands or PL/SQL blocks.
Continuing a Long SQL*Plus Command on Additional Lines You
can continue a long SQL*Plus command by typing
a hyphen at the end of
the line and pressing [Return]. If you wish, you can type a space before
typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt
for each additional line. For example:
SQL> COLUMN SAL FORMAT $99,999 -
> HEADING SALARY
Ending a SQL*Plus Command You do not need to end a SQL*Plus command
with a semicolon. When you finish entering the
command, you can just press [Return]. If you wish, however, you can enter
a semicolon at the end of a SQL*Plus command.
Variables that Affect Running Commands
The SQL*Plus command SET controls many variables--called
system variables--the settings of which
affect the way SQL*Plus runs your commands. System
variables control a variety of conditions within SQL*Plus, including default
column widths for your output, whether SQL*Plus displays the number of
records selected by a command, and your page size. System variables are
also called SET command variables.
The examples in this Guide are based on running SQL*Plus with
the system variables at their default settings. Depending on the settings
of your system variables, your output may appear slightly different than
the output shown in the examples. (Your settings might differ from the
default settings if you have a SQL*Plus LOGIN file on your computer.)
For more information on system variables and their default settings,
see the SET command in Chapter
7. For details on the SQL*Plus LOGIN file, refer to the section "Setting
Up Your SQL*Plus Environment" under "Saving
Commands for Later Use" in Chapter 3 and to the
SQLPLUS command in Chapter 6.
To list the current setting of a SET command variable,
enter SHOW followed by the variable name at the
command prompt. See the SHOW command in Chapter
7 for information on other items you can list with SHOW.
Saving Changes to the Database Automatically
Through the SQL DML commands UPDATE, INSERT, and
DELETE--which can be used independently or within a PL/SQL block--specify
changes you wish to make to the information stored in the database. These
changes are not made permanent until you enter a SQL COMMIT
command or a SQL DCL or DDL command (such as CREATE TABLE), or use the
autocommit feature. The SQL*Plus autocommit feature causes pending changes
to be committed after a specified number of successful SQL DML transactions.
(A SQL DML transaction is either an UPDATE, INSERT, or DELETE command,
or a PL/SQL block.)
You control the autocommit feature with the SQL*Plus SET
command's AUTOCOMMIT variable. It has these four
forms:
| SET AUTOCOMMIT ON |
Turns autocommit on. |
| SET AUTOCOMMIT OFF |
Turns autocommit off (the default). |
| SET AUTOCOMMIT n |
Commits changes after n SQL commands or
PL/SQL blocks. |
| SET AUTOCOMMIT IMMEDIATE |
Turns autocommit on. |
Example 2-5 Turning Autocommit On
To turn the autocommit feature on, enter
SQL> SET AUTOCOMMIT ON
Alternatively, you can enter the following to turn the autocommit feature
on:
SQL> SET AUTOCOMMIT IMMEDIATE
Until you change the setting of AUTOCOMMIT, SQL*Plus will automatically
commit changes from each SQL command or PL/SQL block that specifies changes
to the database. After each autocommit, SQL*Plus displays the following
message:
commit complete
When the autocommit feature is turned on, you cannot roll back changes
to the database.
To commit changes to the database after a number of SQL DML commands
or PL/SQL blocks, for example, ten, enter
SQL> SET AUTOCOMMIT 10
SQL*Plus counts SQL DML commands and PL/SQL blocks as they are executed
and commits the changes after the tenth SQL DML command or PL/SQL block.
Note: For this feature, a PL/SQL block is regarded as one
transaction, regardless of the actual number of SQL commands contained
within it.
To turn the autocommit feature off again, enter the following
command:
SQL> SET AUTOCOMMIT OFF
To confirm that AUTOCOMMIT is now set to OFF, enter the following SHOW
command:
SQL> SHOW AUTOCOMMIT
autocommit OFF
For more information, see the AUTOCOMMIT
variable of the SET command in Chapter
7.
Stopping a Command while it is Running
Suppose you have displayed the first page of a
50 page report and decide you do not need to see the rest of it. Press
[Cancel]. (Refer to Table
2 - 1 at the beginning of this chapter to see how [Cancel] is labelled
on your keyboard.) SQL*Plus will stop the display and return to the command
prompt.
Note: Pressing [Cancel] will not stop the printing of a
file that you have sent to a printer with the OUT clause of the SQL*Plus
SPOOL command. (You will learn about printing query results in Chapter
4.) You can stop the printing of a file through your operating system.
For more information, see the Oracle installation and user's manual(s)
provided for your operating system.
Collecting Timing Statistics on Commands You Run
Use the SQL*Plus command TIMING to collect and
display data on the amount of computer resources used to run one or more
commands or blocks. TIMING collects data for an
elapsed period of time, saving the data on commands run during the period
in a timer. See TIMING in Chapter
7 and the Oracle installation and user's manuals provided for your
operating system for more information.
To delete all timers, enter CLEAR TIMING at the command prompt.
Running Host Operating System Commands
You can execute a host operating system command
from the SQL*Plus command prompt. This is useful when you want to perform
a task such as listing existing host operating system files.
To run a host operating system command, enter the SQL*Plus command
HOST followed by the host operating system command.
For example, this SQL*Plus command runs a host command, DIRECTORY *.SQL:
SQL> HOST DIRECTORY *.SQL
When the host command finishes running, the SQL*Plus command prompt appears
again.
Getting Help
While you use SQL*Plus, you may find that you need to list column definitions
for a table, or start and stop the display that scrolls by. You may also
need to interpret error messages you receive when you enter a command incorrectly
or when there is a problem with Oracle or SQL*Plus. The following sections
describe how to get help for those situations.
Listing a Table Definition
To see the definitions of each column in a given table,
use the SQL*Plus DESCRIBE command.
Example 2-6 Using the DESCRIBE Command
To list the column definitions of the three columns in the sample
table DEPT, enter
SQL> DESCRIBE DEPT
The following output results:
Name Null? Type
------------------------------- ------- ----------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Note: DESCRIBE accesses information in the Oracle data dictionary.
You can also use SQL SELECT commands to access this and other information
in the database. See your Oracle8 Server SQL Reference Manual for
details.
Listing PL/SQL Definitions
To see the definition of a function or procedure,
use the SQL*Plus DESCRIBE command.
Example 2-7 Using the DESCRIBE Command
To list the definition of a function called AFUNC, enter
SQL> DESCRIBE afunc
The following output results:
FUNCTION afunc RETURNS NUMBER
Argument Name Type In/Out Default?
--------------- -------- -------- ---------
F1 CHAR IN
F2 NUMBER IN
Controlling the Display
Suppose that you wish to stop and examine the contents of the screen while
displaying a long report or the definition of a
table with many columns. Press [Pause]. (Refer
to Table 2 - 1 to see how [Pause] is labelled
on your keyboard.) The display will pause while you examine it. To continue,
press [Resume].
If you wish, you can use the PAUSE variable
of the SQL*Plus SET command to have SQL*Plus pause
after displaying each screen of a query or report. For more information,
refer to the SET command in Chapter
7.
Interpreting Error Messages
If SQL*Plus detects an error in a command, it will try to help you out
by displaying an error message.
Example 2-8 Interpreting an Error Message
For example, if you misspell the name of a table while entering
a command, an error message will tell you that the table or view does not
exist:
SQL> DESCRIBE DPT
ERROR:
ORA-04043: object DPT does not exist
You will often be able to figure out how to correct the problem from the
message alone. If you need further explanation, take one of the following
steps to determine the cause of the problem and how to correct it:
-
If the error is a numbered error for the SQL*Plus COPY command, look up
the message in Appendix A of this Guide.
-
If the error is a numbered error beginning with the letters "ORA", look
up the message in the Oracle8 Server Messages manual or in the Oracle
installation and user's manual(s) provided for your operating system to
determine the cause of the problem and how to correct it.
-
If the error is unnumbered, look up correct syntax for the command that
generated the error in Chapter 7 of this Guide for
a SQL*Plus command, in the Oracle8 Server SQL Reference Manual for
a SQL command, or in the PL/SQL User's Guide and Reference for a
PL/SQL block. Otherwise, contact your DBA.
