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:
- Generate reports interactively or as a batch process (non-interactively)
- Perform database administration tasks
- Output (Spool) query results to a file (text, csv or html too)
- Check table structures
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 220.127.116.11.0 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.
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.
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.
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.
Using COLUMN command for a colum we can change its HEADING, its displayed length (using FORMAT) and actual display format, in case of currencies.
Using TRUNCATED, we can truncate the values being displayed if they are getting word-wrapped to next line.
Using WORD_WRAPPED, we can nullify the effect of truncate.
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.
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.
We can remove all sorts for formatting applied on a column via CLEAR command. CLEAR COLUMNS will remove formatting from all columns.
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.).
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.
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.
For further reading, best place is to check Oracle documentation on SQL*Plus.