SQL*Plus

SQL*Plus can run SQL commands, PL/SQL blocks and is used for managing the database.

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.

 

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.

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

SQL*Plus

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.

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.

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:

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.

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.

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

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.

To correct the 3rd line:

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

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:

  1. Using SQL*Plus’ REMARK command i.e. REMARK comment
  2. With SQL command indicators /* … */ i.e. /* comment */
  3. 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.

The “start file_name” command could be useful if you want to run batch SQL operations.

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.