Web based School

SQL*Plus

Introduction

History of SQL*Plus
Usage and Limitations
Platforms

SQL*Plus Commands

Those That Initiate the SQL*Plus Environment

Character Mode Environments
Graphical Mode Environments

SQL*Plus Execute Commands
SQL*Plus Editing Commands
SQL*Plus Formatting Commands
Miscellaneous Commands
Access Commands for Various Databases

SQL*Plus Reporting

Reporting Techniques
Advanced Reporting Techniques

SQL*Plus Additional Functionality

SQL Creating SQL
SQL Creating Command Language Scripts
SQL*Plus Creating Database-Driven Command Language Scripts

Summary

6

SQL*Plus (pronounced "sequel plus") is an interactive tool for the Oracle RDBMS environment. SQL*Plus can be used simply to process SQL statements one at a time, process SQL statements interactively with end users, utilize PL/SQL for procedural processing of SQL statements, list and print query results, format query results into reports, describe the contents of a given table, and copy data between databases.

This chapter concentrates on utilizing SQL*Plus to format output into a variety of reports and introduces methods of utilizing SQL*Plus to create dynamic data-driven SQL*Plus programs and operating system specific command language programs.

**History of SQL*Plus**

SQL*Plus originated from the beginning of the Oracle RDBMS days as a product called User Friendly Interface (UFI). Before Version 4 of Oracle RDBMS, UFI was used primarily to administer the Oracle environment. UFI was later renamed to SQL*Plus with the advent of Oracle Version 5. There have been some improvements to SQL*Plus from the UFI days; however, most of the commands and the ease of formatting results are as easy today as they were with the UFI product. There have been additions to several of the command capabilities, additional ways of starting SQL*Plus, and a changed role for SQL*Plus through the major releases of the Oracle RDBMS kernel. For example, before Oracle Version 6, using UFI or SQL*Plus was the only way to administrate the Oracle database. With Oracle Version 6 came a new tool called SQL*DBA that took over many of the database responsibilities such as backup and recovery and startup and shutdown. SQL*Plus also exists in the world of client/server and is available with all the major graphical interfaces. Specifics of these graphical interfaces is beyond the scope of this chapter.

I highly recommend using SQL*DBA to create and maintain the individual Oracle databases. I would restrict SQL*Plus through the use of Oracle's PRODUCT_USER_PROFILE Table or Oracle PROFILES to prohibit end users from performing any administrative task.

Usage and Limitations

SQL*Plus is the main ad hoc, character-mode interface to the Oracle RDBMS. SQL*Plus can easily be used to produce a variety of types of character mode reports. SQL*Plus can also be used to create dynamic SQL*Plus scripts or even dynamic operating-system specific command language programs. SQL*Plus can be used for some Oracle administration functions, and it can be programmed to be interactive during a specific terminal session. SQL*Plus can process ANSI SQL as well as PL/SQL blocks.

SQL*Plus has a variety of limitations; some are operating system-specific. The following list of limits or maximum values is from Oracle SQL*Plus User's Guide and Reference, Appendix C.

*Item*	*Limit*
Filename length	System-dependent
Username length	30 characters
User variable name length	30 characters
User variable value length	240 characters
Number of user variables	1,024
Number of variables in a SQL	50
INSERT command INTO list
Number of variables per SQL command	100
Command line length	500 characters
Length of a LONG value entered through SQL*Plus	250 characters
Maximum output line size	500 characters
Minimum output line size	Five characters
Line size after variable substitution	1,000 characters (internal only)
Number of lines per SQL command	500 (assuming 80 characters per line)
Number of lines per page	50,000
Total row width	60,000 characters for VMS, otherwise 32,767 characters
Number of rows in an array fetch	5,000
Number of nested command files	20 for VMS, CMS, UNIX; otherwise, five
Page number	99,999

Platforms

SQL*Plus is typically available on any computer system that supports the Oracle RDBMS environment. In the client/server environment, SQL*Plus is available on all of the major graphical interfaces, including MS Windows and Motif.

**SQL*Plus Commands**

There are six types of SQL*Plus commands:

Those that initiate the SQL*Plus environment
SQL*Plus Execute commands
SQL*Plus Editing commands
SQL*Plus Formatting commands
Miscellaneous commands
Access commands for various databases

**Those That Initiate the SQL*Plus Environment**

SQL*Plus is an interactive ad hoc environment that can also be pre-programmed with the use of SQL*Plus commands, SQL statements, and/or PL/SQL blocks submitted via a file. Upon successful login to SQL*Plus, the user, regardless of the environment he or she is using, will receive a SQL*Plus prompt, SQL>. You can change this prompt message to any text string by changing the SQL*Plus system variable SQLPROMPT.

You can enhance the basic SQL*Plus environment for each user or group of users by utilizing a file named LOGIN.SQL. This file should be located in the directory or home environment from which SQL*Plus is initiated. Oracle and SQL*Plus run in a variety of computer environments; the method used to create files and the definition of the home environment varies greatly between types of computer operating systems. Typical contents of this file are various SET commands that alter the SQL*Plus default settings for the particular user.

I have set up these LOGIN.SQL files to contain column format commands for each column of the objects to which the particular user or group of users have access. This gives all ad hoc queries a polished appearance without the end user having to input anything but their ad hoc query.

The PRODUCT_USER_PROFILE table, owned by SYSTEM, is one way to provide product level security that enhances the security provided by the SQL GRANT and REVOKE commands. This level of security is used to disable certain SQL and SQL*Plus commands for individual users.

There are various ways to initiate the SQL*Plus environment, depending on the type of computer platform being utilized. To leave the SQL*Plus environment, simply type EXIT at the SQL> prompt and press Return or Enter. To terminate a SQL*Plus command file, make EXIT the last line of the file.

Character Mode Environments

SQL*Plus is a character-based tool that runs in both character mode and graphical environments. How the SQL*Plus environment is initiated varies greatly between the two types of environments. This section discusses the character mode environment initiation syntax and the next section, "Graphical Mode Environments," discusses the syntax required for the Windows 3.1 environment.

SQLPLUS

This syntax initiates SQL*Plus and prompts the user for a valid user name and password. Most UNIX environments implement the SQL*Plus command in lowercase. Enter a valid password and press Enter. SQL*Plus will then prompt for a password. The password does not appear on the screen.

SQL*Plus Version 3.1.0 - Production on Tue Aug 1 14:30:20 1995

  Oracle Corporation 1979, 1991.  .

