لطفا منتظر باشید ...
Appendix E: Accessing RDBMS
Believe itor not, the graphical user interface (GUI) so prevalent on today's computers
did not always exist; it evolved along with the use of the cathode-ray tube
(CRT) monitors and really took off with the advent of the pointing device,
popularly known as the mouse. Consequently, every database capable of
interactive access has, as a legacy of the non-GUI days, some sort of
command-line interface that allows users to submit a request (usually an SQL
query), and eventually receive some response — be it requested data or an error
message from the RDBMS.
Using ORACLE 9i
Utilities to Access RDBMS
Oracle provides three utilities for
communicating with its RDBMS: SQL*Plus, SQL*Plus Worksheet, and iSQL*Plus. Many
third-party products can work with Oracle 9i Database, the
most popular being TOAD by Quest Software Ltd. (www.quest.com).
SQL*Plus
SQL*Plus is an interactive query tool that
provides a mechanism to submit SQL commands to Oracle RDBMS for execution,
retrieving results, performing database administration, and more. All Oracle
examples in this book are produced using the Oracle SQL*Plus interface, and
this appendix provides the basic knowledge you need to access an Oracle
database and run either interactive commands or script files against it.
SQL*Plus also has the advantage of being platform-independent — exactly the
same commands can be used on Windows, Sun Solaris, Linux, or VAX VMS.
You have two choices starting SQL*Plus
application: from MS-DOS (or Unix, etc.) command prompt or from Windows Start
menu; Oracle 9i also gives you iSQL*Plus — a Web
browser–based interface to SQL*Plus that allows you to query database and
receive results over the Internet. This section explains how to use SQL*Plus on
a local computer, assuming that you followed the Oracle 9i
installation instructions in
Appendix
D. At the command-line prompt, type
sqlplus
/nolog; and
press Enter; the screen should look similar to what you see in
Figure
E-1.
Figure E-1: SQL*Plus command-line
interface started from MS-DOS
Now you need to connect to your database.
The syntax for the
connect command is
connect
<username>/<password>[@<database_sid>].
For example, assuming you already ran scripts from the book's CD-ROM that
create user
ACME, type
connect
acme/acme.
If everything went as it was supposed to, you will get the SQL prompt and can
start typing and executing SQL statements. To actually send an SQL statement to
Oracle you need to add a semicolon at the end of the query and press Enter. If
you press the Enter key without including the semicolon, SQL*Plus will continue
on the new line; each line will be prefixed with a sequence number. See
Figure
E-2.
Figure E-2: Executing SQL commands
from SQL*Plus
| Tip | You can use a slash (/) instead of a semicolon, but this is mainly used in PL/SQL and for batch processing. |
The results will be displayed in the same
window. You can set a number of parameters to customize the look and feel of
the SQL*Plus interface. Please refer to the SQL*Plus manual for more
details.To load a script file use the syntax
@["][<path>]<file_name>["],
for example
SQL>
@"C:\sqlbib\oracle_acme\load.sql"
or
SQL>
@load.sql
The first example assumes the file
To access help from within SQL*Plus, type
help
<topic> (providing Help is
installed).
You can edit your SQL statements using
either SQL*Plus commands or the operating system default editor (usually
Notepad for Windows OS and
vi on Unix). To edit a query from
SQL*Plus, type
edit or
ed at the SQL*Plus prompt. This brings up
the default editor with your last SQL statement. Edit, save, and exit; you
would see your modified statement in the SQL*Plus window. Type semicolon or
slash to execute.
| Note | The modified text will be stored in the buffer file afiedt.buf located in the same directory where you've started your SQL*Plus session. |
There are about 50 SQL*Plus commands you
can use; refer to Oracle documentation should you require more information on
this interface. In
Table
E-1 we've included only the most commonly used commands you'll need in
order to feel comfortable with SQL*Plus.
| Note | The commands in Table E-1 are not to be considered as a part of SQL; Oracle calls them SQL*Plus commands. |
SQL*PLUS COMMAND | DESCRIPTION | EXAMPLE |
|---|---|---|
@ (at sign) | Runs the SQL statements in the specified script. The script can be called from the local file system or from a Web server. | SQL> @load_data_ora.dat |
@@ (double at sign) | Similar to @; often used to call a script from another script(nested scripts). | @@load_data_ora.dat |
/ (slash) | Executes the contents of the SQL buffer. | SQL> /<query results> |
CLEAR SCREEN | Clears your monitor screen. | SQL> clear screen |
CONN[ECT] | Connects a specified Oracle user. | SQL> connect acme/acme |
DEF[INE] | Specifies a user variable. | SQL> define cust_id = 152 |
DESC[RIBE] | Lists the column definition for the specified object (table, view, etc.). | SQL> desc address |
DISC[ONNECT] | Disconnects current user after committing pending changes. | SQL> disc |
ED[IT] | Invokes the default OS editor. | SQL> ed create_acme.sql |
EXIT | Exits SQL*Plus. All changes will be committed. | SQL> exit |
GET | Loads an OS file into the SQL buffer (but does not execute). | SQL> get create_acme.sql |
HELP | Accesses SQL*Plus help. | SQL> help get |
HOST | Executes a host OS command without leaving SQL*Plus. | SQL> host dir |
PASSW[ORD] | Allows to change user's password. | SQL> passw acme |
QUIT | Same as EXIT. | SQL> exit |
R[UN] | Lists and executes the contents of the SQL buffer. | SQL> run create_acme.sql |
SAV[E] | Saves the contents of the SQL buffer in a host OS file. | SQL> save tmp.out |
SPOOL | Stores query result in an OS file. | SQL> spool tmp.out SQL> select * from status; SQL> spool off |
SET | Sets system variables for current session (automatic commit, the line and page size, etc.). | SQL> set autocommit on SQL> set linesize 1000 SQL> set pagesize 300 SQL> set pause on |
START | Same as @. | SQL> start create_acme.sql |
SQL*PLUS COMMAND | DESCRIPTION | EXAMPLE |
UNDEFINE | Deletes a user variable (previously created with the DEFINE command). | SQL> undefine cust_id |
WHENEVER SQLERROR | Stops execution of a script and exits SQL*Plus if an SQL statement returns an error. | SQL> whenever sqlerror continue |
WHENEVER OSERROR | Stops execution of a script and exits SQL*Plus if an operating system error occurs(i.e., connection to the databases lost). | SQL> whenever oserror exit |
| Note | SQL*Plus commands are case insensitive. |
SQL*Plus Worksheet utility
Oracle provides this simple graphical
user interface to execute SQL and SQL*Plus commands, Windows style. To start
SQL*Plus Worksheet type
oemapp
worksheet at your command-line prompt
(OS-independent). The Oracle Enterprise Manager Login screen appears asking for
Username, Password, and, optionally, Service Name. Supply the information and
click OK. The SQL*Plus Worksheet window opens. It gives you a somewhat easier
way to communicate with the RDBMS.
Figure
E-3 shows the SQL*Plus Worksheet window.
Figure E-3: SQL*Plus Worksheet
window
| Tip | You can use all SQL*Plus commands from Table E-1 (and more) when using SQL*Plus Worksheet. |
iSQL*Plus utility
iSQL*Plus is a Web browser–based
interface to Oracle 9i that can be considered a component
of SQL*Plus. It enables you to perform the same tasks as you would through the
command-line version of SQL*Plus, but using a Web browser instead of a command
line.
| Note | You will have to configure Oracle HTTP Server to use iSQL*Plus. The configuration of the server is beyond the scope of this book. |
