Introduction
Overview of SQL*Plus
You can use the SQL*Plus program in conjunction
with the SQL database language and its procedural
language extension, PL/SQL. The SQL database language
allows you to store and retrieve data in Oracle. PL/SQL allows you to link
several SQL commands through procedural logic.
SQL*Plus enables you to manipulate SQL commands and PL/SQL blocks,
and to perform many additional tasks as well. Through SQL*Plus, you can
-
enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks
-
format, perform calculations on, store, and print query results in the
form of reports
-
list column definitions for any table
-
access and copy data between SQL databases
-
send messages to and accept responses from an end user
Basic Concepts
The following definitions explain concepts central
to SQL*Plus:
| command |
An instruction you give
SQL*Plus or Oracle. |
| block |
A group of SQL and PL/SQL
commands related to one another through procedural logic. |
| table |
The basic unit of storage
in Oracle. |
| query |
A SQL command (specifically,
a SQL SELECT command) that retrieves information from one or more tables. |
| query results |
The data retrieved by
a query. |
| report |
Query results formatted
by you through SQL*Plus commands. |
Who Can Use SQL*Plus
The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to
serve the needs of users with some database experience, yet straightforward
enough for new users who are just learning to work with Oracle.
The design of the SQL*Plus command language
makes it easy to use. For example, to give a column labelled ENAME in the
database the clearer heading "Employee", you might enter the following
command:
COLUMN ENAME HEADING EMPLOYEE
Similarly, to list the column definitions for a table called EMP, you might
enter this command:
DESCRIBE EMP
Other Ways of Working with Oracle
Oracle serves as the foundation for a complete set of application development,
and office automation tools. These tools support every phase of a system's
development and life cycle, from analysis and design through implementation
and maintenance.
| Designer/2000 |
a set of second generation client/server design
tools |
| Developer/2000 |
a set of second generation client/server development
tools |
| Discoverer/2000 |
a set of end-user query tools |
| Programmer/2000 |
a set of 3GL programming language interfaces |
| Text Server Option |
an option to include full text storage and retrieval
in databases |
| Spatial Data Option |
an option to include multi-dimensional (spatial)
data in databases |
| Mobile Agents |
a tool for applications using mobile and/or detached
clients |
| Oracle Web Application Server |
a tool which enables database access through
Web browsers and the Internet |
| Gateways |
a tool which enables access to data in non-Oracle
databases |
| Media Objects |
a development tool for object-oriented multimedia
applications |
| Oracle InterOffice |
an electronic messaging (Email), calendar, scheduling
and document management system |
Using this Guide
This Guide gives you information on SQL*Plus that applies to all operating
systems. Some aspects of SQL*Plus, however, differ on each operating system.
Such operating system specific details are covered in the Oracle installation
and user's manual(s) provided for your system. Use these operating system
specific manuals in conjunction with the SQL*Plus User's Guide and Reference.
Throughout this Guide, examples showing how to enter commands
use a common command syntax and a common set of sample tables. Both are
described below. You will find the conventions for command syntax particularly
useful when referring to the reference portion of this Guide.
Conventions for Command Syntax
The following two tables describe the notation and conventions
for command syntax used
in this Guide.
| Feature |
Example |
Explanation |
| uppercase |
BTITLE |
Enter text exactly as spelled; it need not be
in uppercase. |
| lowercase italics |
column |
A clause value; substitute an appropriate value. |
| words with specific meanings |
c |
A single character. |
|
char |
A CHAR value--a literal in single quotes--or
an expression with a CHAR value. |
|
d or e |
A date or an expression with a DATE value. |
|
expr |
An unspecified expression. |
|
m or n |
A number or an expression with a NUMBER value. |
|
text |
A CHAR constant with or without single quotes. |
|
variable |
A user variable (unless the text specifies another
variable type). |
| Table 1 - 1. Commands, Terms, and Clauses |
|
|
Other words are explained where used if their meaning is not explained
by context.
| Feature |
Example |
Explanation |
| vertical bar |
| |
Separates alternative syntax elements that may
be optional or mandatory. |
| brackets |
[OFF|ON] |
One or more optional items. If two items appear
separated by |, enter one of the items separated by |. Do not enter the
brackets or |. |
| braces |
{OFF|ON} |
A choice of mandatory items; enter one of the
items separated by |. Do not enter the braces or |. |
| underlining |
{OFF|ON} |
A default value; if you enter nothing, SQL*Plus
assumes the underlined value. |
| ellipsis |
n... |
Preceding item(s) may be repeated any number
of times. |
| Table 1 - 2. Punctuation |
|
|
Enter other punctuation marks (such as parentheses) where shown in the
command syntax.
Sample Tables
Many of the concepts and operations in this Guide are illustrated by a
set of sample tables. These
tables contain personnel records for a fictitious company. As you complete
the exercises in this Guide, imagine that you are the personnel director
for this company.
The exercises make use of the information in two sample tables:
| EMP |
Contains information
about the employees of the sample company. |
| DEPT |
Contains information
about the departments in the company. |
Figure 1 - 1 and Figure
1 - 2 show the information in these tables.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ----- -------- ---- ----------- ------ ------ ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 30
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
DEPTNO DNAME LOC
--------- ------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
What You Need to Run SQL*Plus
To run SQL*Plus, you need hardware, software, operating
system specific information, a username and password, and access to one
or more tables.
Hardware and Software
Oracle and SQL*Plus can run on many different kinds of computers. Your
computer's operating system manages the computer's resources and mediates
between the computer hardware and programs such as SQL*Plus. Different
computers use different operating systems. For information about your computer's
operating system, see the documentation provided with the computer.
Before you can begin using SQL*Plus, both Oracle and SQL*Plus
must be installed on your computer. Note that in order to take full advantage
of the enhancements in SQL*Plus Release 8.0, you must have Oracle8. For
a list of SQL*Plus Release 8.0 enhancements, see Appendix B.
If you have multiple users on your computer, your organization
should have a Database Administrator (called a
DBA) who supervises the use of Oracle.
The DBA is responsible for installing Oracle and SQL*Plus on your
system. If you are acting as DBA, see the instructions for installing Oracle
and SQL*Plus in the Oracle installation and user's manual(s) provided for
your operating system.
Information Specific to Your Operating System
A few aspects of Oracle and SQL*Plus differ from one type of host computer
and operating system to another. These topics are discussed in the Oracle
installation and user's manual(s), published in a separate version for
each host computer and operating system that SQL*Plus supports.
Keep a copy of your Oracle installation and user's manual(s) available
for reference as you work through this Guide. When necessary, this Guide
will refer you to your installation and user's manual(s).
Username and Password
When you start SQL*Plus, you will need a username
that identifies you as an authorized Oracle user and a password
that proves you are the legitimate owner of your username. See the PASSWORD
command in Chapter 7 for details on how to change
your password. The demonstration username, SCOTT, and password, TIGER,
may be set up on your system during the installation procedure. In this
case, you can use the Oracle username SCOTT and password TIGER with the
EMP and DEPT tables (Figure 1 - 1 and Figure
1 - 2).
Multi-User Systems
If several people share your computer's operating system, your DBA can
set up your SQL*Plus username and password. You will also need a system
username and password to gain admittance to the operating system. These
may or may not be the same ones you use with SQL*Plus.
Single-User Systems
If only one person at a time uses your computer, you may be expected to
perform the DBA's functions for yourself. In that case, you can use the
Oracle username SCOTT and password TIGER. If you want to define your own
username and password, see the Oracle8 Server SQL Reference Manual.
Access to Sample Tables
Each table in the database is "owned" by a particular user. You may wish
to have your own copies of the sample tables to
use as you try the examples in this Guide. To get your own copies of the
tables, see your DBA or run the Oracle-supplied
command file named DEMOBLD
(you run this file from your operating system, not from SQL*Plus).
When you have no more use for the sample tables, remove them by
running another Oracle-supplied command file named
DEMODROP. For instructions on how to run DEMOBLD
and DEMODROP, see the Oracle installation and user's manual(s) provided
for your operating system.
