Appendix E: Accessing RDBMS

Appendix E: Accessing RDBMS

Believe it or 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.

Click To expand 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.

Click To expand
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  load.sql is not in the current directory, so you must specify a fully qualified path to the file. Double quotes are optional and needed only when directory names in your path contain blank spaces. The second example loads the file from the current directory.

Oracle does not make an assumption that the script from a file has to be run immediately; because of that, if your script does not end with a semicolon it would not run until you add it and press Enter.

Tip 

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.

Table E-1: Most Commonly Used 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.

Click To expand
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.