Introduction to SQL*Plus
SQL*Plus can run SQL commands, PL/SQL blocks and is used for managing the database. There are many graphical interfaces that can manage a database, experienced administrators may find it easier to use a command prompt.
SQL*Plus can be used for the following:
- Entering, editing, storing and retrieving SQL commands and PL/SQL blocks.
- Creating group commands for related SQL and PL/SQL blocks using procedural logic.
- Delivering query results as well as presenting them in a report format.
- Column definitions for any table.
- Accessing and copying data from the database.
- Sending messages to and accepting replies from end users.
- Basic concept commands necessary to Oracle operations.
- As a method for storing tables in Oracle.
- To form queries out of select statements to retrieve data from 1 or more tables.
Starting and connecting SQL*Plus
After logging on to the Linux/UNIX system and entering the following commands, you will be prompted for your username and password.
| SQLPLUS username/password@sid | The basic syntax format. |
| CMD > SQLPLUS /NOLOG SQL > conn / as sysdba | DBA users in the oinstall group can login without a password. |
| SQL > connect user/password; | Logging on to the default sid as a normal user. |
| CMD > SQLPLUS user/password | Logging on to the default sid. |
| CMD > SQLPLUS sys/password as sysdba; | Logging on to the default sid as a DBA. |
$ sqlplus
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Dec 31 16:42:18 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
sysenter password : password as sysdba
(The typed password will not be displayed on the screen, not even as a masked password.)
A “Connected to an idle instance” gets displayed, as shown in this screenshot.
The previous line indicates that you have logged on to the system and that SQL*Plus is ready to execute commands.
You can now enter and execute SQL commands and PL/SQL blocks.
To start SQL*Plus more quickly, use the command shown below.
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Dec 31 17:06:00 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
To return to the operation system once you have finished using SQL*Plus, enter the “exit” command.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
Entering and running commands
The cursor that appears after the command prompt, indicates the location where characters are entered. It is here that SQL*Plus instructions are entered as commands.
Generally commands are typed in between other words or have tab characters, the spacing between words are irrelevant to the SQL*Plus interpreter.
There are 3 types of commands that can be entered in the command prompt:
SQL Commands
PL/SQL blocks require a specific type of SQL*Plus command syntax (query result format and other optional parameters).
To view information regarding SQL*Plus, enter the HELP command in the command prompt followed by the command name.
Running SQL commands
Let’s try to type a query to run on a table in our example database: Find employees earning less than $2500.
If you type the first line into the command prompt and press return, the SQL*Plus utility alerts that the command should end with a semicolon ;. A command which is structured properly SQL*Plus will execute the command.
By switching off wrap in SQL*Plus, the lines get truncated.
Displays all of the settings.
You can configure the output according to your preferences.
set line size 70
set pagesize 180
To enter comments along with commands, place them between /* and */ (the same as multi-line comments in the C++ and C# programming languages).
It is possible to structure the SQL*Plus syntax to any number lines of SQL commands. Ideally, to maintain logical integrity, the syntax should be arranged so that related commands are grouped together.
An example of this is the code shown above which has been retyped as:
from emp where sal < 2500;
SQL command statements can be terminated in 3 ways;
• With a semicolon “;“. This will make SQL*Plus ask if you want to run the command.
• With a forward slash character “/“. SQL*Plus asks if you want to run the last entered command.
• With a blank line. Asks if you want to continue the command in the following line.
Running PL/SQL blocks
SQL*Plus interprets PL/SQL procedures, in the blocks, as if they were SQL statements. A full stop character “.” is used to terminate PL/SQL blocks.
This shows a formal PL/SQL block.
2 variable_name variable_type ;
3 begin
4 SQL _commands ;
5 end;
Running Operating System commands
SQL*Plus allows a user to enter operating system commands through its command prompt.
The examples below show 2 different ways to run the pwd (print working directory) command.
/home/oracle
/home/oracle
Listing Table Definitions
To view a table’s property definitions we use the SQL*Plus describe or desc command.
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
Listing a user’s tables and its views
We use an available table called tab.
Command Operations
SQL Buffer
This is the memory area where the last entered SQL command is stored. Until a newer command has been entered this is where commands are stored.
If you aren’t going to run newer commands then it may not be necessary to re-enter the last command.
APPEND command can be configured to append text to commands using shortcuts
CHANGE /old/new C /old/new changes the old for the new
CHANGE /text C /text erases the text from the line
CLEAR BUFFER CL BUFF erases all lines
DEL erases missing lines
INPUT or I (abbreviated), Adds one or more lines
INPUT text or I text adds a line with the text as a parameter
LIST or L (abbreviated), lists all lines in the SQL buffer
LIST n or L n or , list line number n
LIST * or L * lists active line
LIST LAST or L LAST displays the last line
LIST m n, or L m n, lists the range of lines starting at m and ending at n
1 select deptno,ename, sal
2 from emp
3* where deptno = 10
The asterisk or star character * shown on line 3 in the above syntax indicates the currently active line. To view any line enter the line number in the LIST command. This way, we can make any line active.
Let us take a look at how we made the first line active, in this example consider that we have erroneously entered the query.
1 select * from departments
2 where
3 locationn_id=1700;
To correct the 3rd line:
change/locationn_id/location_id
run « this command executes the changes
It is possible to save the expressions in the SQL buffer to a file. Once the Save file_name command is run, the contents of the SQL buffer get saved to a user-defined filename with an .SQL extension.
Create file sqlqueries1.sql.
/home/oracle/sqlqueries1.sql
To clear the contents of the SQL buffer
SQL> clear buffer
buffer cleared
SQL> list
No lines in SQL buffer.
Adding explanatory lines or comments to command files
Comments will help to better understand what the command files were for, at a later point.
There are 3 ways to do this:
- Using SQL*Plus’ REMARK command i.e. REMARK comment
- With SQL command indicators /* … */ i.e. /* comment */
- Using ANSI/ISO characters — i.e. –comment–
Using the commands in the files
The files that have were saved using the saved command, or using any other editor, can be loaded back into the SQL buffer using the SQL get file_name statement.
SQL> get sqlqueries.sql
1 select * from tab;
The “start file_name” command could be useful if you want to run batch SQL operations.
SQL> start sqlqueries.sql
or @file_name
The APPEND command can be used to add a file after one already present in the SQL buffer. To write over it use the REPLACE keyword.
SQL> save sqlqueries append
Appended file to sqlqueries
SQL> host cat sqlqueries.sql
select * from tab;
