User's Guide and Reference
Part Number A82950-01
This chapter helps you learn the basics of using SQL*Plus, including the following topics:
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.
Several keys on your keyboard have special meaning in SQL*Plus.
2-1, "SQL*Plus Special Keys and their Functions" lists these keys.
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
|SQL*Plus Key Name||Keyboard Key Name||Function|
End of a line of input.
Move cursor left one character to correct an error.
Suspend program operation and display of output.
Resume program operation and output [Pause].
Halt program operation; return to the SQL*Plus command prompt.
Exit SQL*Plus and return to the host operating system.
This example shows you how to start SQL*Plus. Follow the steps shown.
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 current date, and copyright information, and prompts you for your username (the text displayed on your system may differ slightly):
SQL*Plus: Release 220.127.116.11.0 - Production on Thu Aug 3 16:29:01 2000 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter user-name:
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
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
To leave SQL*Plus, enter the EXIT command at the SQL*Plus command prompt:
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 use. 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.
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 three kinds of commands at the command prompt:
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.
To get online help for SQL*Plus commands, type HELP at the command prompt followed by the name of the command. For example:
The SQL command language enables you to manipulate data in
the database. See your Oracle8i SQL Reference
for information on individual SQL commands.
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.
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 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 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.
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.
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:
You can also enter the query on several lines:
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 clearly visible structure helpful, but you
may choose whatever line division makes commands most readable to you.
You can end a SQL command in one of three ways:
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 section
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.
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 section "The SQL Buffer"
below for details).
A blank line in a SQL statement 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
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 section "The SQL Buffer"
below for details). If you subsequently enter another SQL command, SQL*Plus
overwrites the previous command in the buffer.
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. For more information, see the
section "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:
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:
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. For more information about editing or re-running a command
or block stored in the buffer see the section "Running
Command Files" in Chapter 3.
SQL*Plus commands are not stored in the SQL buffer.
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.
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
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
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> /
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.
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
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 Chapter
3, Chapter 4, and Chapter
6. For abbreviations of all SQL*Plus commands, refer to the "Command
Reference" in Chapter 8.
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.
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.
SQL> RUN 1 SELECT EMPNO, ENAME, JOB, SAL 2* FROM EMP WHERE SAL < 2400 EMPNO ENAME JOB SALARY ----- ------------- ------------ ------ 7369 SMITH CLERK $800 7499 ALLEN SALESMAN $1,600 7521 WARD SALESMAN $1,250 7654 MARTIN SALESMAN $1,250 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.
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.
Since SQL*Plus identifies the hyphen as a continuation character, entering a hyphen within a SQL statement will be ignored by SQL*Plus. SQL*Plus will not identify the statement as a SQL statement until after the input processing has joined the lines together and removed the hyphen. For example, entering the following will return an error:
SQL> select 200 - > 100 from dual; select 200 100 from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected
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.
The SQL*Plus command SET controls many variables--called
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
8. 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 7.
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 8 for information on other
items you can list with SHOW.
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.)
Turns autocommit on.
Turns autocommit off (the default).
Commits changes after n SQL DML commands.
Turns autocommit on.
To turn the autocommit feature on, enter
Alternatively, you can enter the following to turn the autocommit feature on:
Until you change the setting of AUTOCOMMIT, SQL*Plus automatically commits changes from each SQL DML command that specifies changes to the database. After each autocommit, SQL*Plus displays the following message:
To commit changes to the database after a number of SQL DML commands, for example, ten, enter
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:
To confirm that AUTOCOMMIT is now set to OFF, enter the following SHOW command:
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 stops the display
and returns to the command prompt.
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 your operating system's installation and user(s) manual.
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.
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:
Operating system commands entered from a SQL*Plus session using the HOST command do not effect the current SQL*Plus session. For example, setting an operating system environment variable does not effect the current SQL*Plus session, it only effects SQL*Plus sessions started subsequently.
You can suppress access to the HOST command. For more information about suppressing the HOST command see Appendix E, "Security".
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.
To list the column definitions of the three columns in the sample table DEPT, enter
The following output results:
Name Null? Type ------------------------------- ------- ------------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
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 Oracle8i SQL Referencefor details.
To list the definition of a function called AFUNC, enter
The following output results:
FUNCTION afunc RETURNS NUMBER Argument Name Type In/Out Default? --------------- -------- -------- --------- F1 CHAR IN F2 NUMBER IN
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 8.
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:
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: