About Oracle SQL*Plus

Oracle SQL*Plus an oracle executable program which is primarily used for running scripts, doing administrative tasks and formatting reports.

It has command line user interface (CUI) and a Windows Graphical User Interface (GUI) . There are several GUI based products like TOAD, Oracle SQL Developer, PL/SQL Developer etc. which provide a nice mouse-based interface to do things.

SQL*Plus provides access to Oracle database and has its own commands. Along-with SQL*Plus commands, we can execute SQL & PL/SQL too in SQl*Plus tool.

Using SQl*Plus, one can:

In this article, I will show how to connect to and use SQL*Plus with some examples. After this you should be able to explore it yourself in more detail.

Connecting to database via SQL*Plus

Connecting to database via SQL*Plus can sometimes be a bit confusing, esp when there are multiple ORACLE_HOMEs on same machine. Just remember that your listener should be aware of the service you need to connect to (check using lsnrctl program's status or services command) and ORACLE_HOME and ORACLE_SID must be set correctly.

Lets learn how to connect..

SQL*Plus executable is found in ORACLE_HOME/bin directory (ORACLE_HOME is the directory where Oracle is installed). Enter id, password and database name (SID) and you will be on SQL prompt. You can enter commands (ending with a semicolon ;) and execute them by pressing ENTER key.

See below screen. There were multiple ORACLE_HOMEs present, but as environment variable ORACLE_HOME was set as "C:\oracle\product\12.1.0\dbhome_1" , sqlplus executatable from that ORALCE_HOME's BIN directory was executed, Release shows the version of sqlplus executable that was invoked. But as variable ORALCE_SID was set to orcl (an Oracle 11g database instance), connection was made to that one, not to an Oracle 12c DB instance, ORACLE_SID determined this.

sqlplus connection screen.jpg

If I want to connect to Oracle 12c DB instance (say orcl12c), I shoudl either set ORACLE_SID=orcl12c OR I can specify the SID while invoking sqlplus.

See below image, I gave SID explicitly here, and as I am connecting as sys, I have specified "as sysdba". Again, the version of sqlplus that is invoked has been determined by env variable ORACLE_HOME (pointing to 12c home here), and since the instance to which I am connecting is 12c DB, so connected accordingly.

sqlplus connection screen.jpg

So finally, we have connected to SQL*Plus, and now we can fire commands at the SQL> prompt.

Working with SQL*Plus and its commands

Remember, we can execute both general SQL and PL/SQL commands in SQL*Plus, plus it has its own set of commands.

I will tell about some of the important one's here.

Firing a simple SELECT statement below gave unformatted and non-user friendly output.

sqlplus demonstration.jpg

Lets apply some SQL*Plus formatting commands, and see the difference.

Setting LINESIZE to 200 will increase the span of output line. But still its not nice.

sqlplus demonstration.jpg

Using COLUMN command for a colum we can change its HEADING, its displayed length (using FORMAT) and actual display format, in case of currencies.

sqlplus demonstration.jpg

Using TRUNCATED, we can truncate the values being displayed if they are getting word-wrapped to next line.

sqlplus demonstration.jpg

Using WORD_WRAPPED, we can nullify the effect of truncate.

sqlplus demonstration.jpg

Using COLUMN ON/OFF command, we can suppress the display formats that we gave for each column, and using same command, we can bring them back in effect.

sqlplus demonstration.jpg

You noticed that column heading is appearing after every few lines. We can adjust this recurrence of headings via SET PAGESIZE command, which sets the number of lines on a page.

sqlplus demonstration.jpg

We can remove all sorts for formatting applied on a column via CLEAR command. CLEAR COLUMNS will remove formatting from all columns.

sqlplus cdemonstration.jpg

When we fire a select statement, usually message like 'n rows selected' is displayed at bottom. Use 'SET FEEDBACK OFF' to supress that message.

TTITLE sets the title for the report. E.g. TTITLE CENTER 'Job Report' SKIP 2; will set title as given and will skip 2 lines.

Below example sets report title, aligns it on left side of report, and shows page number on right side of page (page number is ACTUALLY on right side only.).

sqlplus demonstration.jpg

To set the number of lines between the beginning of each page and the top title, use SET NEWPAGE number_of_lines.

To set the number of lines on a page, use SET PAGESIZE number_of_lines.

To see an example of spooling HTML output to a file, check these 2 links: link1 and link2.

Use COMPUTE to calculates and prints summary lines, using various standard computations, on subsets of selected rows.

BREAK command specifies where and how formatting will change in a report.

Here is an example of BREAK. This example breaks ok DEPARTMENT_ID, and shows department# whenever it changes and changes the page too.

sqlplus demonstration.jpg

For further reading, best place is to check Oracle documentation on SQL*Plus.