Schedule a script

[first_bankus]

I am very new to the TOAD world.  I have been looking online to find
a way to schedule a script to run weekly.  I have read that there is
a way to use the Windows Scheduler to start the script in TOAD.  I
have TOAD for Oracle 9.5.  Only thing is I have not really found any
directions on how to do this and no one at my company has a clue how
it is done.

I just want something like this to run at 5am every Monday

drop table Over_laid_claims;
create table Over_laid_claims1 nologging compress as
select    
       a.film_num,
       a.claim_num,
       a.initial_examiner,
       a.examiner,
       a.history_status as proc_status,
       b.history_status as unproc_status,
       a.claim_lock_code as proc_lock_code,
       b.claim_lock_code as unproc_lock_code,
       a.pended_date as proc_pended_date,
       b.pended_date as unproc_evff_date,
       a.eff_date as proc_eff_date,
       b.eff_date as unproc_eff_date
from edw.sub_claim a,    
     edw.sub_claim b
where a.pended_date between to_date(to_char(sysdate-7)) and to_date
(to_char(sysdate-1))  
and      b.pended_date between to_date(to_char(sysdate-7)) and
to_date(to_char(sysdate-1))    
and      a.claim_num = b.claim_num
and      a.history_status in ('F', 'P', 'G', 'H')
and      b.history_status not in ('F', 'P', 'G', 'H')
and      a.eff_date < b.eff_date;


Then export the created table to an .xls

Can you help me?

[Brad Boddicker]

Open View > Action palette.  Select the "Set 1" tab and the "Misc" tabs.  Click on the Execute Script button in the Misc tab to create an "execute script" action.  This action will appear in Set1.  Double click on this action to bring up the properties window and set up your script.  Go to the "Exporting" tab and click on the "Export Dataset" button.  Export Dataset action shows in Set 1 under your Execute script action.  Double click on this action to set up your properties.  Once you have your 2 actions setup, R-click on the Set 1 tab and select "Schedule" and follow the steps.  There you go.

The only limitation of Action Palette 9.5 and 9.6 that will affect this particular problem is that we don't have anything built in that could make your file or folder names dynamic.  You would have to overwrite the export file or create a bat file to move the files.  When Toad 9.7 is released, you will be able to do all of this and then some.  9.7 will introduce the next generation of Toad scripting and will hopefully provide a complete solution for you.  The Action Palette will be replaced with the "AppDesigner" which is much more powerful and complete with folder/file/list iterators, variable support, show messages, if..then, repeat..until, while...do actions, and more.

-Brad


-----Original Message-----
From: toad@... [mailto:toad@...] On Behalf Of first_bankus
Sent: Tuesday, July 08, 2008 4:16 PM
To: toad@...
Subject: [toad] Schedule a script

I am very new to the TOAD world.  I have been looking online to find
a way to schedule a script to run weekly.  I have read that there is
a way to use the Windows Scheduler to start the script in TOAD.  I
have TOAD for Oracle 9.5.  Only thing is I have not really found any
directions on how to do this and no one at my company has a clue how
it is done.

I just want something like this to run at 5am every Monday

drop table Over_laid_claims;
create table Over_laid_claims1 nologging compress as
select
       a.film_num,
       a.claim_num,
       a.initial_examiner,
       a.examiner,
       a.history_status as proc_status,
       b.history_status as unproc_status,
       a.claim_lock_code as proc_lock_code,
       b.claim_lock_code as unproc_lock_code,
       a.pended_date as proc_pended_date,
       b.pended_date as unproc_evff_date,
       a.eff_date as proc_eff_date,
       b.eff_date as unproc_eff_date
from edw.sub_claim a,
     edw.sub_claim b
where a.pended_date between to_date(to_char(sysdate-7)) and to_date
(to_char(sysdate-1))
and      b.pended_date between to_date(to_char(sysdate-7)) and
to_date(to_char(sysdate-1))
and      a.claim_num = b.claim_num
and      a.history_status in ('F', 'P', 'G', 'H')
and      b.history_status not in ('F', 'P', 'G', 'H')
and      a.eff_date < b.eff_date;


Then export the created table to an .xls

Can you help me?



------------------------------------

Questions?  Try the on-line FAQ: http://asktoad.com/DWiki/doku.php?id=faq:questions

Trouble downloading or running the freeware update? See http://asktoad.com/DWiki/doku.php/howto_install_and_update_freetoad

Do you know about the other TOAD Yahoo Message Boards?  Go here to find out more - http://www.toadsoft.com/maillist.htmYahoo! Groups Links

[first_bankus]

Okay....I am following the directions....I don't mean to sound
duh...but when I setup the "Export Dataset" properties it wants me to
put something in the "DataSet" tab.
I'm I putting the query here?  
Just not sure what to do here.
Thanks

[Jeff Smith-7]

Export Dataset Action
The export dataset action lets you run a query and export the dataset to a file at any time.
The Export Dataset properties window options are equivalent to the Save As (Export Dataset) window.
For information about setting options, please see the Grid|Save As (Export Dataset<mk:@MSITStore:C:\Program%20Files\Quest%20Software\Toad%20for%20Oracle%2091\toad.chm::/TOAD/Save_As.htm>) topic.
Dataset
When you are selecting Save As from the data grid, Toad automatically includes the query that was used to create the dataset.
When setting properties for the Export Dataset action, you will need to insert the query or objects you want to export manually.
To set dataset specifications

 1.  Select either Export query or Export objects.
 2.  Enter the query in the box.
Or
Click Add and select the objects you want to export.


From: toad@... [mailto:toad@...] On Behalf Of first_bankus
Sent: Tuesday, July 08, 2008 5:17 PM
To: toad@...
Subject: [toad] Re: Schedule a script


Okay....I am following the directions....I don't mean to sound
duh...but when I setup the "Export Dataset" properties it wants me to
put something in the "DataSet" tab.
I'm I putting the query here?
Just not sure what to do here.
Thanks