I’m using double ampersand. If I run the whole script it works, but I can’t
extract working insert statements from the script output grids. If I highlight
individual statements within the script, it only reliably does the replacement
for the first statement in the script.
select ROWID, P.* from gjbpdef p where p.GJBPDEF_JOB = ‘&&job’;
select ROWID, P.* from gjbpdft p where p.GJBPDft_JOB = ‘&&job’;
select ROWID, D.* from gjbpVAL d where d.GJBPVAL_JOB = ‘&&job’;
On 5/10/2011 3:55 PM, Bert Scalzo wrote:
Are you using double ampersand when you try to have parameter picked up
multiple times – as it is in sql plus and toad – or using single
ampersand
I'm using double ampersand. If I run the whole script it works, but I
can't extract working insert statements from the script output grids. If
I highlight individual statements within the script, it only reliably
does the replacement for the first statement in the script.
select ROWID, P.* from gjbpdef p where p.GJBPDEF_JOB = '&&job';
select ROWID, P.* from gjbpdft p where p.GJBPDft_JOB = '&&job';
select ROWID, D.* from gjbpVAL d where d.GJBPVAL_JOB = '&&job';
I'm using 10.5 and I can get F5 or F9 to prompt once for &&job, and then
use it in all subsequent statements.
I can select a couple of them, only, and F9 or F5 them, get one prompt
for the value, supply it and the results are as expected.
I’m on 10.6.1.3. I open the following script and run it and it produces rows
for each query:
select ROWID, J.* from gjbjobs j where j.GJBJOBS_NAME = ‘&&job’;
select ROWID, P.* from gjbpdef p where p.GJBPDEF_JOB = '&&job';
select ROWID, P.* from gjbpdft p where p.GJBPDft_JOB = '&&job';
select ROWID, D.* from gjbpVAL d where d.GJBPVAL_JOB = '&&job';
Then I highlight just the last query and execute and it prompts for the
variable, look like it has the value I just used, but returns no rows. I
can change the value to another that should return rows, and it still
doesn't find any. If I highlight the last query and select execute as a
script, it does find rows, then I can execute and it will return rows.
On 5/11/2011 10:10 AM, Norman Dunbar wrote:
Hi Wendy,
On 11/05/11 14:48, Wendy Penfold wrote:
> I'm using double ampersand. If I run the whole script it works, but I
> can't extract working insert statements from the script output grids. If
> I highlight individual statements within the script, it only reliably
> does the replacement for the first statement in the script.
>
> select ROWID, P.* from gjbpdef p where p.GJBPDEF_JOB = '&&job';
>
> select ROWID, P.* from gjbpdft p where p.GJBPDft_JOB = '&&job';
>
> select ROWID, D.* from gjbpVAL d where d.GJBPVAL_JOB = '&&job';
>
I'm using 10.5 and I can get F5 or F9 to prompt once for &&job, and then
use it in all subsequent statements.
I can select a couple of them, only, and F9 or F5 them, get one prompt
for the value, supply it and the results are as expected.
--
Cheers,
Norm. [TeamT]
So is what you’re asking for (i.e. what you call the bug) is that execute
should work 100% the same as execute as script with regard to substitution
variables – and that value persists execute and execute as script
instantiations? I’d call this a feature request and suggest adding it to the
idea pond. All SQL*Plus executes as essentially execute as script – so
that’s why it is seemingly different in Toad – as we have two different
execute modes with different execute engines ….