I recently upgraded to Toad Oracle 10.5. Since I logon to multiple databases I use a &SCHEMA variable.
I have many scripts where this variable is repeated. In this version
this variable shows up once for each time it appears in the script.
In one particular script I have 11 unioned statements with a join of 2 or three tables each with the &SCHEMA variable.
In earlier versions I was prompted once for identical variables, which is the standard for most coding languages even PL/SQL, but with Toad each needs to be populated. Once populated I have to scroll down all 33 to get to the other variables I need to populate. And if I go to a new instance/schema to run it I have to go through it again.
While I know I can set the Schema through Toad but that will not work if the joins are for multiple schemas &SCHEMA1 & &SCHEMA2.
To make it even worse, it prompts you for variables even if the line is COMMMENTED OUT. Since we use these queries multiple ways we comment out or uncomment the “and” predicates at will depending what we are trying to query at the moment.
Earlier versions only prompted you once for each variable and ignored variables in lines that were commented out.
Is there any way to get Toad 10,X to handle variables like the older versions?
Does anyone know why Quest changed the way variables are being handled in this release?
Use &&Schema. instead of &Schema. With &&Schema., the variable is only prompted once. I don't know about variables in comments.
At 08-04-2011 18:52, you wrote:
I recently upgraded to Toad Oracle 10.5. Since I logon to multiple databases I use a &SCHEMA variable.
I have many scripts where this variable is repeated. In this version
this variable shows up once for each time it appears in the script.
In one particular script I have 11 unioned statements with a join of 2 or three tables each with the &SCHEMA variable.
In earlier versions I was prompted once for identical variables, which is the standard for most coding languages even PL/SQL, but with Toad each needs to be populated. Once populated I have to scroll down all 33 to get to the other variables I need to populate. And if I go to a new instance/schema to run it I have to go through it again.
While I know I can set the Schema through Toad but that will not work if the joins are for multiple schemas &SCHEMA1 & &SCHEMA2.
To make it even worse, it prompts you for variables even if the line is COMMMENTED OUT. Since we use these queries multiple ways we comment out or uncomment the "and" predicates at will depending what we are trying to query at the moment.
Earlier versions only prompted you once for each variable and ignored variables in lines that were commented out.
Is there any way to get Toad 10,X to handle variables like the older versions?
Does anyone know why Quest changed the way variables are being handled in this release?
William D. Smith
SDSU, Academic Affairs
William.D.Smith@sdsu.edu
I recently upgraded to Toad Oracle 10.5. Since I logon to
multiple databases I use a &SCHEMA variable.
I have many scripts where this variable is repeated. In this version
this variable shows up once for each time it appears in the script.
In one particular script I have 11 unioned statements with a
join of 2 or three tables each with the &SCHEMA variable.
In SQL*Plus, which Toad does its best to emulate, you will get the same
multiple prompt.
To get a prompt, you can use &SCHEMA the first time and &&SCHEMA for the
remainder. The first will prompt you once, the rest will use the same
value. This is what SQL*Plus does also.
You can simply use &&SCHEMA all the time, but I have found in SQL*Plus
that that can lead to problems if I have already executed the script -
it uses the previous values without prompting. So, best to use at least
one &SCHEMA per script.
...
To make it even worse, it prompts you for variables even if
the line is COMMMENTED OUT. Since we use these queries
multiple ways we comment out or uncomment the "and"
predicates at will depending what we are trying to query at
the moment.
Yes, this is exactly what SQL*Plus does as well I'm afraid. In order to
have an & or && in a comment, you need to wrap the comment in:
SET DEFINE OFF
-- This is a comment & another part of the comment.
SET DEFINE ON
Or, you can use SET DEFINE to use a different character as the
"prompting" one and carry on using the & in your comments.
SET DEFINE #
Now you can freely use '&' in comments and will get prompted when a '#'
appears. (Or '##' if not already got a defined value.
Of course, this is of little help when you don't want prompting for
commented out defines that you do need prompting for elsewhere.
Earlier versions only prompted you once for each variable
and ignored variables in lines that were commented out.
I'm not sure about this, but I can't remember I'm afraid. I'm getting
too old and stuff is aging out of my "organic" cache!
Is there any way to get Toad 10,X to handle variables like
the older versions?
Does anyone know why Quest changed the way variables are
being handled in this release?
As I said, I don't remember, but I do think that Toad hasn't changed (I
could be wrong) but Toad is handling defines exactly as SQL*Plus does.
Not a lot of help I'm afraid.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
