SQL*Plus Basics

Oracle's SQL*Plus is a command line tool that allows a user to type SQL statements to be executed directly against an Oracle database. SQL*Plus has the ability to format database output, save often used commands and can be invoked from other Oracle tools or from the operating system prompt.

In the following sections, the basic functionality of SQL*Plus will be demonstrated along with sample input and output to demonstrate some of the many features of this product.

1 Running SQL*Plus

In this section, we give some general directions on how to get into the SQL*Plus program and connect to an Oracle database. Specific instructions for your installation may vary depending on the version of SQL*Plus being used, whether or not SQL*Net or Net8 is in use, etc.

Before using the SQL*Plus tool or any other development tool or utility, the user must obtain an Oracle account for the DBMS. This account will include a username, a password and, optionally, a host string indicating the database to connect to. This information can typically be obtained from the database administrator.

The following directions apply to two commonly found installations: Windows 95/98 or NT client with an Oracle server, and a UNIX installation.

1.1 Running SQL*Plus under Windows 95/98 and Windows NT

To run the SQL*Plus command line program from Windows 95/98 or Windows NT, click on the button, Programs, Oracle for Windows 95 and then SQL*Plus. The SQL*Plus login screen will appear after roughly 15 seconds.

In the User Name: field, type in your Oracle username.
Press the TAB key to move to the next field.
In the Password: field, type your Oracle password.
Press the TAB key to move to the next field.
In the Host String: field, type in the Service Name of the Oracle host to connect to. If the DBMS is Personal Oracle lite then this string might be ODBC:POLITE. If the DBMS is Personal Oracle8, then the host string might be beq-local. For Client/Server installations with SQL*Net or Net8, this string will be the service name set up by the SQL*Net assistant software.

Finally, click on the OK button to complete the Oracle log in process. SQL*Plus will then establish a session with the Oracle DBMS and the SQL*Plus prompt (SQL> ) will appear. The following figure shows the results of logging into Oracle using SQL*Plus:

There are a number of situations in which an error may occur:

• You might mistype your username, password and/or the Host String
• SQL*Plus and SQL*Net may not be configured properly on your Windows client.
• The network between your Windows client and the Oracle server may have a problem
• The Oracle server may be temporarily shut down or otherwise unavailable

In any of the above cases, an error message will be returned. If the Oracle server is not available or if you supply the wrong username or password, an error will be returned right away. If there is a networking problem, SQL*Plus may take several minutes before returning an error.

Here are some common error messages and some suggestions on how to resolve them:

ERROR: ORA-12154: TNS:could not resolve service name

Either the Host string was mis-typed or SQL*Net is not configured properly. Exit SQL*Plus and try logging in again. If the error still occurs, try another PC.

ERROR: ORA-01017: invalid username/password; logon denied

Either the username or password was typed incorrectly. Exit SQL*Plus and try again.

Unfortunately, most versions of SQL*Plus will not re -display the login screen if your attempt to connect is unsuccessful. You should exit SQL*Plus completely by pulling down the File menu and choosing the Exit menu item. Then run SQL*Plus again from the beginning.

For users of Personal Oracle Lite, there is a default database schema created upon installation of the software. To log into Personal Oracle Lite using SQL*Plus, supply the following values on the SQL*Plus login screen:

In the User Name: field, type in OOT_SCH
In the Password: field, type in OOT_SCH
In the Host String: field, type in ODBC:POLITE.

1.2 Running SQL*Plus under UNIX

To run SQL*Plus under UNIX, log into your UNIX account and at the UNIX command prompt (shown as unix% below), type the sqlplus command followed by a carriage return. When prompted for a username, supply your Oracle username (This may be the same as or different from your UNIX account name). When prompted for a password, supply your Oracle account password (this should not be the same as your UNIX account password). <br>unix% sqlplus<br><br>SQL*Plus: Release 3.3.2.0.0 - Production on Sun Dec 21 13:32:53 1997<br>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.<br><br>Enter user-name: holowczak<br>Enter password: ****************<br><br>Connected to:<br>Oracle7 Server Release 7.3.2.3.0 - Production Release<br>With the distributed, replication, and parallel query options<br>PL/SQL Release 2.3.2.3.0 - Production<br><br>SQL><br>

To exit the SQL*Plus program (in any operating system), type EXIT and press Enter or carriage return:

