Return
-
Title
The 'Prompt for substitution variable' option is un-checked but it still prompts for substitution. -
Description
The "Prompt for substitution variable" option is un-checked but it still prompts for substitution.
Running a query as script (F5) with an ampersand (&) value. The option "Prompt for substitution variable" un-checked, but it still prompts for substitution.
Example query:
INSERT INTO COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES (22, '&Hello', 4);
-
Cause
The "Prompt for substitution variable" option only applies to executing query as statement (F9) and not for running scripts (F5).
-
Resolution
WORKAROUND 1:
Run using Execute as Statement (F9) and have Toad Option "Prompt for substitution variable" un-checked.
WORKAROUND 2:
Run using Run as Script (F5) and use the command 'SET DEFINE OFF' as the first statement before your query.
For example,SET DEFINE OFF;
INSERT INTO JLIN.COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES (22, '&Hello', 4);
INSERT INTO JLIN.COUNTRIEs...
INSERT INTO JLIN.COUNTRIEs...If you don’t want to keep typing SET DEFINE OFF, you can put this statement in the glogin file and Toad will execute it in the back end all the time.1. Go to View | Toad Options
2. Select Execute/Compile
3. On the RHS side click on the Edit button for glogin
4. Type in SET DEFINE OFF in the last line, save the file and close it
5. Check ‘Execute login scripts’