Ahh… poor, poor Greg. As soon as you say “can’t”, one
finds a way:
set feedback 0
set pagesize 0;
set heading off;
select ‘select sysdate from dual;’ from dual;
select ‘select sysdate from dual;’ from dual;
Produces:
Time Start: 5/11/2011 9:49:01 AM
select sysdate from dual;
select sysdate from dual;
Time End: 5/11/2011 9:49:02 AM
Elapsed Time for Script Execution: 109 msecs
Setting feedback to a value of zero did the trick for the inner data.
Roger S.
I actually wrote a blog on this a few years back – TOAD can do dynamic
scripting (i.e. script to write and even execute a script). Might be worth a
look see J
Ummm. I meant to say you can’t do it with a toad option! Yeah. That’s it!!
That’s the ticket!!!
On May 11, 2011, at 11:50 AM, “Simoneau, Roger”
wrote:
Ahh… poor, poor Greg. As soon as you say “can’t”, one finds a way:
set feedback 0
set pagesize 0;
set heading off;
select ‘select sysdate from dual;’ from dual;
select 'select sysdate from dual;' from dual;
Produces:
Time Start: 5/11/2011 9:49:01 AM
select sysdate from dual;
select sysdate from dual;
Time End: 5/11/2011 9:49:02 AM
Elapsed Time for Script Execution: 109 msecs
Setting feedback to a value of zero did the trick for the inner data.
Roger S.
I’ll have to look at the scripting…the blog is on the Toad website someplace?
This is what I need to do…often.
I run this script…
select * from general.Gubobjs o where O.GUBOBJS_NAME = ‘&&job’;
select * from bansecr.Guruobj u where u.Guruobj_object =
'&&job';
SELECT ROWID, G.* FROM BANSECR.GUROWNR G WHERE GUROWNR_OBJECT =
'&&job';
select * from bansecr.Guraobj a where a.Guraobj_object =
'&&job';
select * from General.Gjbjobs g where G.GJBJOBS_NAME = '&&job';
select ROWID, P.* from gjbpdft p where p.GJBPDft_JOB = '&&job'
and P.GJBPDFT_USER_ID is null;
select * from General.Gjbpval v where v.GJBPval_JOB = '&&job';
select * from General.Gjbpdft f where F.GJBPDFT_JOB = '&&job';
select ROWID, P.* from gjbprun p where p.GJBPrun_JOB = '&&job';
All of the data that is produced from these selects needs to get moved to the
production database.
If I run this as a script, and get multiple script output grids, I can’t extract
insert statements from each grid easily, because it doesn’t automatically pick
up the schema and table name. So, if I try to highlight and run each
individually, I can’t do it because it will only successfully plug in the &&job
parameter on the first select statement when I select them individually in the
editor–even though it pops up the variable entry box and makes you think it’s
going to do it.
So what I end up doing is copying each of these selects individually into an
editor window, running it, export dataset as insert statements, paste the insert
statements to another editor window, and finally saving the collected inserts
into a file. It’s pretty tedious and easy to forget a select or duplicate one if
I get distracted while doing this. Just to make it more interesting, under some
circumstance that I haven’t been able to track down yet, the schema–which I
have asked toad to include in the insert statements–sometime ends up being the
schema I’m running in, rather than the schema the table lives in… This I
usually don’t notice until the DBA tries to run it in production… So, I’m sure
there must be an easier less error prone way…I just haven’t found it yet.
On 5/11/2011 11:52 AM, Bert Scalzo wrote:
I actually wrote a blog on this a few years back – TOAD can do dynamic
scripting (i.e. script to write and even execute a script). Might be worth a
look see J
How about this:
I didn’t know you could multiselect views to extract data from. I’ll give it a
try. Thanks for the ideas!
On 5/11/2011 1:39 PM, John Dorlon wrote:
How about this:
make a view out of each query (one time). You could use a package
constant or value in a table instead of the substitution variable.
Multiselect the views from SB LHS and choose “export data”.
Export to insert statements without the schema name.
Then make a file (one time) to run the resulting SQL files.
Run the master file while connected to your target schema.