sql submitted on timer

bookor · May 5, 1999

I need to run a SQL script unattended on a time/date basis. 
For example, I need a select script to run every hour to extract a set of records into a delimited text file. What is the best way to accomplish this? Im working with Oracle Pl/Sql but have VB available if necessary. However, I want to use the most efficient method possible because the script will be running against a fairly large database. 
 
Any advice, assistance in creating a text file with sql and running it unattended would be appreciated 
 
bob

janeway · May 17, 1999

If your database is on a UNIX system you can use cron to 
schedule a sql script to start your PL/SQL.

bookor · May 17, 1999

It is on NT. There is an "at" command in nt. which i'm sure is similar. Theoretically I could schedule it to run sql/plus on a periodic basis but ive never had any luck getting it to work correctly. 
 
thanks for the reply

holland · Jun 14, 1999

You could use the dbms_job package inside Oracle 
to schedule the running of a stored procedure. The 
stored procedure in turn could perform the select 
into a cursor object, open up a file for writing, 
then loop through the cursor and write out to your 
file.

bookor · Jun 15, 1999

I found some references to dbms_job, but even in my best ref books there is no description of how to use it. It is mentioned by name but there are NO examples of how to use it. Is there any chance you could point me to any examples of using dbms_job? 
 
IT would be a great help if I could find some details on it. 
 
thanks for the reply

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

sql submitted on timer

bookor

MIS

janeway

MIS

bookor

MIS

holland

Programmer

bookor

MIS

Similar threads

Part and Inventory Search

Sponsor