Toad World® Forums

Re: how-to question...moving data from a test instance to production

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

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]

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 ….

I do believe I misunderstood the original question. And I think I might be
getting a little closer.

I have a piece of code:

select ‘select sysdate from dual;’ from dual;

select ‘select sysdate from dual;’ from dual;

It produces 4 output tabs: Output, Grid 1, Grid 2, and Environment.

Grid 1 and Grid 2 consist of the the data from the individual calls. The Output
tab consists of the total but is filled with “non-functioning
stuff”.

Ex.

Time Start: 5/11/2011 9:38:22 AM

‘SELECTSYSDATEFROMDUAL;’

The start and end time are options. The x rows selected can’t be removed.

Greg

On May 11, 2011, at 11:42 AM, “Simoneau, Roger”
wrote:

I do believe I misunderstood the original question. And I think I might be
getting a little closer.

I have a piece of code:

select ‘select sysdate from dual;’ from dual;

select ‘select sysdate from dual;’ from dual;

It produces 4 output tabs: Output, Grid 1, Grid 2, and Environment.

Grid 1 and Grid 2 consist of the the data from the individual calls. The
Output tab consists of the total but is filled with “non-functioning
stuff”.

Ex.

Time Start: 5/11/2011 9:38:22 AM

‘SELECTSYSDATEFROMDUAL;’