Chat now with support
Chat with Support

SQL Navigator for Oracle 7.4 - Release Notes

Scan Defines/Substitutions

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

Note: Turn on Scan Defines/Substitutions when using variables or text that contain the characters &, &&, or = :[bind variable]. Otherwise, the statements containing the variables will generate an error.

 

Specifying substitution variables in SQL statements

Use substitution variables for flexible SQL statements. Flexible SQL statements are a powerful way to improve productivity.

Feature Example Description

Use the & symbol followed by a variable name to specify a substitution variable.

&EMPNUM

You can use &EMPNUM as a valid substitution variable name.

An example of a SQL statement demonstrating the use of a substitution variable

SELECT *

FROM EMP

WHERE EMPNUM = ‘&EMPNUM’;

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

You can use substitution variables in any part of the SQL statement.

SELECT &COL1, &COL2

FROM &TAB;

When you execute this SQL statement, the Code Editor prompts you to enter the column names, as well as the table name.

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.

CREATE USER &&UNAME IDENTIFIED BY &PASS;

GRANT ALL ON EMP TO &&UNAME;

  

You can use the double ampersand the same way as the single ampersand with some differences.

&&UNAME

When you execute this SQL statement

  • The first time the Code Editor encounters the && variable, it 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, Code Editor substitutes the value in the SQL statement.
  • If the variable is undefined, the Code 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.

 

DEFINE and UNDEFINE

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

Statement Example Description

DEFINE

DEFINE EMP_NAME=‘SCOTT’

DEFINE EMP_NUM=4467

Define a substitution variable.

A character/varchar substitution variable definition uses single quotes.

UNDEFINE

UNDEFINE EMP_NAME

Undefine a previously defined substitution variable.

TIP: Alternatively, right click in the editing pane and select SQL Script Options | Substitutions to open the Substitutions dialog.

 

Related Topics

Module: Code Editor

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating