It's important that the data returned from a query be presented in a manner that is easy to interpret. The reporting features of iSQL*Plus, SQL*Plus, and SQL*Plus Worksheet make it easy to give columns more meaningful names, as well as provide report headers and footers so that the contents of the report are clear.
In fact, changing how reports are formatted and displayed is one way that you can customize the iSQL*Plus environment to suit your needs. You can also change how the interface appears and change an account's password.
Along with formatting, another way to improve a report is by using substitution variables, which prompt the user to enter portions of the query at runtime. For example, instead of including a department number in a SELECT statement, a query can ask the user to enter a department number. Finally, after you've come up with a set of commands that you'll want to reuse, you can save them in a file and run them later.
After you've logged in to an Oracle database using the iSQL*Plus login screen, you can make changes to your environment using the Preferences link in the upper-right area of the browser.
From the Preferences screen, you can do one of five things:
Change how the iSQL*Plus environment appears with the Interface Configuration screen (the default Preferences screen)
Change how reports are formatted and displayed using the Script Formatting link
Change how the scripts are executed with the Script Execution link
Change DBA settings for database recovery with the Database Administration link
Change your account's password with the Change Password link.
In this section, we'll review the Interface Configuration, Script Formatting, and Change Password screens.
The Interface Configuration page controls the History Size. The History Size option specifies how many sets of previous commands, called scripts, are saved in an internal buffer for possible reexecution later. A script is a set of one or more SQL or iSQL*Plus commands that is executed as a group. Scripts are saved in the history buffer during an iSQL*Plus session or can be saved to an operating system file to be retrieved later and executed during the same or a new iSQL*Plus or SQL*Plus session. This page also allows you to adjust, in the Input Area Size section, how big a window you need to enter your SQL statements. You can also specify in the Output Location section how the output from the SQL statements will be displayed: below the input area or saved to an operating system file in HTML format. Finally, the Output Page Setup lets you control whether the output from the script appears on a single page or on multiple pages. If the output appears on multiple pages, you can also specify how many lines to display per page.
Script A set of one or more SQL or iSQL*Plus commands that is executed as a group. Scripts may be retrieved from within an iSQL*Plus session or saved to an operating system file and retrieved later in another session.
After you've adjusted the settings as desired, click Apply to save your preferences.
Script Formatting and System Variables
Executing a script and formatting its output are controlled by system variables. A system variable in iSQL*Plus is similar to a variable in any programming language. Like a column in a row of a table, a system variable can hold a string or a number. The string or number in the system variable controls some aspect of how iSQL*Plus will display the results of a query or a DML statement.
Note All of the system variables that can be set in the iSQL*Plus Script Formatting page are also available for customization in the iSQL*Plus, SQL*Plus, and SQL*Plus Work-sheet environments by using the command SET <system_variable> <value>.
System Variable A variable maintained in the iSQL*Plus, SQL*Plus, or SQL*Plus Worksheet environment that holds a status or a setting for a particular feature in that environment. LINESIZE is an example of a system variable in iSQL*Plus.
The iSQL*Plus environment contains more than 40 variables, most of which are accessible on the Script Formatting page. The Script Formatting page contains more readable versions of these variables and makes it easy to change them using the iSQL*Plus graphical environment.
The following sections discuss a few of the key system variables and their corresponding names on the Script Formatting page in iSQL*Plus: LINESIZE, HEADING, HEADSEP, and FEEDBACK.
The LINESIZE system variable (Line Size on the Script Formatting page) specifies how many characters will be displayed on each row of output. Any characters beyond this limit will wrap to the next line.
The value for HEADING (Display Headings on the Script Formatting page) can either be On or Off, and it specifies whether column headings should appear in query output. Using SQL*Plus, the following command turns query headings off:
set heading off
Turning the column headings off may be useful, for example, when sending the output of a SQL query to a file for processing by another program that may not need to have the column headings.
The HEADSEP variable (Headings on Multiple Lines on the Script Formatting page) allows column headings to appear on multiple lines in the output. A single character, which is the vertical bar ( | ) by default, divides the heading onto multiple lines. You can set the HEADSEP variable to either specify the separator character or turn on or off the HEADSEP feature. We'll talk more about HEADSEP later in this chapter, in conjunction with the COLUMN command.
By default, if a query returns six or more rows, iSQL*Plus returns a summary of the number of rows returned from a query, as in this example.
You can set the FEEDBACK variable (Display Record Count on the Script Formatting page) to either change the number of rows that will trigger the row count or turn off this feedback entirely.
The Change Password page allows you to change your Oracle login password. Changing your password on a regular basis reduces the risk of someone obtaining your password and gaining unauthorized access to your account. You must specify your username, old password, and your new password (twice). In SQL*Plus, you can change your password by using the SQL*Plus PASSWORD command or by using the following SQL DCL command:
ALTER USER <username> IDENTIFIED BY >new password>;
The PASSWORD command will prompt you for the old and new passwords. The ALTER USER command does not prompt you for the old password.