Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

8.2 Using Substitution Variables

Substitution
variables allow you to write generic SQL*Plus scripts.
They let you mark places in a script where you want to substitute
values at runtime.

8.2.1 What Is a Substitution Variable?

A
substitution variable is not like a true variable used in a
programming language. Instead, substitution variables mark places in
the text where SQL*Plus does the equivalent of search and replace at
runtime, replacing the reference to a substitution variable with its
value.

Substitution variables are set off in the text of a script by
preceding them with one or two ampersand characters. Say, for
example, you had this query to list all projects to which employee
107 had charged time:

SELECT DISTINCT p.project_id, p.project_name
FROM project p INNER JOIN project_hours ph
ON p.project_id = ph.project_id
WHERE ph.employee_id = 107;

This query is specific to employee number 107. To run the query for a
different employee, you would need to edit your script file, change
the ID number, save the file, and then execute it.
That's a pain. You don't want to do
that. Instead, you can generalize the script by rewriting the SELECT
statement with a substitution variable in place of the employee ID
number. That script might look like this:

SELECT DISTINCT p.project_id, p.project_name
FROM project p INNER JOIN project_hours ph
ON p.project_id = ph.project_id
WHERE ph.employee_id = &employee_id;

The ampersand in front of &employee_id marks
it as a variable. At runtime, when it reads the statement, SQL*Plus
sees the substitution variable and replaces it with the current value
of that variable. If employee_id contains a value
of 104, then &employee_id is replaced by
"104", and the resulting line looks
like this:

WHERE ph.employee_id = 104 As I said earlier, and as you can see, SQL*Plus does a
search-and-replace operation. The Oracle database
doesn't know that a variable has been used. Nor does
SQL*Plus compare the contents of the employee_id
column against the value of the variable. SQL*Plus does the
equivalent of a search-and-replace operation on each statement before
that statement is executed. As far as the Oracle database is
concerned, you might as well have included constants in your script.

Substitution variables are the workhorse of SQL*Plus scripts. They
give you a place to store user input, and they give you a way to use
that input in SQL queries, PL/SQL code blocks, and other SQL*Plus
commands.

8.2.2 Using Single-Ampersand
Variables

The easiest way to generalize a script is to take one you have
working for a specific case and modify it by replacing specific
values with substitution variables. In this section, we will revisit
the Project Hours and Dollars Detail report shown in Example 7-2. You will see how you can modify the script
to print the report for one employee, and you will see how you can
use a substitution variable to generalize that script by making it
prompt for the employee ID number at runtime.

When SQL*Plus encounters a variable with a single leading ampersand, it always prompts you for a value. This is true even when you use the same variable multiple times in your script. If you use it twice, you will be prompted twice. Double-ampersand variables allow you to prompt a user only once for a given value and are explained later in this chapter.

8.2.2.1 The report for one specific employee

The report from Example 7-2 produced detailed
hours and dollars information for all employees. To reduce the scope
to one employee (e.g., employee 107), you can add this line as the
WHERE clause in the report's underlying query:

WHERE e.employee_id = 107 Example 8-1 shows the resulting script, to which
I've added a SPOOL command to write the report
output to a file.

Example 8-1. Using a substitution variable to mark a user-supplied value