<br> SQL> EXIT<br>

Once a session has been established using the SQL*Plus tool, any SQL statements or SQL*Plus Commands may be issued. In the following section, the basic SQL*Plus Commands are introduced.

2 SQL*Plus Commands

SQL*Plus commands allow a user to manipulate and submit SQL statements. Specifically, they enable a user to:

• Enter, edit, store, retrieve, and run SQL statements
• List the column definitions for any table
• Format, perform calculations on, store, and print query results in the form of reports
• Access and copy data between SQL databases

The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics:

• / - Execute the current SQL statement in the buffer - same as RUN
• ACCEPT - Accept a value from the user and place it into a variable
• APPEND - Add text to the end of the current line of the SQL statement in the buffer
• AUTOTRACE - Trace the execution plan of the SQL statement and gather statistics
• BREAK - Set the formatting behavior for the output of SQL statements
• BTITLE - Place a title on the bottom of each page in the printout from a SQL statement
• CHANGE - Replace text on the current line of the SQL statement with new text
• CLEAR - Clear the buffer
• COLUMN - Change the appearance of an output column from a query
• COMPUTE - Does calculations on rows returned from a SQL statement
• CONNECT - Connect to another Oracle database or to the same Oracle database under a different user name
• COPY - Copy data from one table to another in the same or different databases
• DEL - Delete the current line in the buffer
• DESCRIBE - List the columns with datatypes of a table
• EDIT - Edit the current SQL statement in the buffer using an external editor such as vi or emacs
• EXIT - Exit the SQL*Plus program
• GET - Load a SQL statement into the buffer but do not execute it
• HELP - Obtain help for a SQL*Plus command (In some installations)
• HOST - Drop to the operating system shell
• INPUT - Add one or more lines to the SQL statement in the buffer
• LIST - List the current SQL statement in the buffer
• QUIT - Exit the SQL*Plus program
• REMARK - Place a comment following the REMARK keyword
• RUN - Execute the current SQL statement in the buffer
• SAVE - Save the current SQL statement to a script file
• SET - Set a variable to a new value
• SHOW - Show the current value of a variable
• SPOOL - Send the output from a SQL statement to a file
• START - Load a SQL statement located in a script file and then run that SQL statement
• TIMING - Used to time the execution of SQL statements for performance analysis
• TTITLE - Place a title on the top of each page in the printout from a SQL statement
• UNDEFINE - Delete a user defined variable

Examples of these SQL*Plus commands are given in the following sections.

Note the distinction made between SQL*Plus Commands and SQL Statements. SQL*Plus commands are proprietary to the Oracle SQL*Plus tool. SQL is a standard language that can be used is just about any Relational Database Management System (RDBMS).

3 SQL*Plus Help Facilities

Some versions of SQL*Plus store the help documentation in the database and make it available via the SQL*Plus command line. Newer installations have changed this and now store the documentation in HTML format which can be read using a World Wide Web Browser such as MS Internet Explorer or Netscape Navigator.

The following two sections describe how to invoke help in SQL*Plus under Windows 95/NT and under UNIX. The method you use to access help may differ according to how your software was installed.

3.1 Getting Help Under Windows 95/98/NT

To get HELP on any of the oracle tools, use the Oracle8 Documentation which is accessible through a web browser. To access the Oracle8 Documentation, click on the Windows 95 button, then Programs, Oracle for Windows 95 and finally Oracle8 Documentation: . This will launch your local Web Browser (Netscape Navigator/Communicator or Microsoft Internet Explorer) and the Welcome to the Oracle8 Documentation Library! screen will be displayed. From here, click on the "TEXT VERSION" link to get to the Oracle Product Documentation Library

.

Once in the Oracle8 Documentation main screen, click on Oracle8 Enterprise Edition and then SQL*Plus Getting Started for Windows NT/95. Other documentation you may find useful are:

Help File/Link	Contents
SQL Reference	Comprehensive syntax for all SQL statements
SQL*Plus Getting Started for Windows NT/95	Specific SQL*Plus commands and options for Windows 95 and NT users.
SQL*Plus Quick Reference	Quick reference guide to SQL*Plus commands.
SQL*Plus User's Guide and Reference	Comprehensive guide to using SQL*Plus.

Each of these can be found on the same Oracle8 Enterprise Edition page.

<br>