March 4, 2021
Hot Topics:

SQL*Plus Tips for Oracle Beginners

  • By Michael Klaene
  • Send Email »
  • More Articles »

Many years ago, I attended my first Oracle training class for SQL and PL/SQL programming. In this course, we coded all of our examples using SQL*Plus, Oracle's default tool for accessing the database. I had previously spent a few years doing mainframe development and knew nothing of Oracle. I imagined that Oracle would provide some slick, GUI tool for working with its database and was therefore quite disappointed to find such a simple-looking editor. I asked the instructor why Oracle didn't ship with something "better." He explained that SQL*Plus had been around a long time because many experienced Oracle users prefer to use it, not in spite of its simplicity, but because of it.

I now understand that for many software development tasks, a command-line editor works just fine. Today, when I work with Oracle, I find myself launching SQL*Plus to do my work, despite having more sophisticated, graphical tools available. However, I know not all Oracle users share my fondness for SQL*Plus. I am approached on a regular basis by developers, testers, and managers, mostly new to Oracle, with a myriad of SQL*Plus questions.

If you've not worked with SQL*Plus, it is a simple client application that allows you to manage virtually every facet of the database, from ad-hoc querying to complex administrative tasks. It is not difficult to learn and, chances are, if you are going to spend any amount of time with Oracle, you will use SQL*Plus at some point. This article will provide a number of SQL*Plus tips to increase your productivity. You might think of the following sections as a compilation of the most frequently asked questions I've fielded over the years from SQL*Plus beginners.

If you need basic instructions on how to launch SQL*Plus and connect to a database, I suggest starting with Oracle's SQL*Plus Quick Start.

Pick the Right Environment

When you launch SQL*Plus, there are a number of environment settings to choose from that will affect how the tool behaves. Typing 'SHOW ALL' at the SQL*Plus prompt will list these settings for you. You can toggle any of these settings, on and off, with commands in the form of 'SET {setting} ON/OFF'. There are a great many to choose from but most users will only need a few of them.

If you'll be using SQL*Plus to issue ad hoc database queries, which is probably the most common use of the tool, you'll want to see the results of those queries. You won't be able to unless you issue the command 'SET SERVEROUTPUT ON'.

This directs query results to the terminal. This is an important but oft forgotten setting. I've witnessed many puzzled expressions when a particular user runs a SQL query that returned results yesterday but nothing today. In most cases, the data hasn't changed; they just forgot to reset their environment.

If you expect your query to return a lot of data, save yourself the need to scroll back through the data once it blurs past you.


'SET PAUSE ON' will suspend the display of results at each page, requiring you to hit the enter key to view the next page. The second command will display 'MORE' at the bottom of each page.

Finally, if you would like the query you entered to be displayed back to you, especially helpful when using variables (discussed later), you issue the 'SET VERIFY ON' command. This instructs SQL*Plus to print back the SQL statement as it will actually be parsed by the database.

Easy Editing

A frequent complaint users lodge against SQL*Plus is that it does not let them recover from typing mistakes. Other tools, Quest Software's T.O.A.D. for instance, make it possible to construct queries graphically, no typing required. Nobody likes to type a multi-line query and press Enter, only to realize you've misspelled 'WHERE'.

In SQL*Plus, one way to remedy this situation is to open the SQL buffer in an editor and modify the SQL. The SQL buffer is where Oracle stores your last issued query, whether it was successful or not. When you type 'EDIT' at the SQL*Plus prompt and press Enter, SQL*Plus opens the buffer contents. You can correct the SQL and save it. You can re-execute this SQL statement by typing the '/' character. This character tells SQL*Plus to execute the buffer's contents.

However, there are a few other ways to fix SQL that was incorrectly entered. The first is the change command, c. If you accidentally typed 'SELEET * FROM emp', simply enter this command:


This will replace all instances of the first string with that of the second on your buffered SQL statement.

The second command is the append command, a. As you might have guessed, this allows you to append text to a buffered SQL statement. For example, if you forgot to add a WHERE clause to filter a query's results by emp_id, the command

SQL> a/WHERE emp_id = 1

adds this string to the end of your query.

After using the change or append command, type '/' to re-execute the query from the SQL buffer.

Flexible SQL

To really be productive, you will want to store oft used queries and commands in a file. The command 'SAVE FILE {file name}' will save a buffered SQL statement to a file. A SQL script containing numerous statements can be executed in batch with the 'START {file name}' command, or '@{file name}' as a shortcut. SQL*Plus requires that you separate each SQL statements in a file with a line containing the '/' character.

Saving SQL to scripts is a good example of code reuse. Taking the idea of reusable SQL one step further, consider using substitution variables to make your SQL flexible. This allows you to specify query criteria at execution time. There are two formats for a variable that will be assigned a value at query time: &{variable} and &&{variable}.

As an example, if you routinely look up employee information, save the query to a file (with the 'SAVE {filename}' command).

SELECT * FROM emp WHERE emp_id = &emp_id_to_query;

At execution time, you will be prompted to enter the emp_id of interest. SQL*Plus will use this value in its query. (See the actual query, with its runtime emp_id value, by first executing the 'SET VERIFY ON' command.)

Another way to specify a substitution variable is with a double ampersand (&&). This allows you to reuse a value multiple times. Adding a second ampersand (for example, &&emp_id_to_query) will automatically reuse this value as many times as SQL*Plus encounters it.

Page 1 of 2

This article was originally published on June 17, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date