9.1. iSQL*Plus Configuration

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.

9.1.1. Interface Configuration

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.

9.1.2. 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.

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.

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 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.

LINESIZE

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.

HEADING

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.

HEADSEP

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.

FEEDBACK

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.

select * from countries;

CO COUNTRY_NAME                       REGION_ID
-- --------------------------------- ----------
AR Argentina                                  2
AU Australia                                  3
BE Belgium 1 BR Brazil                        2
CA Canada                                     2
...
UK United Kingdom                             1
US United States of America                   2
ZM Zambia                                     4

ZW Zimbabwe                                   4

25 rows selected.

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.

9.1.3. Change Password

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.