Day 2

Selecting a
Development Tool

by Tom Luers

Oracle's PL/SQL can be used with a variety of development tools including:

• SQL*Plus
• Developer/2000 Forms
• Developer/2000 Reports
• Developer/2000 Graphs
• Enterprise Manager
• Oracle Call Interfaces
• Oracle Precompiler
• Oracle Data Browser

This chapter focuses primarily on the SQL*Plus development tool because you will find that to be the most useful in most cases. Later in the chapter, a few of the other development tools are described briefly. For additional information on Oracle and obtaining its products, you can visit Oracle's Web site at www.oracle.com.

SQL*Plus

In this book, most of the examples and exercises will be executed through SQL*Plus. This chapter covers the most useful SQL*Plus commands that will serve you best while working with PL/SQL.

SQL*Plus is an Oracle product that provides an open window into the Oracle database. It gives developers and end users the ability to interact directly with the database. SQL*Plus has a command-line interpreter so that users can directly submit SQL and SQL*Plus commands and PL/SQL blocks.

SQL commands and PL/SQL blocks are submitted in SQL*Plus to query, manipulate, or delete data in the Oracle databases. In fact, using SQL*Plus is the most common method of interacting with the Oracle database.

There are different groups of SQL*Plus commands that control or manipulate different aspects of your interaction with Oracle. For example, you use environment commands to set the line and page size. Likewise, you can use formatting commands to format the data returned from the database. Additionally, through SQL*Plus you can do all of the following:

• Manage every aspect of the database
• Enter, edit, retrieve, and run SQL statements and PL/SQL blocks
• Create well-polished, formatted reports
• Display column definitions for any table
• Access and copy data between databases
• Send messages to and accept responses from an end user
• Generate SQL and SQL*Plus code dynamically

Environment Commands

The SQL*Plus environment commands define the system parameters within which you work. You use the set and show commands to control these parameters. The set command has an extensive number of options that control the interaction of the user's terminal with other SQL commands. The show command displays the current setting of the various parameters that have been established by the set command.

The Syntax for the set Command
The general syntax of the set command is

set parameter value

where parameter represents the system variable that is to be defined, and value is the state or value that you want the system variable to be set to.

The following sections go into detail on the various specific SQL*Plus parameters.

Session Commands

These parameters establish the general look and feel of your SQL*Plus session. You establish the parameter values with the set command. The following are the options to use with the session commands:

• feedback n—Defines the number of records returned by a query where the query selects at least n records. The default value is 6.
• feedback on/off—Determines whether the feedback display is on or off. The default is on.
• pause msg—Displays a message after scrolling through one screen full of output when running reports. At the time the message displays, the user responds by pressing the Enter key. There is no default value.
• pause on/off—Determines whether the pause feature is on or off. The default value is on.
• pagesize n—Sets the number of lines per page to n. The default value is 14.
• linesize n—Sets the number of characters per line to n. The default value is 80.
• heading on/off—Determines whether column headings are displayed. The default is on.
• space n—Sets the number of spaces between columns. The maximum value is 10. The default value is zero.
• echo on/off—Determines whether each command in a text file is displayed on the screen as it is being executed. The default is off.
• verify on/off—Determines whether each command in a SQL statement is displayed before and after SQL*Plus substitution parameters are replaced by actual values. The default value is on.

Listing 2.1 highlights a few of these parameters.

INPUT/OUTPUT
Listing 2.1. Setting system parameters for SQL*Plus.

set pagesize 6
set pause `Press <return> to continue...'
set pause on
SELECT city_name from city_index
city_name
---------
charlotte
chicago
clarion
davidson
donaldston
dover

Press <return> to continue...

Display Controls for Headers and Footers

The following set of parameters controls the header and footer formats of the output reports:

• ttitle—Defines the header text. By default, this command centers the header. Insert a vertical line in the text to split the header across several lines.
• ttitle off—Turns the header off.
• btitle—Defines the footer text. By default this command centers the footer.
• btitle off—Turns the footer off.

All parameter settings remain in effect until explicitly changed or the SQL*Plus session ends. Therefore, it is always a good idea to clear the header and footer after each report. The SQL*Plus commands in Listing 2.2 will place a header and footer in the output report.

INPUT/OUTPUT
Listing 2.2. Creating a header and footer.

set pagesize 22
set linesize 60
set feedback off
ttitle `Monthly Sales|for June'
btitle `Confidential'

SELECT sales_region, sales_totals from master_sales
WHERE sales_month = `06'

Monthly Sales
For June

SALES_REGION      SALES_TOTALS
------------      ------------

North East        99204
South East        72900
Mid West          921892
Plains            69916
South West        98421
West              1027653
North West        87367

Confidential


ttitle off
btitle off

Display Controls for Columns

The column parameters enable you to change the headings and formats of the report columns and are as follows:

• column column_name—Displays the current setting for the specified column.
• column—Displays the settings for all columns.
• column column_name clear—Clears all settings for the specified column.
• clear columns—Clears the settings for all columns.

All parameter settings remain in effect until explicitly changed or the SQL*Plus session ends. Therefore, it is always a good idea to clear the column parameters after each report. Listing 2.3 is similar to Listing 2.2 except that column headers are specified.

INPUT/OUTPUT
Listing 2.3. Defining column headers.

set pagesize 22
set linesize 60
set feedback off
ttitle `Monthly Sales|for June'
btitle `Confidential'
column sales_region heading `Sales Region' format A15
column sales_totals heading `Total Sales' format $99,999,999

SELECT sales_region, sales_totals from master_sales
WHERE sales_month = `06'

Monthly Sales
For June

                                                                        continues