087-090

Previous

Table of Contents

Next

Scripting In SQL*Plus

SQL*Plus contains a number of features that allow developers to write complex scripts to accomplish a variety of tasks . Although many of these features have been supplanted by the evolution of reporting tools for Oracle, the features still exist and can be used to give your scripts some extra oomph.

SQL*Plus Commands

SQL*Plus provides numerous commands that allow you to control almost every aspect of the way data is displayed. Some of these commands are now used infrequently at best, but a general familiarity with them will give you a better understanding of the kinds of tasks that you can accomplish with SQL scripts.

In addition to these commands, SQL*Plus provides a number of functions (such as min() and max() ) that handle common operations. These functions are documented in Appendix A, along with some other useful functions.

The @ Command

The @ (at sign) command is used to open and run a script from the SQL*Plus command prompt, as shown in the following example:

 @set_grade 999999999 2103 'A' 

The / Command

The / (forward slash) command instructs SQL*Plus to execute the commands stored in the command buffer.

Commenting

SQL*Plus and PL/SQL support both single and multiple line comments, as shown in the following example:

 -- This is a single line comment. /*  This is a multiline comment, like you might find in a C program. */ 

You may use either style of commenting inside your scripts.

The accept Command

You can use the accept command to have your script require input from the user . The syntax for the accept command is:

 accept <  variable  > datatype [prompt <  input prompt  >}] 

where variable is the name of the variable, datatype is char or number , and input prompt is the prompt that should be displayed. You may use the no prompt option if you do not want a prompt for input, as shown in the following statement:

 accept password char no prompt; 

If for some reason you do not wish for the user s input to be echoed to the screen, you may use the hide option, as follows :

 accept password char no prompt hide; 

The break Command

The break command instructs SQL*Plus to perform certain actions when a specified event occurs while processing the results of a SELECT command. There are four types of events that can be specified as part of the break command: expression , row , page , and report .

    expression ”The name of a column being queried is used as an event. Every time the value of the specified column changes, SQL*Plus will perform the action defined in the break event for the expression. In Listing 3.5, the type column is specified as the break event. When the value of this column changes, SQL*Plus calculates the average value of the length column for every type object.

    row ”Every row retrieved is used as an event.

    page ”The end of a page is used as an event.

    report ”The end of the report is used as an event.

Multiple events can be used with the break statement. Consider again the script shown in Listing 3.5, and notice that the report calculates the average number of lines for each type of object and for all the objects. This is accomplished with the following break command:

 break on type report skip page 

There is a distinct set of actions that can be performed with a break: skip n , skip page , noduplicates , and duplicates .

    skip n ”Instructs SQL*Plus to skip n lines when the event occurs.

    skip page ”Instructs SQL*Plus to skip to the next page when the event occurs.

    noduplicates ”Instructs SQL*Plus to print blanks for the value of a break column when the value hasn t changed. This is usually abbreviated as nodup .

    duplicates ”Instructs SQL*Plus to always print the value of a break column.

When used alone, the break command will display the break events that are currently in place.

The btitle Command

The btitle command instructs SQL*Plus to print a title (or footer) at the bottom of each page of a report. The command allows you to perform the following actions:

   You can use multiple lines in the footer using the skip clause, as shown in this example:

 btitle skip  n  SYSDATE; 

In this example, n is the number of lines to be skipped and defaults to one line. If n is set to , SQL*Plus returns to the beginning of the current line.

   You can align text to the left, right, or center, or to a specified column, as follows:

 btitle left 'Malden Power' center 'Quality Control' right SYSDATE;   btitle col 50 'Report'; 

   You can specify a number of tabs for alignment of the footer, as shown in this example:

 btitle tab  n  'Malden Power'; 

In this example, n is an integer value; a negative value skips backward, and a positive value skips forward.

   You can turn the title off and on without affecting its definition, as shown in this example:

 btitle off;   btitle on; 

The clear Command

The clear command allows you to erase certain settings from your current SQL*Plus environment using the following conditions:

    breaks ”Erases all defined break conditions set by the break command.

    buffer ”Erases text from the current buffer.

    columns ”Erases all options set by the column command.

    computes ”Erases all options set by the compute command.

    sql ”Erases the SQL command buffer.

Before setting up new conditions ( especially with the break and compute commands), it s a good idea to clear these options first.

Previous

Table of Contents

Next