This Oracle Tutorial is visited quite frequently by visitors looking for answers about Oracle and the Oracle Tools.
So I thought, it might be a good idea, to give you a forum, where you can ask questions and hopefully get answers beyond this tutorial.
The forum just opened, please visit and participate, so we all benefit from your questions and solutions.
I will try to moderate as much as possible until we have found some volunteers, that are interested in taking over this task.
In this section, I 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.
To run the SQL*Plus command line program from Windows 95/98
or Windows NT,
click on the "[Start]"
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.
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.
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.
To exit the SQL*Plus program (in any operating system),
type EXIT and press Enter or carriage return:
SQL> EXIT
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.
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).
