Producing Readable Output with iSQL*Plus

Producing Readable Output

with iSQL*Plus

Objectives

After completing this lesson, you should be able to

do the following:

• Produce queries that require a substitution

variable

• Customize the iSQL*Plus environment

• Produce more readable output

• Create and execute script files

Substitution Variables

Use iSQL*Plus substitution variables to:

• Temporarily store values

– Single ampersand (&)

– Double ampersand (&&)

– DEFINE command

• Pass variable values between SQL statements

• Dynamically alter headers and footers

Substitution Variables

In iSQL*Plus, you can use single ampersand (&) substitution variables to temporarily store values.

You can predefine variables in iSQL*Plus by using the DEFINE command. DEFINE creates and

assigns a value to a variable.

Examples of Restricted Ranges of Data

• Reporting figures only for the current quarter or specified date range

• Reporting on data relevant only to the user requesting the report

• Displaying personnel only within a given department

Other Interactive Effects

Interactive effects are not restricted to direct user interaction with the WHERE clause. The same

principles can be used to achieve other goals. For example:

• Dynamically altering headers and footers

• Obtaining input values from a file rather than from a person

• Passing values from one SQL statement to another

iSQL*Plus does not support validation checks (except for data type) on user input.

Using the & Substitution Variable

Use a variable prefixed with an ampersand (&) to

prompt the user for a value.

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;

Character and Date Values

with Substitution Variables

Use single quotation marks for date and character

values.

SELECT last_name, department_id, salary*12

FROM employees

WHERE job_id = ’&job_title’ ;

Specifying Character and Date Values with Substitution Variables

In a WHERE clause, date and character values must be enclosed within single quotation marks. The

same rule applies to the substitution variables.

Enclose the variable in single quotation marks within the SQL statement itself.

The slide shows a query to retrieve the employee names, department numbers, and annual salaries of

all employees based on the job title value of the iSQL*Plus substitution variable.

Specifying Column Names,

Expressions, and Text

Use substitution variables to supplement the

following:

• WHERE conditions

• ORDER BY clauses

• Column expressions

• Table names

• Entire SELECT statements

Specifying Column Names, Expressions, and Text

Not only can you use the substitution variables in the WHERE clause of a SQL statement, but these

variables can also be used to substitute for column names, expressions, or text.

Example

Display the employee number and any other column and any condition of employees.

SELECT employee_id, &column_name

FROM employees

WHERE &condition;

Specifying Column Names, Expressions, and Text

SELECT employee_id, last_name, job_id,

&column_name

FROM employees

WHERE &condition

ORDER BY &order_column ;

Defining Substitution Variables

• You can predefine variables using the iSQL*Plus

DEFINE command.

DEFINE variable = value creates a user

variable with the CHAR data type.

• If you need to predefine a variable that includes

spaces, you must enclose the value within single

quotation marks when using the DEFINE

command.

• A defined variable is available for the session

DEFINE and UNDEFINE Commands

• A variable remains defined until you either:

– Use the UNDEFINE command to clear it

– Exit iSQL*Plus

• You can verify your changes with the DEFINE

command.

DEFINE job_title = IT_PROG

DEFINE job_title

DEFINE JOB_TITLE = "IT_PROG" (CHAR)

UNDEFINE job_title

DEFINE job_title

SP2-0135: symbol job_title is UNDEFINED

The DEFINE and UNDEFINE Commands

Variables are defined until you either:

• Issue the UNDEFINE command on a variable

• Exit iSQL*Plus

When you undefine variables, you can verify your changes with the DEFINE command. When you

exit iSQL*Plus, variables defined during that session are lost.

Using the DEFINE Command with

& Substitution Variable

• Create the substitution variable using the DEFINE

command.

DEFINE employee_num = 200

• Use a variable prefixed with an ampersand (&) to

substitute the value in the SQL statement.

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num ;

Using the DEFINE Command

The example on the slide creates an iSQL*Plus substitution variable for an employee number by using

the DEFINE command, and at run time displays the employee number, name, salary, and department

number for that employee.

Because the variable is created using the iSQL*Plus DEFINE command, the user is not prompted to

enter a value for the employee number. Instead, the defined variable value is automatically substituted

in the SELECT statement.

The EMPLOYEE_NUM substitution variable is present in the session until the user undefines it or exits

the iSQL*Plus session.

Using the && Substitution Variable

Use the double-ampersand (&&) if you want to reuse

the variable value without prompting the user each

time.

SELECT employee_id, last_name, job_id, &&column_name

FROM employees

ORDER BY &column_name;

Using the VERIFY Command

Use the VERIFY command to toggle the display of the

substitution variable, before and after iSQL*Plus

replaces substitution variables with values.

SET VERIFY ON

SELECT employee_id, last_name, salary, department_id

FROM employees

WHERE employee_id = &employee_num;

The VERIFY Command

To confirm the changes in the SQL statement, use the iSQL*Plus VERIFY command. Setting SET

VERIFY ON forces iSQL*Plus to display the text of a command before and after it replaces

substitution variables with values.

The example on the slide displays the old as well as the new value of the EMPLOYEE_ID column.

The COLUMN Command

Controls display of a column:

• CLE[AR]: Clears any column formats

• HEA[DING] text: Sets the column heading

• FOR[MAT] format: Changes the display of the

column using a format model

• NOPRINT | PRINT

• NULL

COL[UMN] [{column|alias} [option]]

COLUMN Command

Using the COLUMN Command

• Create column headings.

COLUMN last_name HEADING ’Employee|Name’

COLUMN salary JUSTIFY LEFT FORMAT $99,990.00

COLUMN manager FORMAT 999999999 NULL ’No manager’

• Display the current setting for the LAST_NAME

column.

COLUMN last_name

Using the BREAK Command

Use the BREAK command to suppress duplicates.

BREAK ON job_id

The BREAK Command

Use the BREAK command to divide rows into sections and suppress duplicate values. To ensure that

the BREAK command works effectively, use the ORDER BY clause to order the columns that you

are breaking on.

Syntax

BREAK on column[|alias|row]

In the syntax:

column[|alias|row suppresses the display of duplicate values for a given

column

Clear all BREAK settings by using the CLEAR command:

CLEAR BREAK

Using the TTITLE and BTITLE Commands

The TTITLE and BTITLE Commands

Use the TTITLE command to format page headers and the BTITLE command for footers. Footers

appear at the bottom of the page.

The syntax for BTITLE and TTITLE is identical. Only the syntax for TTITLE is shown. You can use

the vertical bar (|) to split the text of the title across several lines.

Syntax

TTI[TLE]|BTI[TLE] [text|OFF|ON]

In the syntax:

text represents the title text (enter single quotes if the text is more than one

word).

OFF|ON toggles the title either off or on. It is not visible when turned off.

The TTITLE example on the slide sets the report header to display Salary centered on one line and

Report centered below it. The BTITLE example sets the report footer to display Confidential. TTITLE

automatically puts the date and a page number on the report.

Creating a Script File

to Run a Report

1. Create and test the SQL SELECT statement.

2. Save the SELECT statement into a script file.

3. Load the script file into an editor.

4. Add formatting commands before the SELECT

statement.

5. Verify that the termination character follows

the SELECT statement.

Creating a Script File to Run a Report

You can either enter each of the iSQL*Plus commands at the SQL prompt or put all the commands,

including the SELECT statement, in a command (or script) file. A typical script consists of at least one

SELECT statement and several iSQL*Plus commands.

How to Create a Script File

1. Create the SQL SELECT statement at the SQL prompt. Ensure that the data required for the

report is accurate before you save the statement to a file and apply formatting commands.

Ensure that the relevant ORDER BY clause is included if you intend to use breaks.

2. Save the SELECT statement to a script file.

3. Edit the script file to enter the iSQL*Plus commands.

4. Add the required formatting commands before the SELECT statement. Be certain not to place

iSQL*Plus commands within the SELECT statement.

5. Verify that the SELECT statement is followed by a run character, either a semicolon (;) or a

slash (/).

Creating a Script File

to Run a Report

6. Clear formatting commands after the SELECT

statement.

7. Save the script file.

8. Load the script file into the iSQL*Plus text

window, and click the Execute button.

Summary

In this lesson, you should have learned how to:

• Use iSQL*Plus substitution variables to store

values temporarily

• Use SET commands to control the current

iSQL*Plus environment

• Use the COLUMN command to control the display of

a column

• Use the BREAK command to suppress duplicates

and divide rows into sections

• Use the TTITLE and BTITLE commands to display

headers and footers