December 22, 2014
Hot Topics:

SQL*Plus Tips for Oracle Beginners

  • June 17, 2004
  • By Michael Klaene
  • Send Email »
  • More Articles »

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.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

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

Sitemap | Contact Us

Rocket Fuel