Using substitution variables for flexible SQL statements

Flexible SQL statements are a powerful way to improve productivity.

SQL Editor lets you use substitution variables in SQL statements—similar to the way SQL*Plus handles them.

The following are the procedures for:

 Specifying substitution variables with the & symbol

 Specifying substitution variables with the && symbol

 Defining and undefining numeric and character variables

Specifying substitution variables with the & symbol

You can use the & symbol followed by a variable name to specify a substitution variable. For instance, you can use &EMPNUM as a valid substitution variable name.

An example of an SQL statement demonstrating the use of a substitution variable is:

SELECT *

FROM EMP

WHERE EMPNUM = ‘&EMPNUM’;

When you execute the statement above, the SQL Editor prompts you to enter the value for the employee name at run time. This allows you to create generic SQL statements that can be reused.

Substitution variables permit even greater flexibility when you write an SQL statement because you can use them in any part of the SQL statement. For example:

SELECT &COL1, &COL2

FROM &TAB;

This statement prompts you to enter the column names, as well as the table name at the time of execution.

You can use this concept to create other types of generic SQL statements or scripts. One practical application of this concept is creating a generic script for creating user codes at your site.

For example:

CREATE USER &&UNAME IDENTIFIED BY &PASS;

GRANT ALL ON EMP TO &&UNAME;

The use of the double ampersand is described in the paragraphs that follow.

Specifying substitution variables with the && symbol

You can use the double ampersand the same way as the single ampersand (described above) with the following differences.

Whenever SQL Navigator encounters the first && variable, SQL Navigator looks up the variable to determine whether it has already been defined in either a DEFINE statement or in a previous && variable.

 If the variable is defined, SQL Editor substitutes the value in the SQL statement.

 If the variable is undefined, SQL Editor prompts you to enter the value of the variable, defines the variable for future look-ups, and substitutes the value in the SQL statement.

Once a && variable is defined, you are no longer prompted to enter its value in the same session until you UNDEFINE the variable.

Defining and undefining numeric and character variables

You can use the terms DEFINE and UNDEFINE to define and undefine numeric and character variables in SQL scripts.

Alternatively, to view, define, or undefine the substitution variables, you can right-click in the SQL tab of the SQL Editor window and select Define Variables from the shortcut menu.

You can also specify whether you wish the substitution variables to retain their values through subsequent sessions.

To define numeric and character variables

Use the DEFINE statement in the SQL Editor to define a substitution variable.

A character/varchar substitution variable definition uses single quotes:

DEFINE EMP_NAME=‘SCOTT’

An example of a numeric substitution variable definition is:

DEFINE EMP_NUM=4467

To undefine numeric and character variables

Use the UNDEFINE statement in the SQL Editor to undefine a previously defined substitution variable. For example:

UNDEFINE EMP_NAME