2.5 How do I…Prompt users for substitution variables?

Problem

In many cases, I don’t know the information I want to use in my SQL scripts but can get the information at runtime. I need to create my own user-defined variables that I can reference throughout my script. How do I prompt users for substitution variables?

Technique

User-defined variables can be created in SQL*Plus using the ACCEPT command. The execution of the script is halted, and the user is prompted to enter a value for the variable. After the user enters the value, the execution of the script continues at the line following the statement. When created, any references to the user-defined variable are replaced with the value entered. The format of the ACCEPT command is:

ACCEPT VARIABLE

After a user-defined variable has been created using either of these methods, it can be used throughout the SQL*Plus session by prefixing the variable name with an ampersand (&). The user will not be prompted for the value of the variable again, even if it is used as a substitution variable.

Steps

1. CHP2_9.SQL, shown in Figure 2.10, uses a complex method for defining user-defined variables.

The first line of the script sets the HEADING system variable to OFF to suppress the output of the headings generated by the query. The SPOOL statement contained in the second line opens a temporary file that is later executed. Lines 3 and 4 create a DEFINE statement using the result of a query of the DUAL table to dynamically generate a statement. The SPOOL OFF statement in line 5 terminates the creation of the temporary file. The SET HEADING ON statement restores the headings for subsequent queries. The last statement executes the temporary file using the START command. After the ACCEPT statement is executed, the variable can be used in other statements. Execute CHP2_9.SQL using the START command.

SQL> START CHP2_9.sql

When this script is executed, the net result is that the MYDATE user-defined variable gets populated with the results of a query. If an error is returned from the SPOOL OFF statement, it might be ignored. The statement shown in the output of the query is executed by the START command in the last line of the script.

2. Use the MYDATE user-defined variable in a statement. Any reference to &MYDATE in a SQL statement or SQL*Plus command will be replaced with a string containing a system date.

SQL> SELECT ‘&MYDATE’ FROM DUAL;
‘01-FEB-98
----------
01-FEB-98

How It Works

The first line of CHP2_9.SQL turns off the column headings for the next few statements. The second line uses the SPOOL command to create a file called TEMP.SQL. The SPOOL OFF command stops output to the TEMP.SQL file and closes the file. The command START TEMP runs the temporary command file to prompt the user for the MYDATE substitution variable. Because user-defined variables exist for the life of the SQL*Plus session, when Step 2 is executed, the user is not prompted for a value for &MYDATE.

Comments

This technique is invaluable for queries that are run several times with different search or select conditions. Instead of writing a new script each time a new value is needed, you can simply prompt the user at runtime.