Starting SQL*PLUS

Sql*plus is a tool that comes along with Oracle. It is used to issue SQL and SQL*PLUS commands. It provides command line interface through which we can enter SQL and SQL*PLUS command.

To start SQL*PLUS, take the steps given below:

1. Select start->programs->Oracle - Oracle8i.
Oracle8i is the name of the instance. It may be different on your system.
2. Then select Application Development -> SQL Plus.
3. When Log On dialog box is displayed, enter username, password and Host string. Use tab key to move from one field to another. For more information about each of these fields, see next section.
4. Click on OK.
5. If the information supplied is valid then you enter into Oracle and SQL*PLUS will display SQL> prompt.


Username, Password and Host String
Oracle is a multi-user database. Whoever is access the database must log on to database. To log on we have to supply username and password. When the given username and password are recognized by Oracle, it will allow us to access data. A user can access only the data that belongs to his/her and not the data of others. However, it is possible for a user to grant privileges to others so that other can access his/her data.

Creation of users and management of overall security is the responsibility of Database Administrator (DBA). DBA is the person who makes sure that database is functioning smoothly. He is responsible for operations such as taking backup of the database, recovering the database in the event of failure, fine tuning database to get best performance. So, if you want to have a new account under your name, please consult administrator of your database.

Username & Password
Every user who wants to access oracle database must have an account in the database. These accounts are created by DBA. Each account is associated with username and password.

Oracle comes with a set of predefined accounts. The following are the usernames and passwords of these accounts.

Username Password
system manager
sys change_on_install
Scott tiger
Demo demo


Note: when you enter into oracle using either system or sys then you become DBA. That means you get special privileges to perform major operations such as creating users etc.


Host String
Host string is a name that is used to access oracle server that is running on a different machine from client. This is required only when you are trying to access oracle server that is not on the current machine. That means, you never need to use host string for Personal Oracle as client and oracle always run on the same machine in Personal Oracle.

Host string is required when you are trying to connect to Oracle Server running on remote machine. Host string is actually called as net service name. Net service name is a name that is stored in TNSNAMES.ORA file on the client to provide the following information.

Host Name of the machine or IP address of the machine on which oracle server is running.
Instance name Name of the Oracle Instance running on the remote machine.
Port Number Port number of the listener, a program that takes requests from clients. Port number is an integer that uniquely identifies the program on the server.


How to enter SQL statements?
SQL*PLUS allow to types of command to entered at the prompt - SQL and SQL*PLUS.

SQL commands include commands of ANSI/ISO SQL and extra commands added to ANSI SQL by oracle.

The following are the rules to be followed while entering SQL commands.

1. An SQL statement may be entered in multiple lines.
2. It is not possible to break a word across lines.
3. SQL statement must be terminated by semicolon (;).

The following is an example of SQL command. What this command does is not important at this moment.

SQL> select ccode,name
2 from courses
3 where fee > 5000;

In the above command, we entered the command in three lines. When you enter semicolon and press enter key then SQL*PLUS will take it as the end of the command. Also note that you have to press enter key at the end of each line.

Note: Both SQL and SQL*PLUS commands are NOT case sensitive.


How to enter SQL*PLUS statements?
SQL*Plus statements are available only in SQL*PLUS. They are not part of standard SQL. SQL*Plus commands are mainly used for two purposes – editing SQL commands and formatting result of query.

The following rules are to be followed while entering these commands.

1. The entire command must be entered on a single line.
2. No need to terminate command with semicolon (;).
3. Commands can be abbreviated. However, the amount of abbreviation is not fixed. Some commands are abbreviated to one letter some are abbreviated to 2 and so on.


The following example show how to use CLEAR SCREEN command of SQL*PLUS.

SQL>clear screen

Or it can be abbreviated to

SQL>cl scr

Common Errors
The following are the common errors that you get while you are trying to log on to Oracle.

Ora-01017: invalid username/password; login denied
The reason for this error is that you have entered a username or password that is not valid. Check whether username you are entering is really existing and password is correctly typed. Sql*plus gives you three chances to type username and password correctly. If you cannot log on successfully in three chances then Sql*plus will exit. However, you can restart Sql*plus again.

ORA-01034: ORACLE not available
The reason for this message is that Oracle Instance is not up and running. You have to first make sure you have started Oracle Instance. Actually there are a few other problems that occurs when Oracle Instance has not started successfully. If this is case in Oracle Server, notify administrator. If this is the case with Personal Oracle, make sure you start database as mentioned in “starting up database” section.