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. |
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
|
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. |
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Términos de uso Privacidad Cookie Preference Center