Enter user-name:

The following syntax would initiate SQL*Plus but not prompt for the user ID or password.

SQLPLUS  userid/password

If either the user ID or password are not valid, SQL*Plus will give an error and then prompt the user for a valid user ID and password.

SQLPLUS -S userid/password

Using the option -S or -SILENT (-S spelled out) will not display the SQL*Plus version and information. This is handy when you are initiating reports written in SQL*Plus from a menu system where the appearance of a seamless application is desired.

SQLPLUS userid/password@database

The preceding syntax will initiate the SQL*Plus environment and connect the user to the remote database identified by the database name. This database name can be a SQL*Net connect string, a SQL*Net alias name, or a SQL*Net Version 2 database instance name.

SQLPLUS userid/password @filename

The preceding syntax will initiate the SQL*Plus environment and execute the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file (SQL*Plus command file). The contents of this file are covered in the section "SQL*Plus Formatting Commands."

Always use operating system-dependent fully qualified filename with this filename.

SQLPLUS @filename

This syntax will initiate the SQL*Plus environment and expect the very first line of the file to contain a valid user ID and password, in this exact format. If the user ID and password are valid, then SQL*Plus will process the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file.

SQLPLUS userid/password @filename param1 param2 ...

This syntax will initiate the SQL*Plus environment and execute the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file. The command-line parameters will be passed to variables inside the SQL*Plus command file and be identified inside this file by &1, &2, and so on. The use of these parameters is covered in the section "Advanced Reporting Techniques."

Graphical Mode Environments

This section discusses the syntax required to initiate the SQL*Plus environment from the Windows 3.1 graphical environment.

Figure 6.1 shows the default Windows 3.1 setup that installs with Personal Oracle7 for Windows. This method prompts the user for a valid user ID, password, and host connection. (See Figure 6.2.) Once again, the password is not visible on the screen, and the host string can be a SQL*Net connect string, a SQL*Net alias name, or a SQL*Net Version 2 database instance name. (See Figure 6.3.)

Figure 6.1. Microsoft Windows SQL*Plus icon setup.

Figure 6.2. Windows SQL*Plus login box.

Figure 6.3. Windows SQL*Plus icon setup to run a specific report or program.

For Personal Oracle7 users, use 2: for the host string to access the local Personal Oracle7 database.

This icon would initiate the Windows SQL*Plus environment and execute the SQL*Plus commands and the SQL (or PL/SQL blocks) contained within the file. The contents of this file are covered in the section "SQL*Plus Formatting Commands."

Always use operating system-dependent fully qualified file name with this filename.

**SQL*Plus Execute Commands**

The execute commands are used to initiate the processing of SQL statements and PL/SQL blocks, measure the processing time of SQL or PL/SQL statements, execute non-Oracle programs, execute SQL*Forms programs, or attain additional help.

/	Execute the SQL statement or PL/SQL block currently in the SQL buffer. (This is probably the most used of the SQL*Plus commands.)
HELP topic	Provides online assistance with SQL, PL/SQL, or SQL*Plus commands.
HOST	Execute non-Oracle commands (operating system-dependent) without leaving SQL*Plus.
RUN	Displays and executes the contents of the SQL buffer.
RUNFORM	Execute a SQLForms program without leaving SQLPlus.
TIMING	Displays the system CPU time with the SQL prompt.

I would not recommend this TIMING feature for tuning purposes; EXPLAIN_PLAN is a more accurate tool for gathering individual SQL statement statistics. EXPLAIN_PLAN is discussed in more detail in Chapter 15, "Tuning and Optimizing."

**SQL*Plus Editing Commands**

The SQL buffer is a work area assigned to the SQL*Plus environment. This buffer contains only SQL or PL/SQL syntax. You can use the following commands to load, save, and manipulate the contents of this buffer:

A new text or APPEND new text	Appends text to the end of the current line of the SQL buffer.
C/target text/new text/ or	Changes the target text
CHANGE/target text/new text/	to the new text on the current line in the SQL buffer.
DEL	Deletes the current line in the SQL buffer.
EDIT filename	Utilizes an operating system-dependent text editor. To edit the SQL buffer with an operating system-dependent text editor, simply leave off the filename.
GET filename	Reads an operating system-dependent file into the SQL buffer.
I text or INPUT text	Adds the text after the current line in the SQL buffer.
L number or LIST number	Displays the contents of the SQL buffer. When the number syntax is used, LIST will display the line number and make that line the current line in the SQL buffer. SAVE filename Saves the contents of the SQL buffer to an operating system-dependent file.

An excellent method to use when you are creating SQL*Plus command files is to utilize these editing features to arrive at the query results desired: SAVE to the operating system and then edit that file with EDIT to add the formatting and other desired features.

START filename param1 param2 ...

START will execute the contents of the SQL*Plus command file named in filename and pass any input parameters to the SQL*Plus command file.

I find it convenient to utilize this START feature when I create various database objects. It enables me to have complete control over the order in which the objects are created. I simply create a SQL*Plus command file named INSTALL.SQL, create each DDL statement in its own SQL*Plus command file, and simply add a START command in this INSTALL.SQL file for each of the DDL SQL*Plus command files.

**SQL*Plus Formatting Commands**

The SQL*Plus formatting commands are used to manipulate the result set from a SQL query.

BREAK ON column_name and options

This command controls the organization of rows returned by the query. BREAK can manipulate the appearance of the output by specifying under what conditions a BREAK should occur and what actions should be taken at the BREAK. The appearance of the output can be controlled by skipping a line or skipping to top of next page and providing totals when used in conjunction with COMPUTE. Any number of lines can be skipped at a BREAK point. BREAK points can be defined at the column level, for multiple columns, on a row, on a page, or on a report. See the COMPUTE command for BREAK examples. Entering BREAK by itself at the SQL prompt will display the current BREAK settings.

BTITLE print_options and/or text or variable options

BTITLE is used to place text at the bottom of each page. There are various print options that position text at various locations. BTITLE will simply center the text if no print options are specified. print options include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. BTITLE spelled out by itself will display the current text setting. Other options that can be specified are ON and OFF. BTITLE is ON by default.

CLEAR and options

CLEAR resets any of the SQL*Plus formatting commands. You can also use it to clear the screen. The options include BREAKS, BUFFER, COLUMNS, COMPUTES, SCREEN, SQL, and TIMING.

COLUMN column_name and options

