Substituting Expressions and Text in Software

Generator USS Code 39 in Software Substituting Expressions and Text

Almost any element of a SQL statement may be substituted at runtime The constraint is that Oracle requires at least the first word to be static In the case of the SELECT statement, at the very minimum, the SELECT keyword is required and the remainder of the statement may be substituted as follows:

select &rest_of_statement;

When the statement is executed, you are prompted to submit a value for the variable called REST_OF_STATEMENT, which when appended to the SELECT keyword, is any legitimate query Useful candidates for ampersand substitution are statements that are run multiple times and differ slightly from each other

Double ampersand substitution is used to avoid repetitive input when the same variable occurs multiple times in a statement When a double ampersand substitution occurs, the variable is stored as a session variable As the statement executes, all further occurrences of the variable are automatically resolved using the stored session variable Any subsequent executions of the statement within the same session automatically resolve the substitution variables from stored session values This is not always desirable and indeed limits the usefulness of substitution variables Oracle does, however, provide a mechanism to UNDEFINE these session variables The VERIFY command is specific to SQL*Plus and controls whether or not substituted elements are echoed on the user s screen prior to executing a SQL statement that uses substitution variables

Session-level variables are implicitly created when they are initially referenced in SQL statements using double ampersand substitution They persist or remain available for the duration of the session or until they are explicitly undefined A session ends when the user exits their client tool like SQL*Plus or when the user process is terminated The problem with persistent session variables is they tend to detract from the generic nature of statements that use ampersand substitution variables Fortunately, these session variables can be removed with the UNDEFINE command Within a script or at the command line of SQL*Plus or SQL Developer, the syntax to undefine session variables is

UNDEFINE variable;

Consider a simple generic example that selects a static and variable column from the EMPLOYEES table and sorts the output based on the variable column:

select last_name, &&COLNAME from employees where department_id=30 order by &COLNAME;

The first time this statement executes, you are prompted to supply a value for the COLNAME variable Assume you enter SALARY This value is substituted and the statement executes A subsequent execution of this statement within the same session does not prompt for any COLNAME values, since it is already defined as SALARY in the context of this session and can only be undefined with the UNDEFINE COLNAME command Once the variable has been undefined, the next execution of the statement prompts the user for a value for the COLNAME variable The DEFINE command serves two purposes It can be used to retrieve a list of all the variables currently defined in your SQL session; it can also be used to explicitly define a value for a variable referenced as a substitution variable by one or more statements during the lifetime of that session The syntax for the two variants of the DEFINE command are as follows:

DEFINE; DEFINE variable=value;

As Figure 9-8 demonstrates, a variable called EMPNAME is defined explicitly to have the value King The stand-alone DEFINE command in SQL*Plus then returns a number of session variables prefixed with an underscore character as well as other

Figure 9-8

substitution variables defined earlier Two different but simple queries are executed, and the explicitly defined substitution variable EMPNAME is referenced by both queries Finally, the variable is UNDEFINED Support of session-persistent variables may be switched off and on as required using the SET DEFINE OFF command The SET command is not a SQL language command, but rather a SQL environment control command When you specify SET DEFINE OFF, the client tool (for example, SQL*Plus) does not save session variables or attach special meaning to the ampersand symbol This allows the ampersand symbol to be used as an ordinary literal character if necessary The SET DEFINE ON|OFF command therefore determines whether or not ampersand substitution is available in your session The following query uses the ampersand symbol as a literal value When it is executed, you are prompted to submit a value for bind variable SID

select 'Coda & Sid' from dual;