iSQLPlus Configuration

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 (see the figure at the top of the next page), you can do one of three things:

• Change how the iSQL*Plus environment appears with the Set interface options link.

• Change how reports are formatted and displayed using the Set system variables link.

• Change your account’s password with the Change your password link.

Interface Options

The Interface Options page (see the figure at the bottom of the next page) allows you to adjust how big a window you need to enter your SQL statements. It also allows you to specify how the output from the SQL statements will be displayed: within the same page, on its own page, or saved to an operating system file. The History option sets how many sets of previous commands, called scripts, are saved in an internal buffer for possible re-execution 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.

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 OK to return to the Preferences page.

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.

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. PAGESIZE is an example of a system variable in iSQL*Plus.

Note

All of the system variables that can be set in the iSQL*Plus System Variables page are also available for customization in the iSQL*Plus, SQL*Plus, and SQL*Plus Worksheet environments by using the command SET <system_variable> <value>.

The iSQL*Plus environment contains more than 40 variables. The drop-down list at the top of the System Variables page makes it easy to jump directly to a particular variable without scrolling.

The following sections discuss a few of the key system variables used in iSQL*Plus: PAGESIZE, LINESIZE, HEADING, HEADSEP, and FEEDBACK.

PAGESIZE

The PAGESIZE system variable specifies how many lines are displayed in a query result before the column headings are repeated. Setting the PAGESIZE to zero produces the first set of column headings in iSQL*Plus and no column headings in SQL*Plus.

LINESIZE

The LINESIZE system variable specifies how many characters will be displayed on each row of output. Any characters beyond this limit will wrap to the next line. LINESIZE has no effect in iSQL*Plus.

HEADING

The value for HEADING 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 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 to either change the number of rows that will trigger the row count or turn off this feedback entirely.

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 password. The ALTER USER command does not prompt you for the old password.