Substitution variables allow you to write generic SQL*Plus scripts. They allow you to mark places in a script where you want to substitute values at runtime.
A substitution variable is the same thing as a user variable. In the previous chapter, you saw how to get the contents of a database column into a user variable and how to place the contents of that user variable into the page header of a report. SQL*Plus also allows you to place user variables in your script to mark places where you want to supply a value at runtime. When you use them this way, they are called substitution variables.
A substitution variable is not like a true variable used in a programming language. Instead, a substitution variable marks places in the text where SQL*Plus does the equivalent of a 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 either one or two ampersand characters. Say, for example, that 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,
project_hours ph
WHERE ph.employee_id = 107
AND p.project_id = ph.project_id;As you can see, 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, 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. It would look like this:
SELECT DISTINCT p.project_id, p.project_name
FROM project p,
project_hours ph
WHERE ph.employee_id = &employee_id
AND p.project_id = ph.project_id;The ampersand in front of the word “employee_id” marks it as a variable. At run-time, when it reads the statement, SQL*Plus will see the substitution variable and replace it with the current value of the specified user variable. If the employee_id user variable contained a value of 104, then “&employee_id” would be replaced by “104”, and the resulting line would look like this:
WHERE ph.employee_id = 104
As stated earlier, and as you can see now, SQL*Plus truly does a “search and replace” operation. The Oracle database does not know that a variable has been used. Nor does SQL*Plus actually compare the contents of the employee_id column against the value of the variable. SQL*Plus simply 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 just 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.
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 Labor Hours and Dollars Detail report shown in Chapter 3. You will see how you can modify the script to print the report for only 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.
The report in the previous chapter produced detailed hours and dollars information for all employees. To reduce the scope to one employee, you can add this line to the WHERE clause:
AND e.employee_id = 107
Since this report is now only for one employee, the grand totals don’t make sense, so the COMPUTEs to create them can be removed. Finally, a SPOOL command has been added to capture the output in a file to be printed later. The complete script for the report looks like this:
--Setup pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55
--Set the linesize, which must match the number of equal signs used
--for the ruling lines in the headers and footers.
SET LINESIZE 71
--Get the date for inclusion in the page footer.
SET TERMOUT OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY';
COLUMN SYSDATE NEW_VALUE report_date
SELECT SYSDATE FROM DUAL;
SET TERMOUT ON
--Setup 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 -
LEFT report_date -
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
CLEAR COMPUTES
COMPUTE SUM LABEL 'Project Totals' OF hours_logged ON project_name
COMPUTE SUM LABEL 'Project Totals' OF dollars_charged ON project_name
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 C:\A\HOURS_DOLLARS
SELECT P.PROJECT_ID,
P.PROJECT_NAME,
TO_CHAR(PH.TIME_LOG_DATE,'dd-Mon-yyyy') time_log_date,
PH.HOURS_LOGGED,
PH.DOLLARS_CHARGED,
E.EMPLOYEE_ID,
E.EMPLOYEE_NAME
FROM EMPLOYEE E,
PROJECT P,
PROJECT_HOURS PH
WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
AND P.PROJECT_ID = PH.PROJECT_ID
AND E.EMPLOYEE_ID = 107
ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE;
SPOOL OFF
--Reset everything back to the defaults.
CLEAR BREAKS
CLEAR COMPUTES
TTITLE OFF
BTITLE OFF
SET NEWPAGE 1
SET PAGESIZE 24
SET LINESIZE 80Running this script as shown will produce a report specifically for employee 107.
You don’t want to edit the script file and 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:
AND 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 it needs to inform 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, for example, using &id for the variable would leave the user wondering whether to enter an employee ID or a project ID. 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.
When you run the report, SQL*Plus will prompt you for the value of
the &employee_id substitution variable. Assume that the script is
in a file named HOURS_DOLLARS.SQL. Here’s
how the output will look:
SQL>@c:\hours_dollars_bEnter value for employee_id:111
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.
After prompting for a value and substituting it into the script in place of the corresponding variable, SQL*Plus will display both 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 13: AND E.EMPLOYEE_ID = &employee_id new 13: AND E.EMPLOYEE_ID = 111
Next, SQL*Plus goes on to read the remaining lines from the script, producing this hours and dollars report for Taras Shevchenko:
The Fictional Company
I.S. Department Project Hours and Dollars Detail
=======================================================================
Employee: 111 Taras Shevchenko
Dollars
Proj ID Project Name Date Hours Charged
------------ -------------------------- ----------- ------ ------------
1001 Corporate Web Site 01-Jan-1998 1 $100.00
01-Mar-1998 3 $300.00
01-May-1998 5 $500.00
01-Jul-1998 7 $700.00
01-Sep-1998 1 $100.00
01-Nov-1998 3 $300.00
************************** ------ ------------
Project Totals 20 $2,000.00
...In addition to being displayed on the screen, the report is also spooled to the file specified in the script.
In the example just shown, the report was both displayed on the screen and spooled to a file. In Chapter 3 you saw how the SET TERMOUT OFF command could be used to suppress output to the display while still allowing it to be spooled, thus making a report run much faster. Trying to do the same thing in this case presents a special problem. The problem is that the command to turn 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 too 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. Here’s what you will see:
SQL> @c:\hours_dollars_cStrangely enough, even if you remember that SQL*Plus needs an employee number and you type one in, it won’t be accepted. Try running the script like this:
SQL>@c:\hours_dollars_c111
Even though you entered a value of 111, SQL*Plus will proceed as if you had entered an empty string. The end result will be the following error in the spool file:
Enter value for employee_id: old 13: AND E.EMPLOYEE_ID = &employee_id new 13: AND E.EMPLOYEE_ID = ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE * ERROR at line 14: ORA-00936: missing expression
Looking at the before and after versions of the line with the &employee_id variable, which are written to the spool file, you can see that the input of 111 was totally ignored. The result was a syntactically incorrect SQL statement, so instead of a report all you got was an error.
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 this chapter in the section titled Section 4.3.
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 don’t usually want to prompt the user separately for each occurrence.
Take a look at the following script, which displays information about a table followed by a list of all indexes defined on the table:
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 COLUMNSThis script 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 tables’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>@c:\list_indexes_dEnter value for table_name:project_hoursName Null? Type ------------------------------- -------- ---- PROJECT_ID NOT NULL NUMBER EMPLOYEE_ID NOT NULL NUMBER TIME_LOG_DATE NOT NULL DATE HOURS_LOGGED NUMBER DOLLARS_CHARGED NUMBER
Since only a single ampersand was used, the value entered by the user was used for that one specific instance. It was not saved for future reference. The result is that next time SQL*Plus encounters &table_name, it must prompt again. This time it prompts 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 a SQL query. When the DESCRIBE command was read, the user was 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 indexes defined on the project_hours table, looks like this:
INDEX: PROJECT_HOURS_BY_DATE
TABLESPACE: USER_DATA
COLUMNS: TIME_LOG_DATE
INDEX: PROJECT_HOURS_EMP_DATE
TABLESPACE: USER_DATA
COLUMNS: EMPLOYEE_ID
TIME_LOG_DATE
UNIQUE INDEX: PROJECT_HOURS_PK
TABLESPACE: USER_DATA
COLUMNS: PROJECT_ID
EMPLOYEE_ID
TIME_LOG_DATE
6 rows selected.
Commit complete.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 very 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 will save the value. All subsequent references to the same variable use that same value. It doesn’t even matter if subsequent references use a double ampersand or a single. 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.
Now if you run the LIST_INDEXES script, you will only be prompted once for the table name, as the following output shows:
SQL>@c:\list_indexes_eEnter value for table_name:project_hoursName Null? Type ------------------------------- -------- ---- PROJECT_ID NOT NULL NUMBER EMPLOYEE_ID NOT NULL NUMBER TIME_LOG_DATE NOT NULL DATE HOURS_LOGGED NUMBER DOLLARS_CHARGED NUMBER old 9: AND ui.table_name = UPPER('&table_name') new 9: AND ui.table_name = UPPER('project_hours') INDEX: PROJECT_HOURS_BY_DATE TABLESPACE: USER_DATA COLUMNS: TIME_LOG_DATE INDEX: PROJECT_HOURS_EMP_DATE TABLESPACE: USER_DATA COLUMNS: EMPLOYEE_ID TIME_LOG_DATE UNIQUE INDEX: PROJECT_HOURS_PK TABLESPACE: USER_DATA COLUMNS: PROJECT_ID EMPLOYEE_ID TIME_LOG_DATE 6 rows selected. Commit complete.
If you run the LIST_INDEXES 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 either exit SQL*Plus or explicitly undefine the variable.
Because variable definitions persist after a script has ended, it’s usually best to explicitly prompt a user for input rather than depending 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.
Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