COLUMN is used to alter the default display attributes for a given column (column_name) of a SQL query. There are a variety of options, but the more common ones are FORMAT, HEADING, JUSTIFY, NEWLINE, NEW_VALUE, and NOPRINT. The FORMAT option is useful in applying editing to numeric fields, date masks to date fields, and specific lengths to variable-length character fields. The HEADING option overrides the SQL*Plus default heading for the particular column. The JUSTIFY option overrides the SQL*Plus column alignment to the heading default. The NEWLINE option will print the column on the beginning of the next line. NEW_VALUE assigns the contents of the column to a SQL*Plus variable (see DEFINE, later in this section). This value can then be used in conjunction with TTITLE or to store intermediate results for master/detail type reports, and is useful to store and pass information between two or more separate SQL statements.

Examples:

COLUMN sal FORMAT $99,999.00 HEADING Salary

COLUMN home_dir NEW_VALUE home_path NOPRINT

The first SQL query would reference the home_dir; all other SQL queries would then reference the home_path for the information returned by the first SQL query.

COMPUTE function OF options ON break options

COMPUTE calculates and prints totals for groupings of rows defined by the BREAK command. A variety of standard functions can be utilized. The most common option is the name of the column in the query on which the total is to be calculated. The break option determines where the totals are to be printed and reset, as defined by the BREAK command.

Example:

BREAK ON sales_rep SKIP 2

BREAK ON REPORT

COMPUTE SUM OF monthly_sales ON sales_rep

COMPUTE SUM OF commissions ON sales_rep

COMPUTE SUM OF monthly_sales ON REPORT

COMPUTE SUM OF commissions ON REPORT

This list will produce a report with totals of monthly_sales and commissions when the sales_rep column value changes. It then skips two lines and produces monthly_sales and commissions totals at the end of the report.

The COMPUTE command resets the accumulator fields back to zero after printing.

TTITLE print_options and/or text or variable options

TTITLE is used to place text at the top of each page. There are various print options that position text at various locations. TTITLE will center the text and add date and page numbers if no print options are specified. print options include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. TTITLE with no options at all will display the current text setting. Other options that can be specified are ON and OFF. TTITLE is ON by default.

Miscellaneous Commands

This section presents a variety of commands that enable you to interact with the user, comment on the code, and enhance coding options.

ACCEPT variable number or char PROMPT text

ACCEPT receives input from the terminal and places the contents in variable. This variable can already have been defined with the DEFINE command. If the PROMPT option is specified, then the text will be displayed after skipping a line. The variable attributes of number or char can be defined at this time. The variable will be a char if not otherwise defined.

DEFINE variable

DEFINE creates a user-defined variable and assigns it to be of char (character) format. This variable can be assigned a default value at this time.

I find these DEFINE statements handy for assigning a variable name to the input parameters coming into the SQL*Plus command file. For example, DEFINE SYSTEM_NAME = &1. This line would create a character variable SYSTEM_NAME and assign it the text associated with the first input parameter. The DEFINE statement makes SQL*Plus command file code easier to follow.

DESC or DESCRIBE database object

DESCRIBE displays the columns associated with a table, view, or synonym.

PAUSE text

PAUSE prints the contents of text after skipping a line, and then waits for the Return or Enter key to be pressed.

PROMPT text

PROMPT simply skips a line and prints the contents of text.

REM or REMARK

SQL*Plus will ignore the contents of this line when it is used in SQL*Plus command files. REMARK enables documentation or other comments to be contained in these SQL*Plus command files.

SET SQL*Plus System Variable

The SET command controls the default settings for the SQL*Plus environment. You can automatically alter these settings for each SQL*Plus session by including them in the LOGIN.SQL file, discussed earlier in this chapter. See Chapter 6 of Oracle's SQL*Plus User's Guide and Reference for a complete listing of the SET options.

The following are some common SET options utilized for reporting:

SET LINESIZE 80	Controls the width of the output report line
SET PAGESIZE 55	Controls the number of lines per page

The following are some common SET options that suppress various SQL*Plus output:

SET FEEDBACK OFF	Suppresses the number of query rows returned
SET VERIFY OFF	Suppresses the substitution text when using &variables, including command line variables
SET TERMOUT OFF	Suppresses all terminal output; this is particularly useful in conjunction with the SPOOL command
SET ECHO OFF	Suppresses the display of SQL*Plus commands

SPOOL filename or options

The SPOOL command is used to open, close, or print an operating system-dependent file. Specifying SPOOL filename will create an operating system-dependent file; filename can contain the full pathname of the file and extension. If no file extension is given, the file suffix, LST, will be appended (filename.LST). Options include OFF or OUT. If OFF is specified, then the operating system-dependent file is simply closed. If OUT is specified, then the operating system-dependent file is closed and sent to the operating system-dependent printer assigned as the default printer to the user's operating system environment.

If you issue SPOOL filename without issuing a SPOOL OFF or SPOOL OUT, then the current operating system-dependent file is closed and the new one as specified by the SPOOL command is opened.

I prefer to write SQL*Plus-based reports utilizing the SET variables mentioned previously in conjunction with the SPOOL command. I create the output report in the file specified by the SPOOL command and then control whether it is visually displayed to the terminal, optionally printed, or both by using operating system-dependent command language.

I always use a file suffix when specifying SPOOL filename. It enables me to control exactly what the entire filename is and not depend on Oracle default options (the LST suffix feature), which are subject to change without notice with newer releases of the SQL*Plus product.

UNDEFINE variable

UNDEFINE removes the previously DEFINEd variable from the SQL*Plus environment.

Access Commands for Various Databases

The database access commands CONNECT, DISCONNECT, and COPY are used to connect to and share data with other Oracle databases. Discussing these commands is beyond the scope of this chapter.

**SQL*Plus Reporting**

You can use SQL*Plus formatting commands in a variety of combinations to create reports. This section covers reporting techniques that use control breaks, different ways to format headings, input parameters, SQL*Plus environment controls, and the SQL Union operator.

Reporting Techniques

This section covers some common SQL*Plus report formatting features. It also covers techniques for controlling the resulting output. I discuss and provide examples of simple reporting techniques and advanced reporting techniques.

The following example formats the results of a SQL query. It defines a report title and formats, assigns column headings, and applies some control breaks for intermediate and report totaling.

Listing 6.1. Simple SQL*Plus report code.

     1:    define ASSIGNED_ANALYST = &1

     2:    set FEEDBACK OFF

     3:    set VERIFY OFF

     4:    set TERMOUT OFF

     5:    set ECHO OFF

     6:    column APPLICATION_NAME    format a12    heading 'Application'

     7:    column PROGRAM_NAME           format a12        heading 'Program'

     8:    column PROGRAM_SIZE         format 999999     heading 'Program|Size'

     9:    break on APPLICATION_NAME skip 2

    10:    break on report skip 2

    11:    compute sum of PROGRAM_SIZE on APPLICATION_NAME

    12:    compute sum of PROGRAM_SIZE on report

    13:    ttitle 'Programs by Application | Assigned to: &&ASSIGNED_ANALYST'

    14:    spool ANALYST.OUT

    15:    select APPLICATION_NAME,PROGRAM_NAME,nvl(PROGRAM_SIZE,0)

    16:      from APPLICATION_PROGRAMS

    17:     where ASSIGNED_NAME = '&&ASSIGNED_ANALYST'

    18:     order by APPLICATION_NAME,PROGRAM_NAME

    19:    /

    20:    spool off

    21:    exit

The following is the output report from the code in Listing 6.1.

Tue Jul 13                                                            page    1

                               Programs by Application

                                        Assigned to: HOTKA

                Program

    Application     Program             Size

    ------------    ------------    ---------

    COBOL           CLAIMS           10156

                    HOMEOWN          22124

                    PREMIUMS         10345

                                    ---------

    sum                              42625

    FORTRAN         ALGEBRA           6892

                    MATH1             7210

                    SCIENCE1         10240

                                     ---------

    sum                              24342

    sum                              66967

Listing 6.1 is a simple but common form of SQL*Plus formatting. This report passes a command-line parameter (&1 on line 1) and assigns it to the variable name ASSIGNED_ANALYST. The ASSIGNED_ANALYST variable is then used in the headings (see line 13) and again as part of the SQL query (see line 17). Lines 2, 3, 4, and 5 suspend all terminal output from the SQL*Plus environment. The && is utilized to denote substitution of an already defined variable. This report contains two breaks, one when the column APPLICATION_NAME changes (see line 9) and one at the end of the report (see line 10). Totals are also calculated for each of these breaks (see lines 11 and 12). The pipe character (|) in the TTITLE command (see line 13) moves the following text onto its own line. Line 14 will open an operating system-dependent file named ANALYST.OUT in the current operating system-dependent directory. The order by clause of the query on line 18 ensures that the breaks occur in an orderly manner.

Always order the query output by the breaks expected by the program. The only way to guarantee the order of the rows is to use an order by clause on the query.

Advanced Reporting Techniques

The following example creates a cross-tabular report with a spreadsheet appearance.

Listing 6.2. Cross-tabular SQL*Plus report code.

1:    define RPT_DATE = &1

    2:    set FEEDBACK OFF

    3:    set VERIFY OFF

    4:    set TERMOUT OFF

    5:    set ECHO OFF

    6:    column SALES_REP         format a12    heading 'Sales|Person'

    7:    column NISSAN            format 999999 heading 'Nissan'

    8:    column TOYOTA            format 999999 heading 'Toyota'

    9:    column GM                format 999999 heading 'GM'

    10:    column FORD             format 999999 heading 'Ford'

    11:    column CRYSLER          format 999999 heading 'Crysler'

    12:    column TOTALS           format 999999 heading 'Totals'

    13:    break on report skip 2

    14:    compute sum of NISSAN on report

    15:    compute sum of TOYOTA on report

    16:    compute sum of GM on report

    17:    compute sum of FORD on report

    18:    compute sum of CRYSLER on report

    19:    compute sum of TOTALS on report

    20: ttitle left '&&IN_DATE' center 'Auto Sales' RIGHT 'Page: ' format 999 -

    21:           SQL.PNO skip CENTER ' by Sales Person '

    22:    spool SALES.OUT

    23:    select SALES_REP,

    24:        sum(decode(CAR_TYPE,'N',TOTAL_SALES,0)) NISSAN,

    25:        sum(decode(CAR_TYPE,'T',TOTAL_SALES,0)) TOYOTA,

    26:        sum(decode(CAR_TYPE,'G',TOTAL_SALES,0)) GM,

    27:        sum(decode(CAR_TYPE,'F',TOTAL_SALES,0)) FORD,

    28:        sum(decode(CAR_TYPE,'C',TOTAL_SALES,0)) CRYSLER ,

    29:        sum(TOTAL_SALES) TOTALS

    30:    from CAR_SALES

    31:    where SALES_DATE <= to_date('&&RPT_DATE')

    32:    group by SALES_REP

    33:    /

    34:    spool off

    35:    exit

The following code shows the output report from Listing 6.2.

    31-AUG-95            Auto Sales                                     Page: 1

                by Sales Person

    Sales

    Person        Nissan   Toyota       GM       Ford     Crysler     Totals

    --------      -------- ---------    -------- -------- --------    ------

    Elizabeth     5500     2500         0        0        4500        12500

    Emily         4000     6000         4400     2000     0           16400

    Thomas        2000     1000         6000     4000     1500        14500

                  -------- ---------    -------- -------- --------    ------

                  11500    9500         10400    6000     6000        43400

Listing 6.2 is a cross-tabular SQL*Plus command file. This report passes a command-line parameter (&1 on line 1) and assigns it to the variable name RPT_DATE. The RPT_DATE variable is then used in the headings (see line 20) and again as part of the SQL query (see line 31). Lines 2, 3, 4, and 5 suspend all terminal output from the SQL*Plus environment. The report will be created in the operating system-dependent file SALES.OUT. Column formatting commands control the appearance of the columns (lines 6 through 12). The combination of compute commands (lines 14 through 19), the sum statements in the query (lines 24 through 29), and the group by clause in the query (line 32) give the report output the appearance of a cross-tabular report.

I utilized a different TTITLE technique in Listing 6.2 (lines 20 and 21) from that of Listing 6.1 (line 13).

The following example displays a major break field with the supporting data immediately following.

