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.

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  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.

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.