SQL*Plus Tips for Oracle Beginners

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:

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.

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:

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.