I just recently updated to 10.6. The following worked ok in the previous version
(10.1?). I have the code below in an editor window. If I ‘execute as a script’,
the substitution variable prompts once and works correctly for all 3 statements.
If however, I highlight the selects individually and ‘execute’, the prompt
window appears, but the substitution only occurs if I have hightlighted the
first statement alone…if I have either of the subsequent statements selected,
the substitution doesn’t occur.
SELECT *
FROM GENERAL.GUBOBJS G
Where
GUBOBJS_NAME = ‘&&objname’;
SELECT
ROWID, G.GURUOBJ_OBJECT, G.GURUOBJ_ROLE, G.GURUOBJ_USERID,
G.GURUOBJ_ACTIVITY_DATE
FROM BANSECR.GURUOBJ G
Where
GURUOBJ_OBJECT = '&&objname';
SELECT
ROWID, G.GURAOBJ_OBJECT, G.GURAOBJ_DEFAULT_ROLE, G.GURAOBJ_CURRENT_VERSION,
G.GURAOBJ_SYSI_CODE, G.GURAOBJ_ACTIVITY_DATE, G.GURAOBJ_CHECKSUM
FROM BANSECR.GURAOBJ G
Where
GURAOBJ_OBJECT = '&&objname';
When I change it to &, then it prompts me repeatedly for the variable when I run
the entire script. Yes, I can deal with it…I’ll make 2 copies of the script,
one with & and one with && then…but it still seems like a bug. I’ve been able
to use this script and many others either way for many years. And it doesn’t
make logical sense that running the first select alone works ok, but running any
of the others alone does not.
On 10/18/2010 8:56 AM, Brad Boddicker wrote:
Wendy, if you’re using && then you should only get one prompt
for the sub var. If you use & instead of && I think you will get
your desired behavior.
It is possible that they have made Toad act more like SQL*Plus. So the new
behaviour may be a feature rather than a bug.
I just ran some SQLPlus and as long as the first query entered is && it asks
only once. So it is possible that using F9 makes it look like SQLPlus. Once the
variable has been set it does not prompt anymore. While F5 acts like a new
session each time.