Listing 6.3. Master/detail SQL*Plus report code.

    1:    ttitle 'Sales Detail | by Sales Rep'

    2:    set HEADINGS OFF

    3:    column DUMMY NOPRINT

    4:    select 1 DUMMY, SALES_REP_NO,'Sales Person: ' || SALES_REP

    5:    from sales

    6:    UNION

    7:    select 2 DUMMY,SALES_REP_NO,'--------------------'

    8:    from sales

    9:    UNION

    10:    select 3 DUMMY,SALES_REP_NO, rpad(CAR_MAKE,4) || '    ' ||

    11:        to_char(SALE_AMT,'$999,999.99')

    12:    from sales_detail

    13:    UNION

    14:    select 4 DUMMY,SALES_REP_NO,'         ----------'

    15:    from sales

    16:    UNION

    17:    select 5 DUMMY,SALES_REP_NO,'Total:  ' ||

    18:        to_char(sum(TOTAL_SALES),'$999,999.99'))

    19:    from sales

    20:    UNION

    21:    select 6 DUMMY,SALES_REP_NO,'          '

    22:    from sales

    23:    order by 2,1,3

    24:    /

I will now only include the specific SQL*Plus commands necessary to produce the desired output in the remaining examples.

The following code shows the output report from Listing 6.3.

    Thur Aug 31                                                    page    1

                                  Sales Detail

                     by Sales Rep

    Sales Person:  Elizabeth

    -----------------------------

    Chrysler    $3,000

    Chrysler    $1,500

    Nissan      $2,000

    Nissan      $2,000

    Nissan      $1,500

    Toyota      $2,500

                   ----------

    Total:      $12,500

    Sales Person:  Emily

    -----------------------------

    Ford        $1,000

    Ford        $1,000

    GM          $2,000

    GM          $2,400

    Nissan      $2,000

    Nissan      $2,000

    Toyota      $1,000

    Toyota      $2,500

    Toyota      $2,500

                ----------

    Total:      $16,400

    Sales Person:  Thomas

    -----------------------------

    Chrysler    $1,500

    Ford        $1,000

    Ford        $3,000

    GM          $1,400

    GM          $1,600

    GM          $3,000

    Nissan      $2,000

    Toyota      $1,000

                ----------

    Total:      $16,400

Listing 6.3 creates a master/detail SQL*Plus report by utilizing the SQL UNION command. In this example, there are six distinct separate types of lines to be printed: the sales person (line 4), a line of dashes under the sales person (line 7), the detail line (line 10), a line of dashes under the detail total (line 14), a total line (line 17), and a blank line (line 21). There are six separate queries that have their output merged and sorted together by the SQL JOIN statement (see lines 6, 9, 13, 16, 19, and 23). When you use JOIN to merge the output of two or more queries, the output result set must have the same number of columns. The headings are turned off (line 2) because regular SQL*Plus column headings are not desired for this type of report. The first column of each query has an alias column name of DUMMY. This DUMMY column is used to sort the order of the six types of lines (denoted by each of the six queries). The DUMMY column's only role is to maintain the order of the lines within the major sort field (SALES_REP_NO in this example); therefor, the NOPRINT option is specified in line 3.

Listing 6.4 uses the JOIN feature to display output from two or more tables within the same report.

Listing 6.4. Multitable SQL*Plus report code.

    1:    column OBJECT_TYPE      format a20     heading 'Object'

    2:    column OBJECT_NAME      format a8     heading 'Name'

    3:    column COMMENT     format a8     heading 'Comments'

    4:    break on OBJECT_TYPE skip 1

    5:    ttitle 'System Summary Report

    6:    select 'Program' OBJECT_TYPE, program_name OBJECT_NAME,

    7:        program_comments  COMMENTS

    8:        from program_table

    9:    UNION

    10:   select 'Command Language',cl_name, assoc_system

    11:       from cl_table

    12:   UNION

    13:    select 'Files',file_name, 'File Size = ' || file_size ||  'Bytes'

    14:       from file_table

    15:    /

The following code shows the output report from Listing 6.4.

    Thr Aug 31                            page    1

                                   System Summary Report

    Object                       Name              Comments

    -------------------------    ----------        ------------------------

    Programs                     AM1                Algebra Test 1

                                 AM2                Algebra Test 2

                                 AM3                Algebra Test 3

    Command Language             CL1                AM1

                                 CL2                AM2

                                 CL3                AM3

    Files                        AM1.TST            File Size = 1200 Bytes

                                 AM2.TST            File Size = 3000 Bytes

                                 AM3.TST            File Size = 2200 Bytes

Listing 6.4 creates a SQL*Plus report utilizing different columns from different tables using the SQL UNION command. In this example, there are three different tables (see lines 8, 11, and 14), but there are only three columns of output. The first query contains the column names (see lines 6 and 7). This is because of the way the UNION operator works. The queries after the first query must follow the number of columns and the type of column (text or numeric) based on the column definitions of the first query. The BREAK command (line 4) causes the OBJECT_NAME to print once and creates the blank line between the groupings of records.

I will demonstrate two methods of creating reports that print with specific text in specific positions. Method 1 in Listing 6.5 utilizes the RPAD SQL function whereas Method 2 in Listing 6.6 utilizes the COLUMN formatting command. Both examples will create the same output report.

Listing 6.5. Method 1 fixed position formatting SQL*Plus report code.

    1:    define  TICKET_ROWID = &1

    2:    set LINESIZE 80

    3:    set  HEADING OFF

    4:    set FEEDBACK OFF

    5:    spool TICKET.OUT

    6:    select RPAD('----------------------------------------------------' ||

    7:        null,80),

    8:    RPAD('                       Customer Contact Survey' || null,80),

    9:    RPAD('-------------------------------------------------' || null,80),

    10:   RPAD(' Customer Name: ' || CUSTOMER_NAME || ' PHONE#: ' ||

 PHONE || null,80),

    11:    RPAD(' Customer Address:  ' || CUSTOMER_ADDRESS  || null,80),

    12:    RPAD('                       ' || CUSTOMER_CITY || CUSTOMER_STATE ||

    13:        CUSTOMER_ZIP  || null,80),

    14:    RPAD('------------------------------------------------' || null,80),

    15:    RPAD(' ' || TO_CHAR(CONTACT_DATE,'mm/dd/yy HH:MI') ||

 '  Caller: ' || CALLER ||

    16:        null,80),

    17:    RPAD('------------------------------------------------' || null,80),

    18:    RPAD('  Home Phone? ' ||  HPHONE_YN  || 'Best Time to call:  ' ||

 CALL_TIME ||

    19:        null,80),

    20:    RPAD('    Has Catalog? ' || CATALOG_YN || 'Desire Future Calls? ' ||

 FUTURE_YN ||

    21:        null,80),

    22:    RPAD('------------------------------------------------' || null,80),

    23:    RPAD('PRINTED:  ' || TO_CHAR(SYSDATE,'mm/dd/yy HH:MI || 'BY:  ' ||

    24:        OPERATOR || null,80)

    25:    from CUSTOMER_TABLE

    26:    where ROWID = '&&TICKET_ROWID'

    27:    /

    28:    set PAGESIZE 1

    29:    set  NEWPAGE 0

    30:    select  null from dual;

    31:    set PAGESIZE 0

    32:    spool OUT

    33:    exit

Listing 6.6. Method 2 fixed position formatting SQL*Plus report code.

    1:    define TICKET_ROWID = &1

    2:    set PAGESIZE 55

    3:    set LINESIZE 80

    4:    set HEADING OFF

    5:    set FEEDBACK OFF

    6:    column LINE1 JUSTIFY LEFT NEWLINE

    7:    column LINE2 JUSTIFY LEFT NEWLINE

    8:    column LINE3 JUSTIFY LEFT NEWLINE

    9:    column LINE4 JUSTIFY LEFT NEWLINE

    10:    column LINE5 JUSTIFY LEFT NEWLINE

    11:    column LINE6 JUSTIFY LEFT NEWLINE

    12:    column LINE7 JUSTIFY LEFT NEWLINE

    13:    column LINE8 JUSTIFY LEFT NEWLINE

    14:    column LINE9 JUSTIFY LEFT NEWLINE

    15:    column LINE10 JUSTIFY LEFT NEWLINE

    16:    column LINE11 JUSTIFY LEFT NEWLINE

    17:    column LINE12 JUSTIFY LEFT NEWLINE

    18:    column LINE13 JUSTIFY LEFT NEWLINE

    19:    column LINE14 JUSTIFY LEFT NEWLINE

    20:    break ON ROW SKIP PAGE

    21:    SPOOL TICKET

    22:    select '--------------------------------------------' || null LINE1,

    23:    '                       Customer Contact Survey' || null LINE2,

    24:    '---------------------------------------------------' || null LINE3,

    25:    ' Customer Name:  ' || CUSTOMER_NAME || ' PHONE#: ' ||

 PHONE || null LINE4,

    26:    ' Customer Address:  ' || CUSTOMER_ADDRESS  || null LINE5,

    27:    '                            ' || CUSTOMER_CITY || CUSTOMER_STATE ||

    28:            CUSTOMER_ZIP  || null LINE6,

    29:    '---------------------------------------------------' || null LINE7,

    30:    ' ' || TO_CHAR(CONTACT_DATE,'mm/dd/yy HH:MI || '  Caller: ' ||

 CALLER || null

    31:        LINE8,

    32:    '---------------------------------------------------' || null LINE9,

    33:    '  Home Phone? ' ||  HPHONE_YN  || 'Best Time to call:  ' ||

 CALL_TIME || null

    34:        LINE10,

    35:    '   'Has Catalog? ' || CATALOG_YN || 'Desire Future Calls? ' ||

 FUTURE_YN || null

    36:        LINE11,

    37:    '--------------------------------------------------' || null LINE12,

    38:    'PRINTED:  ' || TO_CHAR(SYSDATE,'mm/dd/yy HH:MI || 'BY:  ' ||

 OPERATOR || null

    39:        LINE13,

    40:    '---------------------------------------------------' || null LINE14

    41:    from CUSTOMER_TABLE

    42:    where ROWID = '&&TICKET_ROWID'

    43:    /

    44:    spool OUT

    45:    exit

Listings 6.5 and 6.6 both produce the same output report, as follows in Listing 6.7.

Listing 6.7. Output of Listing 6.5 and 6.6, fixed position formatting SQL*Plus report.

    ---------------------------------------------------------------------------

        Customer Contact Survey

    ---------------------------------------------------------------------------

    Customer Name:  John Smith   PHONE#: 515 123-4567

    Customer Address:  123 Oak Street

                       Anytown  VA 12345

    ---------------------------------------------------------------------------

      31-Aug-95 10:05  Caller:   DHotka

    ---------------------------------------------------------------------------

       Home Phone?    Y       Best Time to call:  8pm

       Has Catalog?   Y       Desire Future Calls?   N

    ---------------------------------------------------------------------------

    PRINTED: 31-Aug-95 12:45   BY:  DHotka

    ---------------------------------------------------------------------------

Listings 6.5 (method 1) and 6.6 (method 2) produce the exact same output, as seen in Listing 6.7. Both of these methods will produce reports with information in fixed or predefined positions. Both of these methods could be utilized to print information on a preprinted form. These particular examples were designed to be started from inside another process, such as SQL*Forms, because the only input parameter is an Oracle ROWID used to read and process a single row from the database (see lines 1 and 26 in Listing 6.5 and lines 1 and 42 in Listing 6.6).

These examples utilize the concatenation feature of SQL (||) to blend text between database fields. Each column in the SQL statement represents an individual line in the report. Both examples have the standard column headings feature turned off (line 3 of Listing 6.5, line 4 of Listing 6.6). Both examples have a one-to-one relationship between a SQL column and a line of output. The methods differ in how the columns are formatted to create the individual lines.

The main difference in these two methods is the approach used in the individual line setup. Method 1 (Listing 6.5) uses the SQL command RPAD (see line 6) in combination with LINESIZE (line 2) to create an output line. The RPAD is used to fill the line with blanks to position 80, and with LINESIZE set at 80 will cause the formatted line to appear on a line by itself. Method 2 (Listing 6.6) uses the column command with the option NEWLINE specified in conjunction with a field alias name (see lines 6 and 22). The column command with the NEWLINE option will make the formatted line appear on a line by itself.

Listing 6.5 uses lines 28 through 31 to skip to the top of a new page. Listing 6.6 uses a break command to skip to a new page after each row of data from the SQL query. The entire SELECT command of each example formats one row of information from the database.

**SQL*Plus Additional Functionality**

The remainder of this chapter discusses a variety of ways to format SQL*Plus output to create database-driven types of output (that is, SQL code, operating system-dependent command language, and script files for other Oracle products).

SQL Creating SQL

The classic example of using SQL*Plus formatting to create other SQL statements (hence the term "SQL creating SQL") is cleaning up a table after an employee leaves a company. The Oracle data dictionary view TAB is used in this example. You can easily enter at the SQL*Plus prompt (shown here as SQL>) the steps in Listing 6.8 or adapt them to a SQL*Plus command file using features you already learned.

Listing 6.8 is an example of SQL creating SQL.

Listing 6.8. Dropping all tables owned by a particular user.

    SQL>set headings off

    SQL>set pagesize 0

    SQL>set termout off

    SQL>spool drop_tbl.sql

    SQL>select 'DROP TABLE ' || tname || ';' from tab;

    SQL>spool off

    SQL>set termout on

    SQL>start drop_tbl

This scenario assumes that the login ID and the owner of the table objects to be dropped are both the same. The first three commands are used to set up the SQL*Plus environment. The spool file drop_tbl.sql will capture the concatenated text and table names (tname) from the SQL query. The spool off command closes the file and the start command executes the drop table commands now inside the drop_tbl.sql file.

It is common practice to use this SQL-creating-SQL example to perform a variety of clean-up and monitoring tasks.

Listing 6.9 is an extension of Listing 6.8 as another example of creating useful database-driven programs. This example will add four auditing fields to the end of each table owned by the user ID that runs this particular SQL*Plus command file. This script will also create a database trigger that will automatically maintain these four added fields. I utilized the fixed position formatting discussed in Listing 6.5.

Listing 6.9. SQL creating database triggers.

    1:    set ECHO OFF

    2:    set TERMOUT OFF

    3:    set FEEDBACK OFF

    4:    set VERIFY OFF

    5:    set PAGESIZE 0

    6:    set LINESIZE 80

    7:    set HEADING OFF

    8:    spool cre_dbtrg.sql

    9:    select     RPAD('select '  alter table ' || TNAME || null,80),

    10:      RPAD( '         add (inserted_by      varchar2(10), ' || null,80),

    11:      RPAD( '              inserted_date        date    , ' || null,80),

    12:      RPAD( '              updated_by       varchar2(10), ' || null,80),

    13:      RPAD( '              updated_date         date    ); ' || null,80)

    14:   from TAB;

    15:   select     RPAD(' create trigger trg_' || TNAME || null,80),

    16:        RPAD(' before insert or update ' || null,80),

    17:        RPAD('     on ' || TNAME || null,80),

    18:        RPAD('    for each row ' || null,80),

    19:        RPAD(' begin ' || null,80),

    20:        RPAD('  if :old.inserted_by is null then ' || null,80),

    21:        RPAD('    :new.inserted_by   := USER; ' || null,80),

    22:        RPAD('    :new.inserted_date := SYSDATE; ' || null,80),

    23:        RPAD('    :new.updated_by    := null; ' || null,80),

    24:        RPAD('    :new.updated_date  := null; ' || null,80),

    25:        RPAD('   else ' || null,80),

    26:        RPAD('    :new.inserted_by   := :old.inserted_by; ' || null,80),

    27:      RPAD('    :new.inserted_date := :old.inserted_date; ' || null,80),

    28:        RPAD('    :new.updated_by    := USER; ' || null,80),

    29:        RPAD('    :new.updated_date  := SYSDATE; ' || null,80),

    30:        RPAD('    end if; ' || null,80),

    31:        RPAD(' end; ' || null,80),

    32:        RPAD( '/' || null,80)

    33:    from TAB;

    34:    spool off

    35:    set FEEDBACK ON

    36:    set TERMOUT ON

    37:    set VERIFY ON

    38:    set ECHO ON

    39:    spool dbtrg.log

    40:    start dbtrg.sql

    41:    spool off

    42:    exit

Lines 1 through 7 set up the SQL*Plus environment so that no extra messages appear in the cre_dbtrg.sql file (see line 8). Lines 9 through 14 create the SQL alter table statement that will add the audit fields to each table, and lines 15 through 33 create the SQL create trigger statement that will add the database triggers necessary to maintain these audit fields. Lines 35 through 38 reset the SQL*Plus environment so that all SQL commands and messages display. Line 40 then runs the SQL*Plus command file cre_dbtrg.sql that was just created.

In Listing 6.9, line 39 opens the file DBTRG.LOG. This file will contain the output (an audit trail) when the DBTRG.SQL statement is executed with the START command on Line 40. I like to create SQL audit trails for various DBA commands, particularly ones such as this example where the process is rather automated. The audit trails enable me to review the additions and any errors that might have occurred by simply editing the log file.

SQL Creating Command Language Scripts

SQL*Plus formatting commands are quite versatile. Besides their uses discussed previously, they can be used to create operating system-dependent command language scripts. The examples in this section apply to an MS-DOS environment; however, the scripts can easily be adapted to any operating system-dependent command language.

The example in Listing 6.10 applies the SQL creating SQL discussed in Listing 6.8 to create a DOS BAT file.

Listing 6.10. SQL creating command language scripts.

    1:    column HOME_DIR new_value HDIR noprint

    2:    column PROGRAM_DIR new_value PDIR noprint

    3:    column PROGRAM_SUFFIX new_value PSUF noprint

    4:    select HOME_DIR,PROGRAM_DIR,PROGRAM_SUFFIX

    5:       from APPLICATION_DEFAULTS

    6:    /

    7:    spool  LIST614.BAT

    8:    select 'CD &PDIR'

    9:        from dual

    10:    /

    11:    select 'DIR *.&PSUF'

    12:        from dual

    13:    /

    14:    select 'CD &HDIR'

    15:        from dual

    16:    /

    17:    spool off

    18:    exit

The following code is the output created by Listing 6.10.

    CD \COBOL\PROGRAMS

    DIR *.COB

    CD \

Listing 6.10 is a simple example of creating an MS-DOS batch file with SQL*Plus formatting commands. The important concept of this example comes in lines 1 through 3. These lines contain three column commands that contain the NEW_VALUE clause. The importance of this concept is that these variables can be loaded from the Oracle database and their values referenced again in other SQL queries. Lines 4 and 5 populate these variables as named in the column statement. Note that when the variables are referenced in other SQL queries (lines 8, 11, and 14), the reference is to the NEW_VALUE variable name.

Use the column command with the NEW_VALUE option to load variables from Oracle tables to use in other SQL queries.

**SQL*Plus Creating Database-Driven Command Language Scripts**

The final example, Listing 6.11, incorporates a variety of concepts discussed in this chapter. The goal of this example is to load all program names and program sizes found in a particular directory structure, along with some database information, into an Oracle database table, APPLICATION_PROGRAMS. This directory structure is stored in a different Oracle database table, APPLICATION_DEFAULTS.

Listing 6.11. MS-DOS batch command file.

    1:    SQLPLUS -S HOTKA/DAN @LIST6_16.SQL

    2:    CALL LIST6_16.BAT

    3:    SED -F LIST6_19.SED LIST6_15A.DAT > LIST6_15B.DAT

    4:    SQLLOAD USERid=HOTKA/DAN CONTROL=LIST6_16.CTL

Listing 6.10 is the actual MS-DOS bat command file that runs the four computer tasks to accomplish our goal. The SQLPLUS command on line 1 connects to the database and runs the SQL*Plus command file LIST6_16.SQL (see Listing 6.12). LIST6_16.SQL creates two files, LIST6_16.BAT (see Listing 6.12) and LIST6_16.CTL (see Listing 6.13). Line 2 executes the newly created LIST6_16.BAT file. This command creates the file, LIST6_15A.DAT, that is an MS-DOS DIR (directory) list of directory 'C:\COBOL'. Line 3 is a stream editor (SED) that deletes the first few lines and the last few lines (as directed by LIST6_19.SED; see Listing 6.15) of file LIST6_15A.DAT, creating LIST6_15B.DAT. This file is the MS-DOS DIR output without the heading and trailing text information. Line 4 then runs Oracle's SQL*Loader program, using the LIST6_16.CTL SQL*Loader control file created by line 1 and reading the datafile LIST6_15B.DAT file created by line 3.

Listing 6.12 is the LIST6_16.SQL referenced in Line 1 of Listing 6.11 and will create the LIST6_16.BAT file referenced in Line 2 of Listing 6.11.

Listing 6.12. SQL*Plus command file LIST6_16.SQL.

    1:    set PAGESIZE 0

    2:    column HOME_DIR new_value HDIR noprint

    3:    column PROGRAM_DIR new_value PDIR noprint

    4:    column PROGRAM_SUFFIX new_value PSUF noprint

    5:    select HOME_DIR,PROGRAM_DIR,PROGRAM_SUFFIX

    6:       from APPLICATION_DEFAULTS

    7:    /

    8:    spool  LIST6_16.BAT

    9:    select 'DIR &PDIR\*.&PSUF > &HDIR\LIST6_15A.DAT'

    10:        from dual

    11:    /

    12:    spool off

    13:    spool LIST6_16.ctl

    14:    select 'load data'

    15:         from dual

    16:    /

    17:    select 'infile '|| '''' || 'LIST6_15B.DAT' || ''''

    18:            from dual

    19:    /

    20:    select 'append'

    21:          from dual

    22:    /

    23:    select 'into table APPLICATION_PROGRAMS'

    24:         from dual

    25:    /

    26:    select '(PROGRAM_NAME position(1:8) char,'

    27:         from dual

    28:    /

    29:    select 'PROGRAM_SUFFIX constant  ' || '''' || '&PSUF' || '''' || ','

    30:         from dual

    31:    /

    32:    select ÔPROGRAM_SIZE position(15:22) integer external,'

    33:           from dual

    34:    /

    35:    select 'PROGRAM_PATH constant ' || '''' || '&PDIR' || '''' || ','

    36:          from dual

    37:    /

    38:    select 'ASSIGNED_ANALYST constant ' || '''' || '&USER' || '''' || ')'

    39:          from dual

    40:    /

    41:    spool off

    42:    exit

The file in Listing 6.13, LIST6_16.BAT, was created by Listing 6.12, lines 8 and 9.

Listing 6.13. SQL creating MS-DOS batch file output.

    DIR C:\COBOL\*.COB > C:\FILES\LIST6_15A.DAT

The Oracle SQL*Loader control file in Listing 6.14, LIST6_16.CTL, was created by the remainder of Listing 6.12, beginning at line 13.

Listing 6.14. SQL creating Oracle SQL*Loader control file output.

    load data

    infile 'LIST6_15B.DAT'

    append

    into table APPLICATION_PROGRAMS

     (PROGRAM_NAME position(1:8) char,

    PROGRAM_SUFFIX constant 'COB',

    PROGRAM_SIZE position(15:22) integer external,

    PROGRAM_PATH constant 'C:\COBOL',

    ASSIGNED_ANALYST constant 'HOTKA')

The file in Listing 6.15 is needed to modify the LIST16_5A.DAT file, the file created from a MS-DOS DIR command (see Listing 6.13). Remember: Listing 6.13 was created by Listing 6.12 at Line 9.

Listing 6.15. LIST6_17.SED file used in Listing 6.15, line 3.

    1,4d

    /bytes/,$d

Listing 6.12 expands on the Listing 6.10 example. This SQL*Plus command file reads the Oracle database, loading three user variables with default information from database table APPLICATION_DEFAULTS (lines 2 through 7). Line 8 opens the first file, LIST6_16.BAT. Simple text, in the form of MS-DOS commands, is joined with information stored in the above-mentioned variables with default information (see line 9 for syntax and Listing 6.13 to view results of this SQL query). This file is closed at line 12 and the second output file, LIST6_16.CTL, is opened. This file is the control file that tells Oracle's SQL*Loader what to do. Lines 14 through 40 are a series of select ... from dual SQL queries. Each of these SQL statements will output one line. The table DUAL (its real name is SYSTEM.DUAL) contains one column and one row and is convenient in the example when only one row of output is desired from each of these SQL queries (see lines 14 through 40). Lines 29 and 35 incorporate the default information stored in the user variables. Listing 6.14 displays the results of this series of SQL queries.

The goal here was to use information stored in the Oracle database to retrieve information from an operating system file system directory and to load this information into the Oracle database. Listing 6.11 drives this whole example, running Listing 12 to create the necessary files with information from the Oracle database, preparing the output file created for loading, and running the Oracle SQL*Loader process with the SQL*Loader control file created by Listing 6.12. The goal of this example is a simple one and used several of the concepts in this chapter.

The table SYSTEM.DUAL, or DUAL, is a one-column, one-row table that played a major role in the programming of Oracle tools before the introduction of Oracle's PL/SQL software.

Line 9 of Listing 6.12 can easily be adapted for more complex command syntax using the SQL UNION operator discussed previously in this chapter.

Summary

In this chapter you learned the history and functional uses of SQL*Plus and saw an in-depth list of SQL*Plus commands with examples. You used these commands in a variety of ways to produce report and program output examples. Some of the features discussed in this chapter are not directly referenced in Oracle documentation.

Hopefully, you can utilize the skills and refer to the examples provided in this chapter in your application, design, and development of Oracle-based products.