SET ECHO OFF
SET RECSEP OFF
--Set up pagesize parameters
SET NEWPAGE 1
SET PAGESIZE 55
--Set the linesize, which must match the number of equals signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 66
--Set up page headings and footings
TTITLE CENTER "The Fictional Company" SKIP 3 -
LEFT "I.S. Department" -
RIGHT "Project Hours and Dollars Detail" SKIP 1 -
LEFT "========================================" -
"==========================" -
SKIP 2 "Employee: " FORMAT 9999 emp_id_var " " emp_name_var SKIP 3
BTITLE LEFT "========================================" -
"==========================" -
SKIP 1 -
RIGHT "Page " FORMAT 999 SQL.PNO
--Format the columns
COLUMN employee_id NEW_VALUE emp_id_var NOPRINT
COLUMN employee_name NEW_VALUE emp_name_var NOPRINT
COLUMN project_id HEADING "Proj ID" FORMAT 9999
COLUMN project_name HEADING "Project Name" FORMAT A26 WORD_WRAPPED
COLUMN time_log_date HEADING "Date" FORMAT A11
COLUMN hours_logged HEADING "Hours" FORMAT 9,999
COLUMN dollars_charged HEADING "Dollars|Charged" FORMAT $999,999.99
--Breaks and computations
BREAK ON employee_id SKIP PAGE NODUPLICATES -
ON employee_name NODUPLICATES -
ON project_id SKIP 2 NODUPLICATES -
ON project_name NODUPLICATES
COMPUTE SUM LABEL 'Totals' OF hours_logged ON project_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON project_id
COMPUTE SUM LABEL 'Totals' OF hours_logged ON employee_id
COMPUTE SUM LABEL 'Totals' OF dollars_charged ON employee_id
--Execute the query to generate the report.
SPOOL ex8-1.lst
SELECT e.employee_id,
e.employee_name,
p.project_id,
p.project_name,
TO_CHAR(ph.time_log_date,'dd-Mon-yyyy') time_log_date,
ph.hours_logged,
ph.dollars_charged
FROM employee e INNER JOIN project_hours ph
ON e.employee_id = ph.employee_id
INNER JOIN project p
ON p.project_id = ph.project_id
WHERE e.employee_id = 107
ORDER BY e.employee_id, p.project_id, ph.time_log_date;
SPOOL OFF
EXIT Running the script in Example 8-1 will produce a
report specifically for employee 107:

The Fictional Company
I.S. Department Project Hours and Dollars Detail
==================================================================
Employee: 107 Lesia Ukrainka
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
1001 Corporate Web Site 02-Jan-2004 1 $45.00
02-Mar-2004 3 $135.00
02-May-2004 5 $225.00
. . .

The next step is to generalize the script to make it usable for any
employee.

8.2.2.2 Generalizing the report with substitution variables

You don't want to modify your script every time you
need to produce a report for a different employee, and you
don't have to. Instead, you can replace the
reference to a specific employee number with a substitution variable
and let SQL*Plus prompt you for a value at runtime.
Here's how the affected line of script looks with a
substitution variable instead of a hardcoded value:

WHERE e.employee_id = &employee_id The variable name should be descriptive, and it needs to serve two
purposes. It needs to inform the user and you. First and foremost,
the variable name is used in the prompt and must convey to the user
the specific information needed. In this case, using
&id for the variable would leave the user
wondering whether to enter an employee ID or a project ID. The use of
&employee_id clarifies the answer. The second
thing to keep in mind is that you will need to look at the script
again someday, so make sure the name is something that will jog
your memory as well.

8.2.2.3 Running the report

In the examples for this book, the modified report script can be
found in ex8-1b.sql. When you run the report,
SQL*Plus will prompt you to provide a value for the
&employee_id substitution variable.
Here's how the script execution and prompt will
look:

oracle@gennick02:~/sqlplus/ExampleScripts> sqlplus gennick/secret @ex8-1b
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Jun 27 22:53:12 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter value for employee_id: 108 As commands are executed, SQL*Plus constantly looks for the ampersand
character, indicating a substitution variable. When an ampersand is
encountered, the next token in the command is treated as a variable.
SQL*Plus first looks to see if that variable has been previously
defined. In this example it hasn't, so SQL*Plus
automatically prompts for the value.

Be sure to run the examples in this chapter using command-line SQL*Plus. Many commands, notably SPOOL and SET NEWPAGE, are not available in i SQL*Plus, so these scripts that function perfectly well in the command-line environment will fail in the web environment.

After prompting for a value and substituting it
into the script in place of the corresponding variable, SQL*Plus
displays the old and the new versions of the particular line of
script involved. During development, this aids you in verifying that
your script is executing correctly. Here are the before and after
versions of the line containing the
&employee_id variable from the current
example:

old 12: WHERE e.employee_id = &employee_id
new 12: WHERE e.employee_id = 108 Next, SQL*Plus goes on to read the remaining lines from the script,
producing the following hours and dollars report for Pavlo Chubynsky.

The Fictional Company
I.S. Department Project Hours and Dollars Detail
==================================================================
Employee: 108 Pavlo Chubynsky
Dollars
Proj ID Project Name Date Hours Charged
------- -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Jan-2004 1 $220.00
01-Mar-2004 3 $660.00
01-May-2004 5 $1,100.00
01-Jul-2004 7 $1,540.00
01-Sep-2004 1 $220.00
01-Nov-2004 3 $660.00
******* ************************** ------ ------------
Totals 20 $4,400.00
. . .

In addition to being displayed on the screen, the report is spooled
to the ex8-1b.lst file, as specified in the
script.

8.2.2.4 When TERMOUT is off

In the example just shown, the report was
displayed on the screen and spooled to a file. In Chapter 5 you saw how the SET TERMOUT OFF command
could be used to suppress output to the display while allowing it to
be spooled, thus making a report run much faster. Doing the same
thing in this case presents a special problem. The problem is that
the command SET TERMOUT OFF must precede the SELECT statement that
generates the report, so terminal output is off by the time SQL*Plus
reads the line containing the substitution variable. SQL*Plus does
not handle this situation well. You won't see a
prompt for the substitution variable because terminal output is off,
but SQL*Plus will still be waiting for you to type in a value. Your
session will appear to be hung.

Using the example scripts, you can demonstrate the problem of an apparent hung session by executing script ex8-1c.sql: sqlplus username/password @ex8-1c SQL*Plus will display the normal messages about the release of SQL*Plus and the release of the database to which you are connected. And that's it. SQL*Plus will be awaiting your input, but you'll never see a prompt.

There is a solution to this problem. The solution is to use the
ACCEPT command to explicitly prompt the user for the employee ID
prior to issuing the SET TERMOUT OFF command. You will see how to do
this later in the section titled "Prompting for
Values."

8.2.3 Using Double-Ampersand Variables

Using
a
double ampersand in front of a
substitution variable tells SQL*Plus to define that variable for the
duration of the session. This is useful when you need to reference a
variable several times in one script because you usually
don't want to prompt the user separately for each
occurrence.

8.2.3.1 An example that prompts twice for the same value

Take a look at the script in Example 8-2, which
displays information about a table followed by a list of all indexes
defined on the table.

Example 8-2. Prompting twice for the same substitution variable

SET HEADING OFF
SET RECSEP OFF
SET NEWPAGE 1
COLUMN index_name FORMAT A30 NEW_VALUE index_name_var NOPRINT
COLUMN uniqueness FORMAT A6 NEW_VALUE uniqueness_var NOPRINT
COLUMN tablespace_name FORMAT A30 NEW_VALUE tablespace_name_var NOPRINT
COLUMN column_name FORMAT A30
BREAK ON index_name SKIP PAGE on column_header NODUPLICATES
TTITLE uniqueness_var ' INDEX: ' index_name_var -
SKIP 1 ' TABLESPACE: ' tablespace_name_var -
SKIP 1
DESCRIBE &table_name
SELECT ui.index_name,
ui.tablespace_name,
DECODE(ui.uniqueness,'UNIQUE','UNIQUE',' ') uniqueness,
' COLUMNS:' column_header,
uic.column_name
FROM user_indexes ui,
user_ind_columns uic
WHERE ui.index_name = uic.index_name
AND ui.table_name = UPPER('&table_name')
ORDER BY ui.index_name, uic.column_position;
TTITLE OFF
SET HEADING ON
SET RECSEP WRAPPED
CLEAR BREAKS
CLEAR COLUMNS Example 8-2 uses &table_name
twice, once in the DESCRIBE command that lists the columns for the
table and once in the SELECT statement that returns information about
the table's indexes. When you run this script,
SQL*Plus will issue separate prompts for each occurrence of
&table_name. The first prompt will occur when
SQL*Plus hits the DESCRIBE command:

SQL> @ex8-2
Enter value for table_name: project_hours
Name Null? Type
----------------------------------------- -------- ------------------
PROJECT_ID NOT NULL NUMBER(4)
EMPLOYEE_ID NOT NULL NUMBER
TIME_LOG_DATE NOT NULL DATE
HOURS_LOGGED NUMBER(5,2)
DOLLARS_CHARGED NUMBER(8,2) Because only a single ampersand was used in the script, the value
entered is used for that one specific instance. It is not saved for
future reference. The result is that next time SQL*Plus encounters
&table_name, it must prompt again, this time
for the table name to use in the SELECT statement:

Enter value for table_name: project_hours
old 9: AND ui.table_name = UPPER('&table_name')
new 9: AND ui.table_name = UPPER('project_hours') Notice that SQL*Plus only displays before and after images of a line
containing substitution variables when that line is part of an SQL
statement. When the DESCRIBE command was read, the script prompted
for a table name, and the substitution was made, but the old and new
versions of the command were not shown.

The remaining output from the script, showing the index defined on
the project_hours table, looks like this:

UNIQUE INDEX: PROJECT_HOURS_PK
TABLESPACE: USERS
COLUMNS: PROJECT_ID
EMPLOYEE_ID
TIME_LOG_DATE

8.2.3.2 A modified example that prompts once

Obviously there's room for improvement here. You
don't want to type in the same value over and over
just because it's used more than once in a script.
Aside from being inconvenient, doing so introduces the real
possibility that you won't get it the same each
time. One way to approach this problem is to use a double ampersand
the first time you reference the table_name
variable in the script. Thus, the DESCRIBE command becomes:

DESCRIBE &&table_name The only difference between using a double ampersand rather than a
single ampersand is that when a double ampersand is used, SQL*Plus
saves the value. All subsequent references to the same variable use
that same value. It doesn't matter if subsequent
references use a double ampersand or a single ampersand. Once the
table_name variable has been defined this way, any
other reference to &table_name or
&&table_name will be replaced with the
defined value.

Execute ex8-2b.sql to see the behavior when using a double ampersand. Otherwise, the script is the same as ex8-2.sql.

You will be prompted only once for the table name, as the following
output shows:

SQL> @ex8-2b
Enter value for table_name: project_hours
Name Null? Type
----------------------------------------- -------- ---------------
PROJECT_ID NOT NULL NUMBER(4)
EMPLOYEE_ID NOT NULL NUMBER
TIME_LOG_DATE NOT NULL DATE
HOURS_LOGGED NUMBER(5,2)
DOLLARS_CHARGED NUMBER(8,2)
old 9: AND ui.table_name = UPPER('&table_name')
new 9: AND ui.table_name = UPPER('project_hours')
UNIQUE INDEX: PROJECT_HOURS_PK
TABLESPACE: USERS
COLUMNS: PROJECT_ID
EMPLOYEE_ID
TIME_LOG_DATE

8.2.3.3 A final caveat

If you run the
ex8-2b.sql script again, you
won't be prompted for a table name at all. Instead,
the value entered earlier will be reused, and you will again see
information about the project_hours table and its
indexes. The reason for this is that once you define a variable, that
definition sticks around until you exit SQL*Plus or explicitly
undefine the variable.

You can undefine the table_name variable by placing the following command at the end of the script: UNDEFINE table_name The version of the script in ex8-2c.sql has this command.

Because variable definitions persist after a script has ended,
it's usually best to explicitly prompt a user for
input rather than depend on SQL*Plus to do it for you. The ACCEPT
command is used for this purpose and is described in the next
section. At the very least, you should UNDEFINE variables at the end
of a script so they won't inadvertently be reused later.