http://www.developer.com/

Back to article

SQL*Plus Tips for Oracle Beginners


June 17, 2004

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.

SQL > SET PAUSE ON
SQL > SET PAUSE "MORE..."

'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:

SQL > c/SELEET/SELECT

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.

Improving Readability

A few formatting tricks can solve a lot of headaches too. SQL*Plus, by default, will allocate enough space for the full length of each database column. In reality, however, a column does not need all that room and this causes lines to wrap unnecessarily.

For example, a last_name column on an emp table might hold up to 100 characters but it is likely that the longest last name will not exceed 30 characters. To prevent SQL*Plus from allocating 100 characters for this field's display, use the following command:

SQL > COLUMN last_name FORMAT A30

This tells SQL*Plus to use only 30 characters to display last name.

Of course, there are several other formatting tricks you could use to beautify results. The command 'COLUMN emp_salary FORMAT $99,999.00' will print emp_salary in currency format. Note that any format settings in SQL*Plus are for display purposes only. No data is actually modified.

To further enhance your SQL formatting, consider using SQL*Plus commands that allow you to format data into reports, complete with titles, headings, footers, and more. A particularly useful command for reporting ordered data is the 'BREAK ON {column name}' command, which allows you to split a report into sections based upon a column's value. Below is a listing of a SQL script called emp.sql. It demonstrates some basic formatting features as well as SQL*Plus's 'SPOOL {file}' command, which sends query output to a file.

SPOOL emp_rpt

TTITLE 'Employees|By Department|Report'
BTITLE ' End of Report '

BREAK ON dept_id

COLUMN emp_id      HEADING 'Employee ID'
COLUMN dept_id     HEADING 'Department ID'
COLUMN emp_name    FORMAT A15  HEADING 'Employee Name'
COLUMN emp_salary  FORMAT $99,999.00 HEADING 'Annual Salary'

--Our report's select statement:
SELECT dept_id, emp_id, emp_fname||' '||emp_lname emp_name, emp_salary
  FROM emp
  ORDER BY dept_id, emp_id
/

SPOOL OFF

This script will spool the query, formatted to specification, to the emp_rpt.lst file:

Mon Apr 19                                    page    1
                       Employees
                     By Department
                        Report

Department ID Employee ID Employee Name   Annual Salary
------------- ----------- --------------- -------------
            1           1 Tom Smith          $35,000.00
                        2 Jane Thomas        $40,000.00
            2           3 Steve Campbell     $50,000.00
                        4 Mike Keller        $25,500.00
            3           5 Elizabeth Brady    $75,000.00

If HTML format is more convenient, use the commands 'SET MARKUP HTML ON SPOOL ON' at the top of your script. When spooling to HTML, you must also be sure to clear this setting with 'SET MARKUP HTML OFF SPOOL OFF' at the end of your script.

Save Your Preferences

An effective way to increase your productivity with SQL*Plus is to save your preferences for future sessions. This is done by modifying the login.sql file in your working SQL*Plus directory. The login.sql file is executed every time you log into the utility so it is an ideal place to perform tasks that you'll know need every session.

At the SQL*Plus prompt, type in 'EDIT login.sql' and this file will open in your editor. As you can see, SQL*Plus configures a default environment for you but you can modify these settings to your liking.

A few simple modifications to this file can make a big difference. If you want to display SQL results page by page, the following commands could be placed at the top of your login.sql file:

SET SERVEROUTPUT ON
SET PAUSE ON
SET PAUSE "MORE..."

If it's your job to monitor employee activity, add a call to the script that generates the emp_rpt.lst file every morning you log in to SQL*Plus:

--my login.sql file
...
--all of my custom settings...
...
--run my daily emp report:
@create_emp_rpt;
...
...

Welcome to iSQL*Plus

Starting with Oracle 9i, Oracle provides a browser-based version of its SQL*Plus tool, appropriately called iSQL*Plus. With iSQL*Plus, you now have the ability to communicate with the database via a browser by entering a URL with your database instance name. There's no need for an SQL client.

Syntactically speaking, not a whole lot has changed so once you know your way around the SQL*Plus syntax, using iSQL*Plus will be simple.

Conclusion

In this article, I've discussed a few techniques that any Oracle SQL*Plus user should be aware of. If you are new to Oracle, do yourself a favor and spend a few minutes to get comfortable with these SQL*Plus commands. Once you are ready to explore the tool in greater detail, read Oracle's guide here for additional features.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.

Sitemap | Contact Us

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